In-depth reporting and analysis of Cycle tests often requires the use of a database to log all of the details of your Cycle executions, but not all users have a readily available database to use for this.
Cycle comes pre-packaged with an H2 database. H2 is a serverless database that allows the Cycle user to create a local database file on the machine running cycle. This database can be used to store and maintain data for driving test execution as well as storing reporting data. This document describes an approach to use an H2 database to store easily maintained data and use it to drive test execution. This approach can also be used with a MOCA database or any other database with a connection step in Cycle.
H2 Database Engine
The H2 database console can be downloaded using the link below. Downloading the H2 database to your local machine gives you the ability to use the H2 console to create, connect to, and maintain a local database without the need for a database server.
The example database and feature included a the end of this document show one potential approach for using variable test data stored in a database rather than hard-coding variable data values within a feature or using a csv file to store data.
Creating a Local H2 Database
After installing the H2 Database Engine downloaded from the link above, you will have the H2 Console available to use on your machine. This console opens a web based front end for the H2 DB that allows you to create and maintain your local database. The H2 database can also be accessed using any SQL query tool that allows JDBC connections.
Run the H2 Console application to open the H2 login page. You will see a login page similar to the one below:
The JDBC URL is the location of your database. In this example, I have created a directory under my Windows user directory called ~\Cycle\H2_Database_Project\local_database\ and I have named my database “sample_H2_db”.
I have added the additional parameter to the JDBC URL “;AUTO_SERVER=TRUE”. This option allows me to have the database open in my web browser and allow Cycle to access the DB at the same time. If AUTO_SERVER is not enabled, then you must ensure the DB session is disconnected for Cycle to be able to access the DB. Only one active connection is allowed at a time if this parameter is not used as part of the JDBC URL.
The full JDBC_URL for the sample project in this document is:
I have specified a user name of “cycle” and a password of “cyclepwd” as part of my connection parameters.
Creating a new database is as simple as populating the JDBC URL, User Name, and Password fields with new data. The H2 console will attempt to connect to an existing database at the JDBC URL. If no database exists, then H2 console will create a new database using the parameters provided.
The H2 Console web browser based front end allows you to write SQL statements and execute them against your database.
In addition to writing queries to create tables, insert data, update data, delete data, etc…, the console also gives you the ability to edit records directly in the console with the use of the edit button under the results grid.
The SQL used to create the table and insert records for the example feature used by this document can be downloaded here:
There are several approaches for storing test data to drive test execution. Variable test data can be written directly into the feature using steps to assign values to variables or it can be stored in an Example list for a Scenario outline.
Variable test data can also be stored in a csv file with each record being read one record at a time and values assigned to variables. Both of those approaches have various drawbacks including “hardcoding” data into features and being forced to read every record of a csv one line at a time.
Using a database driven approach allows a more dynamic method of storing, maintaining, and retrieving test data.
The example Feature detailed in this document demonstrates an approach using a simple H2 database table to store test data used by three separate Scenarios.
The following step is used to connect to an H2 database:
I connect to H2 database at “<URL>” logged in as “<USERNAME>” with passwords “<PASSWORDS>”
The sample feature assigns the URL connection string to a variable and constructs the connection step as shown below:
The following step is used to run SQL against the H2 DB after establishing connection:
I execute SQL “<SQL_STATEMENTS>”
Associating Data with Scenarios
The example Feature includes three separate Scenarios that perform three different tests.
Scenario 1: Checks a test value to see if it is less than 10
Scenario 2: Checks a test value to see if it is greater than 10
Scenario 3: Checks a test value to see if it is equal to 10
All three scenarios have their own set of variable test data, but none of the Scenarios have variable test data hard-coded within the feature. All three retrieve their test data directly from the H2 database.
To achieve this, I have created a table called “cycle_scenario_data” in my H2 database. There are three columns within this example table.
SCENARIO_NAME – I am using this column to tie my Cycle Scenario to the appropriate test data within the table
TEST_NUMBER – This part of the primary key (scenario_name, test_number) that allows me to run the same Scenario multiple times with variable test data during each execution of that Scenario
TEST_VALUE – This is the variable data that I will be using within my Cycle Scenario
My example table is populated with the following data:
In the data above, the Scenario “equal_to_ten” has two different sets of data for Cycle to execute, the Scenario “greater_than_ten” has three sets of data, and Scenario “less_than_ten” has two sets of data.
An approach similar to this can be used to test one single Scenario against multiple datasets including multiple order types, part numbers, part families, inventory statuses, locations, etc…
Getting Test Data During Execution
At the time of the writing of this article, Cycle does not support passing variables to “.sql” files the way it supports passing MOCA environment variables to “.msql” files. This means “.sql” files will need to be hardcoded for selecting data, or the SQL statements will need to be written within the feature.
For our example Feature, I have chosen to construct and execute the SQL statements to retrieve test data within the feature itself. This allows for using the “I assign VARIABLE by combing” step to create queries with variable parameters.
This example has two SQL statements used to retrieve data, both of which are in their own @wip Scenarios:
The “Get Test Number Count” Scenario queries the database to see how many different iterations of test data exist for the given Scenario.
The “Get Test Data” Scenario queries the database and assigns the data from a specific row number to the variable used by the test.
In the examples above using the data I loaded previously, queries for the Scenario “less_than_ten” will return a count of “2” and test_value = “7” for row 0 and test_value = ‘12’ for row 1.
Using Control Structures with Data to Drive Tests
The example approach in this feature uses a WHILE loop control structure to retrieve the data for the test and then runs the test Scenario with the appropriate data associated to that Scenario.
The first step is to assign the variable for the Scenario name:
Then get a count of the number of times the Scenario will be executed:
Reset the test_data_row counter that is used by the Get Test Data Scenario to ensure we execute the Scenario the appropriate number of times based on our test data:
Next, start the WHILE loop and stay in the loop as long as the test_data_row is less than the number of test_count:
Within the WHILE loop, run the Scenario to get the variable test data and assign it to the test variable for the specific iteration you are executing:
Then execute the test Scenario:
After executing the test Scenario, increment the row_count:
Once the Scenario runs with the last set of test data, we break from the while loop and run the After Scenario.
Cycle then proceeds to run the remaining Scenarios using the same approach to retrieve test data as the first Scenario:
The query to get test data returns only rows associated to the specific scenario_name we are querying unlike the approach of reading records from a single csv requiring us to evaluate each line of data to see if it applies to our current test.