Ask a Question

Variable for connection string or Data Source Name?

denolfe
Occasional Contributor

Variable for connection string or Data Source Name?

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"]);


}















5 REPLIES 5
denolfe
Occasional Contributor

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


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");


  }


}

---------
Tanya Yatskovskaya
SmartBear Community and Education Manager



cbachman
Not applicable

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

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");


  }


}




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

cancel
Showing results for 
Search instead for 
Did you mean: