SQL Server

You can save results of your tests into a SQL server database.

Introduction #

You can easily store results of your tests into a table in MS SQL server:

You can then easily create nice reports using your favorite reporting platform, such as Power BI or SSRS.

How to Instruct CAT to Save Test Results #

CAT saves results to a database you have defined in Data Sources section of your project file. (If you load list of data sources from an external provider, that will also work.)

Data Sources:
- Name: MyDWH
  Provider: SqlServer@2  # both SqlServer@1 and SqlServer@2 are supported
  ConnectionString: "%MY_DWH_CONNECTION_STRING%"
Tests:
- Name: .....   # abbreviated
Ouptut:
- Database: MyDWH # name of an existing data source
  Table: dbo.CatTestResults

In this case, CAT will insert results of tests into the table of your choice. If the table does not exist, CAT will attempt to create it.

Second option is to use a stored procedure:

Ouptut:
- Database: MyDWH # name of an existing data source
  Procedure: dbo.SaveCatTestResults

In this case, CAT will use a stored procedure to save the results of tests into a table. Feel free to tweak the procedure to suite your needs.

If the procedure does not exist:

• CAT will attempt to create the procedure (with the name you specified, dbo.SaveCatTestResults in the example above)
• It will also create a table with name dbo.CatTestResult if it does not exist.

Output Test Results into a Table #

Do you want to create the table manually? Makes perfect sense. If CAT has only reading permissions on the database, which is highly recommended, it will not be able to create the table or procedure if it does not exist.

Just run SSMS or other client tool you use and run this script to create the table:

CREATE TABLE [dbo].[CatTestResult]
(
    TestResultID INT IDENTITY PRIMARY KEY,
    ExecutionGuid UNIQUEIDENTIFIER NOT NULL,
    [Project] NVARCHAR(200) NOT NULL,
    [FullName] NVARCHAR(700) NOT NULL,
    TestResult VARCHAR(30) NOT NULL,
    StartedOn DATETIME2 NOT NULL,
    FinishedOn DATETIME2 NOT NULL,
    [RawMessage] NVARCHAR(MAX) NULL,
    [Exception] NVARCHAR(MAX) NULL,
    [Description] NVARCHAR(MAX) NULL,
    [FirstDataSource] NVARCHAR(200) NOT NULL,
    FirstQuery NVARCHAR(MAX) NOT NULL,
    SecondDataSource NVARCHAR(200) NULL,
    SecondQuery NVARCHAR(MAX) NULL,
    Expectation VARCHAR(50) NOT NULL,
    [Key] NVARCHAR(200) NULL,
    Metadata NVARCHAR(300) NULL,
    Tags NVARCHAR(1000) NULL,
    Suite NVARCHAR(200) NULL,
    TestCase NVARCHAR(200) NULL,
    [Order] INT NULL,
    [Name] NVARCHAR(300) NULL,
    [Timeout] INT NULL,
    MaximumErrorsLogged INT NULL,
    ThreadNumber INT NOT NULL,
    [Message] NVARCHAR(MAX) NULL,
    NumberOfErrors INT NULL,
    LogNumberOfErrors BIT NULL
);
GO

Don’t forget CAT is highly configurable, the name of the table can be anything. Change it in your script, change it in the Outputs section of the project file. CAT process must have INSERT permission on the table.

The structure of the table is not fixed. CAT checks the structure of the table you provided in the project file and adapts to it. That means - if you are not interested into some columns, just drop them. Do you want only three columns, e.g. FullName, TestResult and StartedOn? No problem, CAT will happily save the results into your table with those three columns only. See the complete list of properties supported on the output.

You can add additional custom columns to the table, but they must be either nullable or must have defined a default value. To avoid conflict with any new future columns, use some prefix or suffix for your column names (name of your company, underscore, …).

Use Stored Procedure Instead #

You might prefer a stored procedure. It allows you to perform additional actions, such as enrich your own logs, run other processes when a specific test(s) fail, …

Here is the script. Again, you can change name of the stored procedure, name of the table it uses, omit some parameters you don’t need, use your naming conventions, …

CREATE PROCEDURE dbo.SaveCatTestResults
(
    @ExecutionGuid UNIQUEIDENTIFIER,
    @Project NVARCHAR(200),
    @FullName NVARCHAR(700),
    @TestResult VARCHAR(30),
    @StartedOn DATETIME2,
    @FinishedOn DATETIME2,
    @RawMessage NVARCHAR(MAX),
    @Exception NVARCHAR(MAX),
    @Description NVARCHAR(MAX),
    @FirstDataSource NVARCHAR(200),
    @FirstQuery NVARCHAR(MAX),
    @SecondDataSource NVARCHAR(200),
    @SecondQuery NVARCHAR(MAX),
    @Expectation VARCHAR(50),
    @Key NVARCHAR(200),
    @Metadata NVARCHAR(300),
    @Tags NVARCHAR(1000),
    @Suite NVARCHAR(200),
    @TestCase NVARCHAR(200),
    @Order INT,
    @Name NVARCHAR(300),
    @Timeout INT,
    @MaximumErrorsLogged INT,
    @ThreadNumber INT,
    @Message NVARCHAR(MAX),
    @NumberOfErrors INT,
    @LogNumberOfErrors BIT
)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO [dbo].[CatTestResult]
    (
        ExecutionGuid, Project, FullName, TestResult, StartedOn, FinishedOn, RawMessage, Exception, Description,
        FirstDataSource, FirstQuery, SecondDataSource, SecondQuery, Expectation,
        [Key], [Metadata], [Tags], Suite, TestCase, [Order], [Name],
        [Timeout], MaximumErrorsLogged, ThreadNumber, [Message], NumberOfErrors, LogNumberOfErrors
    )
    VALUES
    (
        @ExecutionGuid, @Project, @FullName, @TestResult, @StartedOn, @FinishedOn, @RawMessage, @Exception, @Description,
        @FirstDataSource, @FirstQuery, @SecondDataSource, @SecondQuery, @Expectation,
        @Key, @Metadata, @Tags, @Suite, @TestCase, @Order, @Name,
        @Timeout, @MaximumErrorsLogged, @ThreadNumber, @Message, @NumberOfErrors, @LogNumberOfErrors
      );
END
GO

The list of parameters of the procedure is NOT fixed. CAT again checks the metadata of the procedure before it executes tests. CAT will call the procedure with only the parameters you defined.

How Does it Work? #

Before CAT starts executing tests, it “prepares” a command for saving test results - one for each thread. It checks the metadata of table/procedure you specified in the project file and prepares INSERT or EXEC command with only columns/parameters that are needed. If the table or procedure does not exist, CAT will attempt to create it (and will fail if it does not have permissions).

Immediately after a test is executed, CAT attempts to either insert a row in the results table (if you configured with Table: tablename) or execute a procedure (if you configured Procedure: ProcedureName).

This feature works even if you run tests in parallel - each thread keeps its own connection to the database for storing outputs.