Can I use Cycle's Data Store reporting even if I don't have a database available?

Can I use Cycle's Data Store reporting even if I don't have a database available?

Problem

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.

Solution

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.

http://www.h2database.com/html/main.html

Cycle 2.4/2.5 contains H2 Driver version 1.4.197. Use the Latest Stable release, 1.4.199, on the H2 download page to avoid compatibility issues.

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:

H2_1.png

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:

jdbc:h2:~\Cycle\H2_Database_Project\local_database\sample_H2_db;AUTO_SERVER=TRUE

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.

H2 Console

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.

H2_2.png

The SQL used to create the table and insert records for the example feature used by this document can be downloaded here:

sample_db_sql_scripts.sql

Example

If you have the "Write results to data store" setting turned on while running a test, mceclip0.png

Cycle will write the results of the execution into the database specified in the DATA STORE CONNECTION section of your reporting settings.

H2 Example

mceclip1.png

Note: If you are using an H2 database for Data Store reporting and also have an open session connected to it in a separate DB client, Cycle will not be able to simultaneously connect to it unless you include ;AUTO_SERVER=TRUE at the end of your Server URL as seen in the example.

Test Connection

This will confirm that the credentials supplied are in fact able to log in and create a session on the database.

Create Database Tables

This will attempt to create the tables in the specified Database for logging results. While this is not required it will help determine whether the user has the necessary permissions to write to the database.

 

The tables created will resemble the following:

Database Tables created


**Note:** At minimum, the user configured in your connection settings should have `select`,`insert`, and `update` privileges to these tables.

JDBC Data store ERD

JDBC Data store ERD

Sample Queries

To get the max test execution id for the most recent Cycle execution

SELECT
MAX(execution_id)
FROM
CYCLE_EXECUTION_RESULTS;

Use the result of that query and put in in place of <EXECUTION_ID>. This produces a summary report of that execution.

 SELECT
f.name,
f.status,
f.file_uri,
s.name,
s.status,
COUNT(CASE step.status WHEN 'Pass' THEN 1 WHEN 'True' THEN 1 ELSE NULL END) AS passed_steps,
COUNT(CASE step.status WHEN 'Fail' THEN 1 WHEN 'False' THEN 1 ELSE NULL END) AS failed_steps
FROM
cycle_execution_results AS f
JOIN cycle_execution_results AS s ON
s.execution_id = f.execution_id
AND s.node_type = 'Scenario'
AND s.sending_node_id = f.node_id
LEFT JOIN cycle_execution_results AS step ON
step.execution_id = s.execution_id
AND step.node_type = 'Step'
WHERE
f.execution_id = '<EXECUTION_ID>'
AND f.node_type = 'Feature'
GROUP BY
f.name,
f.status,
f.file_uri,
s.name,
s.status;

Sample Output

mceclip2.png

    • Related Articles

    • 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, ...
    • Using Cycle's Data Extract Tool

      The Data Extract tool allows you to easily create CSV files from an existing database. The Extract Data window can be accessed via the "Tools > Extract Data..." menu item in Cycle. It can also be accessed from the Inspector panel with the ...
    • 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, ...
    • Can I use Cycle to run MSQL scripts?

      Problem More complex scripts can be long and hard to read or understand for less technical users. Breaking the overall flow and SOP use case of a Cycle Feature. Solution Short MOCA commands and local syntax statements can be built in-line with the “I ...
    • How to use Scenario tag filters

      Cycle 2.5 introduces the ability to filter tags during Feature and Playlist execution. This allows the Cycle user more control over which Scenarios within a Feature or Playlist are executed while also allowing more flexibility when assigning tags to ...