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 runtime e.g.:



- user enters some order data in a GUI that is bound to e.g. Northwind database (e.g. user orders a number of products for a customer for a specific date)

- order is saved in the GUI and corresponding records in tables Orders and Products are saved in the database (a web service in the background is actually writing the order into DB).

- the order overview is displayed in the GUI



Now I need to compare the overview in the GUI (ordered components for specific customer for a specific date are displayed in a form inside a panel) with corresponding situation in the database after order was saved.



Is there any possibility to create the SQL query on the fly (with dynamic values for date and customer) and to compare returned DB table with data displayed on the GUI form?



Many thanks for any hints...
  • Dmitry_Nikolaev's avatar
    Dmitry_Nikolaev
    Staff
    13 years ago

    Hi Marin,



    There are means for validating content of a grid control (Table Checkpoint) and data in a database (DB Table Checkpoint), but there is no built-in functionality to compare data of a GUI grid control with data in a database. However, you can create a script that will request data from the database and compare it with data in the grid control. Please find information on how to get data from a db in the Working With Databases help topic.



    You can put the data obtained from the database to a Table Checkpoint in a script and then invoke this Table Checkpoint. You can do this using the Values property of the Table object.







  • marin's avatar
    marin
    Frequent Contributor
    13 years ago
    Hello David,



    many thanks for the suggestion, I will certainly give it a try...



    Thanks again,



    Marin
  • Alexander_Biery's avatar
    Alexander_Biery
    New Contributor
    11 years ago
    I may be misinformed, but as far as I know, truly dynamic sql is not supported in TestComplete (bind variables or execute immediate a la Oracle PL/SQL).  It might be possible that you could build the query using a script, then concatenate variables into the statement.  However that seems like a ton of work for a feature that SmarBear should have developed long ago.



    If this exists, I apologize for my ignorance.  Plz let me know how you decided to do this, or if anyone else has an idea.
  • Dmitry_Nikolaev's avatar
    Dmitry_Nikolaev
    Staff
    11 years ago

    Hi Alexander,


     


    I am guessing that you do not understand the initial question. It was about changing the SQL request for a checkpoint on playback and not about dynamic SQL.


     


    Nevertheless, please note that TestComplete does not have its own implementation of SQL at all. It just uses OLE DB providers registered in the system and, therefore, the available functionality is limited by these providers.

    • mgroen2's avatar
      mgroen2
      Super Contributor
      8 years ago

      I am struggling with similar issue as well.

       

      My scenario:

       

      As a POC, I have created some simple Keyword Driven tests, which do the following:

      1. start application;

      2. add a Customer using the UI;

      3. Close application;

       

      I want to extend this test a step 4, which is a validation on the database. This validation should check whether the added customer has actually been written to the database. In this validation a simple SQL query would be enough for the moment. The SQL Query looks (simplified) as follows:

       

      Select CustomerName, ID, Address

      from Table.Customers

      where CustomerName is "ENTERED_CUSTOMER_NAME".

       

      The value "ENTERED_CUSTOMER_NAME" in the where clausule of the SQL statement, should match the value entered in the Keywordtest in the UI (step 2). And these values should be dynamically (read test data from Excel file).

       

      The validation at step 4 should pass if the result of the query is > 0 (a record is found), and it should fail if no record is found (which would indicate the entered customer data does not exist in the database).

       

      Question: is this possible with TestComplete? If so, could this be handled by Database Checkpoints or something?

       

       

       

      • tristaanogre's avatar
        tristaanogre
        Esteemed Contributor
        8 years ago

        This sounds like a DBTable checkpoint with the exception that you need a dynamic "WHERE" clause. So... I don't think this is available out of the box.  However, you can do what you're asking using ADO objects such as ADO.CreateCommand.  Build a common function where you pass in the fields you want, the table name, and the parameter for the "WHERE" clause and run the SQL query that way.

        I'm currently in the middle of re-factoring my SQLUtilities Script Extension to be a bit more object oriented but, built into it, is a query for precisely that in the current version.  Feel free to download and extract the code if you want to see how it's done.  The link I posted above also gives an example of writing such code.