Variable for connection string or Data Source Name?

[denolfe]

Hello,



I have been dynamically building my database connection string through scripting as I perform testing on SQL 2005, 2008, and 2012, which all could have a different SQL Driver.  



I am now seeing the need to create Database Checkpoints as opposed to checking specific values.  The problem I am running into is that when creating the DB Checkpoints, TestComplete requires me to either enter a Data Source Name or enter a connection string, both of which could be different based on which machine the testing is occurring on.



So my question is, is there a way to pass a variable for the connection string or the Database Source Name for these checkpoints? Is there a better way to do this, like populate a temporary table from a query then compare that to a data object?



Any help is appreciated.



Code for retrieving the connection string:

 function GetConnectionString()

 { 

   var Connection;

   var Key, ValueName;

   var regEx, SQLVersion, SQLDriver;

   // Gets an object for the Windows system registry key

   Key = Storages.Registry("Software\\Microsoft\\MSSQLServer\\MSSQLServer\\CurrentVersion", HKEY_LOCAL_MACHINE);

   // Specifies the name of the value you want to obtain

   ValueName = "CurrentVersion";

   SQLVersion = Key["GetOption"](ValueName, "Not Found");

   regEx = /^\d*/;

   // Perform search operation

   Match = SQLVersion["match"](regEx);


   if (Match == "9") 

     SQLDriver = "SQL Native Client";

   else if (Match == "10")

     SQLDriver = "SQL Server Native Client 10.0";

   else if (Match == "11") 

     SQLDriver = "SQL Server Native Client 11.0";


   Project["Variables"]["Connection_String"] = "Driver={"+ SQLDriver +"};Server="+ Sys["HostName"] +";Database=TWO;Trusted_Connection=yes;";

   Log["Message"]("SQL Connection String: "+ Project["Variables"]["Connection_String"]);

 }

[denolfe]

For anyone interested, I got around this making every environment have the same Data Source Name and created the database checkpoints using that.

[TanyaYatskovska]

Hi Elliot,

Thanks for sharing your solution with us!

I'd like to add that there is a suggestion to use project variables when specifying a connection, and your request has increased its rating!

Besides, the following sample may be helpful as well. It iterates through the DBTables collection and changes the needed connection string:

function UpdateConnectionString()

{

  //Obtain a collection of the DBTable elements using the aqObject.GetProperties method

  var props = aqObject.GetProperties(DBTables);

  var prop;

  var tableName;

  var newConnectionString = <my connection string>;

  //If you use a project variable to keep the new connection string (for example, called myConnectionString), use the following line instead:

  //var newConnectionString = Project.Variables.myConnectionString; 

  while (props.HasNext())

  {

    //Move to the next property

    prop = props.Next();

    //Obtain the table name

    tableName = prop.Name;

    //Use the eval function to assign a new value to the table's ConnectionString property

    eval("DBTables." + tableName + ".ConnectionString = newConnectionString");

  }

}

[cbachman]

In addition to use project variables when specifying a connection,  it would be valuable to specify a project variable when specifying th Query Text

[SangeethaT]

Tanya, 

         I tried doing exactly as you had scripted above. Yet the Connection String is not getting updated.

eval ("DBTables."+tableName+".ConnectionString = "+Chr(34)+newConnectionString+Chr(34))
log.message (DBTables.lockedrecords.ConnectionString)

       In line 2 of the script snippet pasted above, I still get the old connection string. I actually want to do a DB Table checkpoint after changing to the new connection string.

 eval ("DBTables."+tableName+".ConnectionString = "+Chr(34)+newConnectionString+Chr(34))
eval("DBTables."+tableName+".Check")

   Checkpoint in line 2 is never working as per the new connection string . I am using latest version of TestComplete. 

Please help. 

---

@TanyaYatskovska wrote:

Hi Elliot,

Thanks for sharing your solution with us!

I'd like to add that there is a suggestion to use project variables when specifying a connection, and your request has increased its rating!

Besides, the following sample may be helpful as well. It iterates through the DBTables collection and changes the needed connection string:

function UpdateConnectionString()

{

  //Obtain a collection of the DBTable elements using the aqObject.GetProperties method

  var props = aqObject.GetProperties(DBTables);

  var prop;

  var tableName;

  var newConnectionString = <my connection string>;

  //If you use a project variable to keep the new connection string (for example, called myConnectionString), use the following line instead:

  //var newConnectionString = Project.Variables.myConnectionString; 

  while (props.HasNext())

  {

    //Move to the next property

    prop = props.Next();

    //Obtain the table name

    tableName = prop.Name;

    //Use the eval function to assign a new value to the table's ConnectionString property

    eval("DBTables." + tableName + ".ConnectionString = newConnectionString");

  }

}

---

[DanH]

I've just tried the script and it worked perfectly.  Thanks.