Parameterizing Standard Database Checkpoints - WinRunner

When you create a standard database checkpoint using ODBC (Microsoft Query), you can add parameters to an SQL statement to parameterize the checkpoint. This is useful if you want to create a database checkpoint with a query in which the SQL statement defining your query changes. For example, suppose you are working with the sample Flight application, and you want to select all the records of flights departing from Denver on Monday when you create the query. You might also want to use an identical query to check all the flights departing from San Francisco on Tuesday. Instead of creating a new query or rewriting the SQL statement in the existing query to reflect the changes in day of the week or departure points, you can parameterize the SQL statement so that you can use a parameter for the departure value. You can replace the parameter with either value: “Denver,” or “San Francisco.” Similarly, you can use a parameter for the day of the week value, and replace the parameter with either “Monday” or Tuesday.”

Understanding Parameterized Queries

A parameterized query is a query in which at least one of the fields of the WHERE clause is parameterized, i.e., the value of the field is specified by a question mark symbol ( ? ). For example, the following SQL statement is based on a query on the database in the sample Flight Reservation application:

SELECT Flights.Departure, Flights.Flight_Number, Flights.Day_Of_Week
FROM Flights Flights
WHERE (Flights.Departure=?) AND (Flights.Day_Of_Week=?)
  • SELECT defines the columns to include in the query.
  • FROM specifies the path of the database.
  • WHERE (optional) specifies the conditions, or filters to use in the query.
  • Departure is the parameter that represents the departure point of a flight.
  • Day_Of_Week is the parameter that represents the day of the week of a flight.

In order to execute a parameterized query, you must specify the values for the parameters.

Creating a Parameterized Database Checkpoint

You use a parameterized query to create a parameterized checkpoint. When you create a database checkpoint, you insert a db_check statement into your test script. When you parameterize the SQL statement in your checkpoint, the db_check function has a fourth, optional, argument: the parameter_array argument. A statement similar to the following is inserted into your test script:

db_check("list1.cdl", "dbvf1", NO_LIMIT, dbvf1_params);

The parameter_array argument will contain the values to substitute for the parameters in the parameterized checkpoint.

WinRunner cannot capture the expected result set when you record your test. Unlike regular database checkpoints, recording a parameterized checkpoint requires additional steps to capture the expected results set. Therefore, you must use array statements to add the values to substitute for the parameters. The array statements could be similar to the following:

dbvf1_params[1] = “Denver”;
dbvf1_params[2] = “Monday”;

You insert the array statements before the db_check statement in your test script. You must run the test in Update mode once to capture the expected results set before you run your test in Verify mode.

To insert a parameterized SQL statement into a db_check statement:

  1. Create the parameterized SQL statement using one of the following methods:
    • In Microsoft Query, once you have defined your query, add criteria whose values are a set of square brackets ( [ ] ). When you are done, click File > Exit and return to WinRunner. It may take several seconds to return to WinRunner.
    • If you are working with ODBC, enter a parameterized SQL statement, with a question mark symbol ( ? ) in place of each parameter, in the Database Checkpoint wizard.
  2. Finish creating the database checkpoint.
    • If you are creating a default database checkpoint, WinRunner captures the database query.
    • If you are creating a custom database checkpoint, the Check Database dialog box opens. You can select which checks to perform on the database. For additional information, see “Creating a Custom Check on a Database” Once you close the Check Database dialog box, WinRunner captures the database query.
  3. A message box prompts you with instructions, which are also described below. Click OK to close the message box.
  4. The WinRunner window is restored and a db_check statement similar to the following is inserted into your test script.

    db_check("list1.cdl", "dbvf1", NO_LIMIT, dbvf1_params);

  5. Create an array to provide values for the variables in the SQL statement, and insert these statements above the db_check statement. For example, you could insert the following lines in your test script:
  6. dbvf1_params[1] = “Denver”;
    dbvf1_params[2] = “Monday”;

    The array replaces the question marks ( ? ) in the SQL statement with the new values. Follow the guidelines below for adding an array in TSL to parameterize your SQL statements.

  7. Run your test in Update mode to update the SQL statement with these values.

After you have completed this procedure, you can run your test in Verify mode with the SQL statement. To change the parameters in the SQL statement, you modify the array in TSL.

Guidelines for Parameterizing SQL Statements

Follow the guidelines below when parameterizing SQL statements in db_check statements:

  • If the column is numeric, the parameter value can be either a text string or a number.
  • If the column is textual and the parameter value is textual, it can be a simple text string.
  • If the column is textual and the parameter value is a number, it should be enclosed in simple quotes ( ’ ’ ), e.g. “'100'”. Otherwise the user will receive a syntax error.
  • Special syntax is required for dates, times, and time stamps, as shown below:
Date {d ’1999-07-11’}
Time {t ’19:59:27’}
Time Stamp {ts ’1999-07-11 19:59:27’}

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

WinRunner Topics