Forum Discussion

denolfe's avatar
denolfe
Occasional Contributor
12 years ago

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

Sort By
  • denolfe's avatar
    denolfe
    Occasional Contributor
    12 years ago
    For anyone interested, I got around this making every environment have the same Data Source Name and created the database checkpoints using that.
  • TanyaYatskovska's avatar
    TanyaYatskovska
    Icon for Alumni rankAlumni
    12 years ago

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


      }


    }

    • SangeethaT's avatar
      SangeethaT
      Regular Visitor
      9 years ago

      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's avatar
        DanH
        Contributor
        9 years ago

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

  • cbachman's avatar
    cbachman
    12 years ago
    In addition to use project variables when specifying a connection,  it would be valuable to specify a project variable when specifying th Query Text