Forum Discussion

Sjef's avatar
Sjef
Occasional Contributor
3 years ago
Solved

Use Variables in the sql connectionstring

Hi there,


I'm using 2 environments and 2 databases:
1 to create the testcases and 1 to execute them

I want to check the results with DBTables Custom queries (SSMS) and I have i.e. next connectionstring:

 

How CAN I make this string Variable ?
When I login at the ALPHA-environment  I want in the string ALPHA and server01

When I login at the BETA-environment I want in the string BETA and server02

 

Greetings,

Sjef van Irsel

Checkpoints
integrations
Keyword Tests
Test Results
  • rraghvani's avatar
    rraghvani
    3 years ago

    It's not possible within that dialog. However you can use ADO Object and create your own connection string using variables e.g.

    function ADOExample()
{
    var AConnection = ADO.CreateConnection();
    var dbServer = "Server1";
    var dbName = "ALPHA";
    AConnection.ConnectionString = "Provider=MSOLEDBSQL19.1; Integrated Security=SSPI; Persist Security Info=False; Initial Catalog=" + dbName + "; Data Source=" + dbServer + "; Use Encryption for Data=False;";
    AConnection.Open();
   
    // Do something
   
    AConnection.Close();
}

     

6 Replies

  • rraghvani's avatar
    rraghvani
    Icon for Champion Level 3 rankChampion Level 3
    3 years ago

    I have not provided any SQL statements, as I don't know your database structure.

     

    If you read the documentation on ADO Object, which I provided, it has example code. Which you can use to query your database.

     

     

     

     

  • Sjef's avatar
    Sjef
    Occasional Contributor
    3 years ago

    I have been able to make an ADO connection with the SQL server; but I'm not able to use SQL statements when I use your solution; is this possible or isn't it possible to use SQL statements ?

  • rraghvani's avatar
    rraghvani
    Icon for Champion Level 3 rankChampion Level 3
    3 years ago

    It's not possible within that dialog. However you can use ADO Object and create your own connection string using variables e.g.

    function ADOExample()
{
    var AConnection = ADO.CreateConnection();
    var dbServer = "Server1";
    var dbName = "ALPHA";
    AConnection.ConnectionString = "Provider=MSOLEDBSQL19.1; Integrated Security=SSPI; Persist Security Info=False; Initial Catalog=" + dbName + "; Data Source=" + dbServer + "; Use Encryption for Data=False;";
    AConnection.Open();
   
    // Do something
   
    AConnection.Close();
}

     

    • Hanila's avatar
      Hanila
      Occasional Contributor
      6 months ago

      Hi RRaghavani,

      How can i store my Db password in project variable and call that in the connection string?

      As password is encrypted , I am not able to call it directly as Project.Variables.sqlReadOnlyPWD. Could you please help me if you have a solution .

       

      Currently I have hardcoded the password in the string and I understand its not the secure way of execution

    • Sjef's avatar
      Sjef
      Occasional Contributor
      3 years ago

      Hi rraghvani,

      I'm Using Python as script ion my projectsuite, not C#

      We have tried to make an solution with Python, this solution works outside of Testcomplete, but not within testcomplete:


      import pyodbc

      def SqlConnection():

      # Connectiondata SQL Server
      #if ProjectSuite.Variables.SSMS == "ALPHA":
      server = "server01.isodev.loc"
      database = "ALPHA"
      username = "User123"
      password = "*********"

      # Make connection with SQL Server
      connection_string = f"Driver={{SQL Server}};Server={server};Database={database};UID={username};PWD={password}"
      #connection_string = f"Driver={{SQL Server}};Server={server};Database={database};Integrated Security=True"
      #connection = None

      # Initialise variable 'connection' outsite try

      try:
      # Make connection with SQL Server
      connection = pyodbc.connect(connection_string)

      # Make query
      query = f"SELECT top 1 CASE ir.message_data_source_id \
      WHEN 100 THEN 'Artikelbericht' \
      WHEN 101 THEN 'Productbericht' \
      WHEN 102 THEN 'Conditiebericht' \
      ELSE 'Onbekend bericht' \
      END AS [Berichtsoort] \
      FROM import_resource ir \
      WHERE ir.message_type_id = '1'"

      # Run query
      cursor = connection.cursor()
      cursor.execute(query)
      result = cursor.fetchone()

      if result:
      berichtsoort = result[0]
      print("Berichtsoort:", berichtsoort)
      # Change Project.Variables.Case
      #Project.Variables.Case = berichtsoort
      else:
      #Project.Variables.Case = "No result"
      print("No result")
      except Exception as e:
      print("Error making connection or running query", str(e))
      finally:
      if connection:
      connection.close() # Close connection

      # Call function to make sql connnection
      SqlConnection()

      #Log.Message(Project.Variables.Case)