You can save results of your tests into a SQL server database.
You can easily store results of your tests in 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@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.SaveCatTestResultsin the example above)
- It will also create a table with name
dbo.CatTestResultif 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.