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