Forum Discussion

Sjef's avatar
Sjef
Occasional Contributor
2 years ago

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

  • rraghvani's avatar
    rraghvani
    Champion Level 3

    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();
    }

     

    • Sjef's avatar
      Sjef
      Occasional Contributor

      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)

  • Sjef's avatar
    Sjef
    Occasional Contributor

    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
    Champion Level 3

    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.