Forum Discussion

jkrolczy's avatar
jkrolczy
Regular Contributor
5 years ago

TestComplete: SQL: ADO.CreateADOCommand: After a period of run time- Object is no longer valid.

I have a test script in which I am doing a series of SQL queries using ADO.

I would like to use these in a for - loop to go through a couple of servers in the same script.

I have them broken out into individual scripts currently ... at least 4 of the 18 so far.

Proving concept and hoping to work out this issue before going further.

 

One time through the code works, but on the next loop thru I eventually get:  Object is no longer valid.

 

To me, there seems to be maybe a timeout going on ???

 

Here is a sample piece of code of one of my queries.

 

var idx = 1;

var inum= 0;

 

// Data base operations
var RecSet, Cmd;

// Create a new object
Cmd = ADO.CreateADOCommand();

// Specify the connection string
Cmd.ConnectionString = "Provider="+Project.Variables.DBSqlServConnStrName+";Server="+transSvr[inum]+";Database=Domain1;Trusted_Connection=yes";

// Specify the command text (the SQL expression)
Cmd.CommandText = "SELECT * FROM Sites where Status = 0";
// Execute the command
RecSet = Cmd.Execute();
// Process the table records
RecSet.MoveFirst();
while (! RecSet.EOF)
{
siteID[idx] = RecSet.Fields("SiteID").Value;
// Log.Message(idx+" "+RecSet.Fields("SiteID").Value);
idx++;
RecSet.MoveNext();
};

// Log.Message(" ");
Log.Message("# of Active Sites: "+siteID.length);
// Log.Message(" ");

 

 

My script initially logged out a lot of the info from one SQL query part to another.

The log dumps were around ~500 rows.

 

As I took out most of the logging, the 5 SQL queries I performed worked and the script completed.

Again --- just for 1 loop, into the second loop, Object is no longer valid. will appear somewhere.

 

Which  leads me back to a timeout issue for the SQL queries ?

 

If this is the case, anyone know how to extend the timeout period or disable it for SQL: ADO.CreateADOCommand ?

 

 

 

 

  • jkrolczy's avatar
    jkrolczy
    5 years ago

    Current status on this issue:

     

    I went back to my ADO code approach and changed it up to follow an example from TestComplete help
    and I added the increase  connection CommandTimeout.

     

    The below is a piece of the code in place for my scripts.

     

    function TRANSsvr()
    {
      var AConnection, RecSet;
      // Create a Connection object
      AConnection = ADO.CreateADOConnection();
      AConnection.CommandTimeout = 120;
      // Specify the connection string
      AConnection.ConnectionString = "Provider="SQLNCLI11";Server="TRANSsvr";Database=Primary;Trusted_Connection=yes";
      // Suppress the login dialog box
      AConnection.LoginPrompt = false;
      AConnection.Open();
      // Execute a simple query
      RecSet = AConnection.Execute_("SELECT * FROM Sites where Status = 0");

      RecSet.MoveFirst();

     

      while(! RecSet.EOF)
      {
        Log.Message(RecSet.Fields.Item("name").Value);
        RecSet.MoveNext();
      }

      AConnection.Close();
    }

     

    The scripts now go past ~24 sec barrier I kept hitting to where I lost my connection Obj.

     

    I am happy with this approach now and can close off on these scripts and this thread.

     

    Thanks all again for you assistance.

     

     

  • AlexKaras's avatar
    AlexKaras
    Champion Level 3

    Hi,

     

    Another idea is that connections created by ADO objects are not closed in your code. And ADO has a limit to simultaneously opened connections (32 IIRIC). Try to explicitly close connection before switching to another server.

     

    • tristaanogre's avatar
      tristaanogre
      Esteemed Contributor

      AlexKaras wrote:

      Hi,

       

      Another idea is that connections created by ADO objects are not closed in your code. And ADO has a limit to simultaneously opened connections (32 IIRIC). Try to explicitly close connection before switching to another server.

       


      Ah!  I had forgotten about that.  I knew about he connections for the Microsoft JET used for CSV and Excel DDT, but yeah, that might be the limation there as well.  Connection could be closed and the objects should be set to "null" to allow for implicit garbage collection.

      • jkrolczy's avatar
        jkrolczy
        Regular Contributor

        I have played with the Implicit and Explicit Close ways for the Recordset and Cmd of ADO.

        I have also tried to condense down my code (remove unnecessary code and logging) to not waste time.

         

        My script continues to FAIL around 25 secs.

        Some of this time is TestComplete run time ~5-10 secs.

         

        Maybe let's go with this a different direction ... how can I get more time for the ADO connection.

        It seems the intense SQL query going on along between 2 servers I am connecting to seems to need more time.

         

        I understand what is being done for a Close if that worked, but the 'Object is no longer valid.' after

        the script runs for the ~25 secs is the killer right now.

         

        My work around right now is I have broken my for-loop going thru 18 different Production envs

        out into their own script.

        Now again from this - running each script 99% of the time each will run fine but the 1% of  'Object is no longer valid.' occurs to this timeout ? .  So rerunning the script again, it will usually complete.

         

        I am also at the mercy of the Production DBs with a lot of data and how quickly it can handle my DB calls

         

        Summarizing here:

          How to increase ADO timeout time ?

         

    • jkrolczy's avatar
      jkrolczy
      Regular Contributor

      I will try this approach and get back with reply asap.

      • sonya_m's avatar
        sonya_m
        SmartBear Alumni (Retired)

        Thanks for the collective effort in solving this!

         

        jkrolczy was this a success?

  • tristaanogre's avatar
    tristaanogre
    Esteemed Contributor

    On what line do you get the "Object is no longer valid" error?