Forum Discussion

marin's avatar
marin
Frequent Contributor
13 years ago

How to dynamically set SQL query for database table checkpoint?

Hello all, I wonder if it is possible to dynamically set SQL query which is the source for DB table checkpoint? My test scenario includes data consistency check that needs to be preformed at...
mgroen2's avatar
mgroen2
Super Contributor

tristaanogre Thanks again for your reply.


In short I totally agree with you, in the way that waiting for TestComplete to support this testing "out of the box", is not really desired in current situation.

Therefore, I am working on implementing ADO querie. However, I run in an issue with that:

For the ADO querie to be succesfull, I need to set the Connection String: ("Qry.ConnectionString = ").

 

But, I get stuck already at where to find the connection string for the environment I need to test.

I am using MS SQL Server Management Studio to connect to the database, but nowhere the Connection String is displayed (viewed properties, etc).

 

Sub TestProc
  ' Create a query
  Set Qry = ADO.CreateADOQuery
  ' Specify the connection string
  Qry.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + _
  "Data Source=C:\Microsoft Visual Studio\VB98\biblio.mdb"
  ' Specify the SQL expression
  Qry.SQL = "Select * FROM Authors WHERE Authors.[Year Born] >= :Param_Year"
  ' Specify the parameter value
  Qry.Parameters.ParamByName("Param_Year").Value = 1950
  ' Execute the query
  Qry.Open
  ' Process results and insert data into the test log
  Qry.First
  While Not Qry.EOF
    Log.Message Qry.FieldByName("Author").Value, Qry.FieldByName("Year Born").Value
    Qry.Next
  Wend
  ' Closes the query
  Qry.Close
End Sub

Also, I would like to emphasize the potential for Feature Requests. While feature requests (like this one) have been created, SmartBear can work on implementing them, in the mean time TestComplete users can use workarounds (coding, scripting, write extensions whatsoever) to deal with current validation challenges until TestComplete provides robust, enhanced, and high quality support for (this kind of) situations.

 

 

 

tristaanogre's avatar
tristaanogre
Esteemed Contributor
8 years ago

The connection string for an ADO object is the programmatic way of describing what you are doing in management studio.  You're not going to find the connection string explicitly stated in SQL Server Management studio.

I don't know the specifics of your environment so I'm not sure which string precisely to use.  However, if you go to https://www.connectionstrings.com/sql-server/ there are a variety of examples there.  

This is the one I've use most often in my environments.  Replace the bold items with the specifics for your environment.:

 

Provider=SQLOLEDB.1;Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

  • mgroen2's avatar
    mgroen2
    Super Contributor
    8 years ago

    SmartBear support desk helped me out with it as well, with these steps on how to get the Connection String from MS Visual Studio:

     

    You can try connecting to your database from Visual Studio:

    1. Open the Server Explorer window in Visual Studio.
    2. Right-click the Data Connections node and select Add Connection.
    3. Select Microsoft SQL Server as Data Source and .NET Framework Data Provider for OLE DB as Data provider. You'll then need to establish a connection  - select the server, provide valid credentials and select your database.
    4. When the connection is established, you can select you database on the list and see the Connection String value in the Properties tab (see the attached screenshot).
  • AlexKaras's avatar
    AlexKaras
    Champion Level 3
    8 years ago

    Hi,

     

    Another possible approach is to use .udl file and steps from here: https://community.smartbear.com/t5/TestComplete-Functional-Web/Can-anyone-help-me-how-to-access-Mongo-database/m-p/86460#M20415

     

    As for the dbTable checkpoint...

    If you open the file that corresponds to some given dbTable checkpoint with some text editor, you will notice that it contains (static) snapshot of the database data returned by the SQL entered when creating checkpoint. When called from the test, checkpoint gets new data from the database using the stored SQL expression and compares the obtained result with the stored one.

    The above algorithm implements the approach that is a recommended one for the unit testing, when a new clean database is created before test execution and then test is executed using predefined static set of data. dbTable checkpoint works perfectly fine for this approach and scenario.

    On the contrary, functional, end-to-end testing, that most of us is doing with TestComplete, does not assume re-creation of the clean environment before every test and static test data are not recommended as well. For this type of testing, dbTable checkpoint is nor the best tool, indeed.

  • Colin_McCrae's avatar
    Colin_McCrae
    Community Hero
    8 years ago

    Now that you have it connected, and as you start to play around with what you can do with this connection, I suspect you'll find it's pretty powerful.

     

    I do all sort of SQL related stuff in my tests. Verify counts, verify full records/single fields, record addition, record removal, allows for use of a timestamp so you don't have to reset the DB constantly (although it also allows you to create and restore backup DB's if you wish), etc etc. It's all built into my Framework as a script extension now and is very easy for the test guys to use. And they can fire it any valid SQL query they like. They just store the query as a small file, reference the file location (usually in the same folder as the driver sheet, but doesn't have to be) in the call on the driver sheet, and it will run that query and perform the appropriate check on the resulting RecordSet object.

     

    (One thing I will add .... I don't like RecordSet objects. They're horrible klunky things to work with IMHO!)