How to use a SQL query as a Scenario Outline data source

How to use a SQL query as a Scenario Outline data source

With the enhancements to Scenario Outlines, it is now possible to use multiple sources for test parameter data. Previously, parameters were set in the Feature in an Examples section specified and maintained by the user. The addition of Example Row, CSV, SQL, MOCA, and Datastore sources provide more dynamic permutations and less maintenance.

This article details what the SQL Examples data source is, as well as why and how to use it.

What are SQL Examples?

SQL stands for Structured Query Language and is a mechanism for interaction with databases. SQL Examples in Cycle is the keyword used in Scenario Outlines to use a SQL query result set as the test parameters to be used in execution. The query is contained in a .sql file. The SQL query will return column names and those names will become the Cycle variable names. Each row in the result set will generate an iteration of the Scenario Outline.

Example:

Scenario Outline: SQL Test
SQL Examples:scripts\wh.sql connected to <DATABASE_CONNECTION> logged in as <DATABASE_CREDENTIALS>

Why use a SQL query?

Using a SQL query for Scenario Outline provides a dynamic way to build test parameter iterations. Static data values are not required. Once the query is built that generates the necessary parameters to use in the test it can be reused over and over. The data elements themselves may change but the query remains the same and therefore requires little maintenance if any.

How to use SQL queries?

First and foremost you will need to have valid SQL syntax in your .sql file. For help writing proper SQL there are many tutorials available for free on the web.

Also, there are many open source tools to use while developing the query for your .sql file. A very popular one is DBeaver due to its ability to connect to many database platforms.

SQL Examples are used with the keyword Scenario Outline:. As in the above example illustrates, it is declared on the line immediately following Scenario Outline.

Another critical step to using SQL files in Scenario Outlines is you must provide Connection and Credentials for access to your database.

Before going any further please review the KB article on setting up Connections and Credentials in Cycle.

Once you have your Connections and Credentials saved, they are referenced with the following syntax immediately following the file path.

Scenario Outline: SQL Test
SQL Examples:scripts\wh.sql connected to <DATABASE_CONNECTION> logged in as <DATABASE_CREDENTIALS>

If you reference saved connections or credentials by an incorrect name or that have not yet been created you will receive the following error at execution.

mceclip2.png

If your connection string is invalid you will receive an error similar to below at execution.

mceclip3.png

If your credentials are invalid you will receive an error similar to below at execution.

mceclip4.png

The SQL Examples keyword requires a Project relative directory path to a valid SQL file. In the example above we provided scripts\wh.sql as the path. The full path would be similar to C:\Cycle\PROJECT_DIR\scripts\wh.sql with PROJECT_DIR being the example Project directory.

When the Scenario Outline is executed Cycle converts the column names from the result set to variable names as chevron variables and the subsequent data rows as the values to use in the Steps. Each data row will be an iteration of the Scenario Outline executions steps.

For example if you have a SQL file formatted like:

/* In my DB this query will return 2 rows*/
SELECT
wh_id
FROM
wh;

/*Result set*/
mceclip5.png

then Steps use the chevron and variable replacement to use the information provided.

Given I echo <wh_id>

When the Step executes <wh_id> will be replaced with WMD1 in the first iteration and WMD2 in the second.

Putting it all together

In this example, the Steps verify successful connection for a specific terminal device. The SQL file contains a query to return the list of terminal devices to validate.

SQL file example:

SELECT term_id as TERMINAL_ID
FROM rf_term_mst
where wh_id = 'WMD1'
and term_typ = 'vehicle';
Scenario Outline:Vehicle Terminal Test
SQL Examples:Datasets\vehicle_terminals.sql connected to TEST_DB logged in as TEST_CREDENTIALS
Then I open terminal connected to "HOST:PORT" for terminal <term_id>
If I see "Terminal ID" on line 1 in terminal within 5 seconds
Then I enter <term_id> in terminal
Endif
Once I see "User ID" in terminal
Then I close terminal

When new terminals are added to the system that have the same criteria they will automatically get validated at Scenario Outline execution with no change to the SQL file or the Feature.

The Output in the Output Panel shows each Example numbered individually for troubleshooting.

mceclip0.png

    • Related Articles

    • How to use a CSV file as a Scenario Outline data source

      With the enhancements to Scenario Outlines, it is now possible to use multiple sources for test parameter data. Previously, parameters were set in the Feature in an Examples section specified and maintained by the user. The addition of Example Row, ...
    • How to use Datastore Examples as a Scenario Outline data source

      With the enhancements to Scenario Outlines, it is now possible to use multiple sources for test parameter data. Previously, parameters were set in the Feature in an Examples section specified and maintained by the user. The addition of Example Row, ...
    • What improvements to Scenario Outlines should I be aware of?

      What do we know about Scenario Outlines? Scenario Outlines are similar to regular Scenarios except that a Scenario Outline executes multiple times, once for each row of data it is using. What is staying the same? The keyword Scenario Outline: The ...
    • How to store Connections and Credentials in Cycle

      Cycle 2.5 includes the ability to save Connections and Credentials for your systems under test. Using named Connections and Credentials streamlines and simplifies declaring system access, makes Features look cleaner and promotes reusability of “set ...
    • How to use Example Row as a Scenario Outline data source

      With the enhancements to Scenario Outlines, it is now possible to use multiple sources for test parameter data. Previously, parameters were set in the Feature in an Examples section specified and maintained by the user. The addition of Example Row, ...