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.

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

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

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*/

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.