Forum Discussion

Sjef's avatar
Sjef
Occasional Contributor
11 months 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 connect...
Checkpoints
Integrations
Keyword Tests
Test Results
rraghvani's avatar
rraghvani
Champion Level 3
11 months 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();
}

 

  • Sjef's avatar
    Sjef
    Occasional Contributor
    10 months 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)