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

[jkrolczy]

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 ?

[AlexKaras]

@jkrolczy :

Hi,

Just the idea of the last hope: to my knowledge, TestComplete's ADO object namespace is a wrapper over Delphi's one. I never used it in my code but always used pure native ADO objects and never had problems like yours. The transition from TestComplete's ADO to native ADO should not be difficult. What if you give it a try?

P.S. Documentation for the native ADO can be found at Microsoft's MSDN. Sorry, but I don't have a direct link at hand.

[jkrolczy · Accepted solution]

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.