cancel
Showing results for 
Search instead for 
Did you mean: 

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

SOLVED
Highlighted
Frequent Contributor

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 ?

 

 

 

 

JamesK
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Contributor

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

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.

 

 

JamesK

View solution in original post

11 REPLIES 11
Highlighted
Community Hero

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

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


Robert Martin
[Hall of Fame]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Vegas Thrill Rider
Extensions available
Highlighted
Community Hero

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

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.

 

Regards,
  /Alex [Community Hero]
____
[Community Heroes] are not employed by SmartBear Software but
are just volunteers who have some experience with the tools by SmartBear Software
and a desire to help others. Posts made by [Community Heroes]
may differ from the official policies of SmartBear Software and should be treated
as the own private opinion of their authors and under no circumstances as an
official answer from SmartBear Software.
The [Community Hero] signature is used with permission by SmartBear Software.
https://community.smartbear.com/t5/custom/page/page-id/hall-of-fame
================================
Highlighted
Community Hero

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


@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.


Robert Martin
[Hall of Fame]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Vegas Thrill Rider
Extensions available
Highlighted
Frequent Contributor

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

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

JamesK
Highlighted
Community Manager

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

Thanks for the collective effort in solving this!

 

@jkrolczy was this a success?


Sonya Mihaljova
Community and Education Specialist

Learn SmartBear products in a fun and easy way and prove your knowledge!
>>Participate in the TechCorner Challenge today
Highlighted
Frequent Contributor

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

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 ?

 

JamesK
Community Leader

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

 

    var Command = ADO.CreateCommand();
    var Error   = "";
    Command.CommandText = Sql;
    Command.CommandTimeout = 120;  // Timeout of the connection in seconds
    Command.ActiveConnection = Connection;
    Command.Execute();
    ErrCount = Connection.errors.count;
    if (ErrCount !== 0) {
      for (Err = 0; Err == ErrCount; Err++){
        Error += Connection.errors.item + "\n";
      }
      Connection.Errors.Clear;
      throw Error;
    }

 

 

When an error occurs do you trace the stack of ADO error to look at the root cause ?

 

 

Un sourire et ça repart

Highlighted
Frequent Contributor

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

Sorry,

 

I understand the principle here that this should work, but it did not for me.

 

I see a solution was accepted for this thread, but it is not a solution that solved this issue for me.

 

Thanks all for the info you supplied me.

I will continue to try and debug more on this issue or maybe even try to change up

my ADO coding structure to where maybe the CommandTimeout can be adjusted.

 

 

JamesK
Highlighted
Community Manager

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

@jkrolczy the solution was unmarked!

What happened when you tried using the approach? We can continue the investigation here and see if the Community can come up with more useful suggestions!

 


Sonya Mihaljova
Community and Education Specialist

Learn SmartBear products in a fun and easy way and prove your knowledge!
>>Participate in the TechCorner Challenge today
New Here?
Join us and watch the welcome video:
Announcements
Join TechCorner Challenge!
Want a fun and easy way to learn TestComplete? Try solving weekly TechCorner challenges and get into the Leaderboard!

Challenge Status

How to execute remote test and obtain results via Test Runner REST API

Participate!

Comparing content of HTML table with Excel file data

Participate!

Compare HTML table with Excel file and correct data in the file

Participate!

Retrieve Data from a Web Page

Participate!
Top Kudoed Authors