SQL Server

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

Introduction

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

MS SQL server CAT output in a table

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@1
  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.

I Want to Create Table / Procedure 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]( -- CHANGE TABLE NAME IF YOU WANT
	[TestResultID] [INT] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[ExecutionGuid] [UNIQUEIDENTIFIER] NOT NULL,
	[Project] [NVARCHAR](200) NOT NULL,
	[FullName] [NVARCHAR](700) NOT NULL,
	[TestResult] [VARCHAR](30) NOT NULL,
	[StartedOn] [DATETIME2](7) NOT NULL,
	[FinishedOn] [DATETIME2](7) NOT NULL,
	[Message] [NVARCHAR](MAX) NULL,
	[Suite] [NVARCHAR](200) NULL,
	[TestCase] [NVARCHAR](200) NULL,
	[Order] [INT] NULL,
	[Name] [NVARCHAR](300) NULL,
	[FirstDataSource] [NVARCHAR](200) NOT NULL,
	[FirstQuery] [NVARCHAR](MAX) NOT NULL,
	[SecondDataSource] [NVARCHAR](200) NULL,
	[SecondQuery] [NVARCHAR](MAX) NULL,
	[Expectation] [VARCHAR](50) NOT NULL,
	[Timeout] [INT] NULL,
	[MaximumErrorsLogged] [INT] NULL,
	[ThreadNumber] [INT] NOT NULL
)

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.

The structure of the table is fixed. If you use Table: in the outputs configuration, CAT expects table exactly in that format. It may have additional nullable columns or columns with defaults though.

You might prefer a stored procedure - here is the script. Again, you can change name of the stored procedure and name of the table it uses:

CREATE PROCEDURE [CAT].[SaveTestResult]
(
    @ExecutionGuid UNIQUEIDENTIFIER,
    @Project NVARCHAR(200),
    @FullName NVARCHAR(700),
    @TestResult VARCHAR(30),
    @StartedOn DATETIME2,
    @FinishedOn DATETIME2,
    @Message NVARCHAR(MAX),
    @Suite NVARCHAR(200),
    @TestCase NVARCHAR(200),
    @Order INT,
    @Name NVARCHAR(300),
    @FirstDataSource NVARCHAR(200),
    @FirstQuery NVARCHAR(MAX),
    @SecondDataSource NVARCHAR(200),
    @SecondQuery NVARCHAR(MAX),
    @Expectation VARCHAR(50),
    @Timeout INT,
    @MaximumErrorsLogged INT,
    @ThreadNumber INT
)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.CatTestResult
    (
        ExecutionGuid, Project, FullName, TestResult, StartedOn, FinishedOn, Message, Suite, TestCase, [Order], [Name], FirstDataSource,
        FirstQuery, SecondDataSource, SecondQuery, Expectation, [Timeout], MaximumErrorsLogged, ThreadNumber
    )
    VALUES
    (@ExecutionGuid, @Project, @FullName, @TestResult, @StartedOn, @FinishedOn, @Message, @Suite, @TestCase, @Order, @Name, @FirstDataSource,
      @FirstQuery, @SecondDataSource, @SecondQuery, @Expectation, @Timeout, @MaximumErrorsLogged, @ThreadNumber);
END

The list of parameters of the procedure is fixed. CAT will call it with those. If you want to store only a subset of the information, adjust the table and the procedure body. The name of the procedure may be anything, just change it in the script and in the Outputs section in your project file.

How Does it Work?

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). CAT expects a fixed list of columns for the table. If some of them are missing, the INSERT command CAT runs will fail. The same for the stored procedure - CAT expects a fixed set of input parameters. But inside the procedure, you can already do whatever you want (and thus e.g., save ony subset of the information the procedure received via parameters).

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

← Odbc@1
Troubleshooting →