Teststand SQL database

[BigAngryHillMan]

There seem to be very few examples of how to get results out of the standard Teststand SQL database. There is a top level table called UUT_RESULT but it may not be obvious to a new user how to get the results from a test run in the UUT_RESULT table with a given ID GUID.

To this end I give an example below in the hope it saves someone else the pain of surfing the internet trying to find an example.

The SQL script below when run will return all the results for a test. Obviously you will still need to read up on SQL but it is a good starting point.

There is a comment in the code on how to make sure only tests with results are returned.

The SQL script is a lot more readable when coloured in a SQL editor.

DECLARE @UGID [nvarchar](50) SET @UGID = 'C26760D9-3FE4-4500-99FA-6B4B26341563' SELECT        	RTRIM (UUT_RESULT.UUT_SERIAL_NUMBER) AS UUT_SERIAL_NUMBER,RTRIM (UUT_RESULT.START_DATE_TIME) AS START_DATE_TIME, RTRIM (UUT_RESULT.USER_LOGIN_NAME) AS USER_LOGIN_NAME,              	RTRIM(UUT_RESULT.STATION_ID) AS STATION_ID,              	RTRIM (UUT_RESULT.UUT_STATUS) AS UUT_STATUS,RTRIM (STEP_RESULT.ORDER_NUMBER) AS ORDER_NUMBER,RTRIM(STEP_RESULT.STEP_GROUP) AS STEP_GROUP, RTRIM(STEP_SEQCALL.SEQUENCE_NAME) AS SEQUENCE_NAME,              	RTRIM(STEP_RESULT.STEP_NAME) AS STEP_NAME,RTRIM(PROP_NUMERICLIMIT.COMP_OPERATOR) AS COMP_OPERATOR, RTRIM(STEP_RESULT.STEP_TYPE) AS STEP_TYPE, RTRIM(STEP_RESULT.STATUS) AS STATUS,RTRIM(PROP_RESULT.DATA) AS RESULT,              	RTRIM(PROP_NUMERICLIMIT.LOW_LIMIT) AS LOW_LIMIT,RTRIM(PROP_NUMERICLIMIT.HIGH_LIMIT) AS HIGH_LIMIT,              	RTRIM(STEP_RESULT.MODULE_TIME) AS MODULE_TIME,RTRIM(STEP_SEQCALL.SEQUENCE_FILE_PATH) AS SEQUENCE_FILE_PATH,RTRIM(PROP_NUMERICLIMIT.UNITS) AS UNITS,              	RTRIM(UUT_RESULT.EXECUTION_TIME) AS EXECUTION_TIME,              	RTRIM(STEP_RESULT.ERROR_CODE) AS ERROR_CODE,RTRIM(STEP_RESULT.ERROR_MESSAGE) AS ERROR_MESSAGE,RTRIM(UUT_RESULT.UUT_ERROR_CODE) AS UUT_ERROR_CODE,RTRIM(UUT_RESULT.UUT_ERROR_MESSAGE) AS UUT_ERROR_MESSAGE                  	FROM 		UUT_RESULT INNER JOIN                  	STEP_RESULT INNER JOIN                  	STEP_SEQCALL ON STEP_RESULT.STEP_PARENT = STEP_SEQCALL.STEP_RESULT ON UUT_RESULT.ID = STEP_RESULT.UUT_RESULT LEFT OUTER /*INNER */JOIN -- SWAP LEFT OUTER FOR INNER TO miss out steps without results.                  	PROP_NUMERICLIMIT INNER JOIN                  	PROP_RESULT ON PROP_NUMERICLIMIT.PROP_RESULT = PROP_RESULT.ID ON STEP_RESULT.ID = PROP_RESULT.STEP_RESULTWHERE 	(STEP_RESULT.UUT_RESULT = @UGID)ORDER BY STEP_RESULT.ORDER_NUMBER

[Ray Abram]

I was unable to run the query in MSSQL... Copilot was able to rearrange a few things and resulted inn the following working query 

next step is to use the LabVIEW Database toolkit to execute the querry

DECLARE @UGID nvarchar(50);
SET @UGID = N'C0CFF9BF-A8A5-11F0-9E18-4CB04AE64A78';

SELECT
    RTRIM(ur.UUT_SERIAL_NUMBER)       AS UUT_SERIAL_NUMBER,
    RTRIM(CONVERT(varchar(23), ur.START_DATE_TIME, 121)) AS START_DATE_TIME,  -- ISO 8601
    RTRIM(ur.USER_LOGIN_NAME)          AS USER_LOGIN_NAME,
    RTRIM(ur.STATION_ID)               AS STATION_ID,            -- <-- moved to UUT_RESULT
    RTRIM(ur.UUT_STATUS)               AS UUT_STATUS,
    sr.ORDER_NUMBER,                   -- keep numeric for sorting
    RTRIM(sr.STEP_GROUP)               AS STEP_GROUP,
    RTRIM(sc.SEQUENCE_NAME)            AS SEQUENCE_NAME,
    RTRIM(sr.STEP_NAME)                AS STEP_NAME,
    RTRIM(pnl.COMP_OPERATOR)           AS COMP_OPERATOR,
    RTRIM(sr.STEP_TYPE)                AS STEP_TYPE,
    RTRIM(sr.STATUS)                   AS STATUS,
    RTRIM(pr.DATA)                     AS RESULT,
    CAST(pnl.LOW_LIMIT  AS nvarchar(50))   AS LOW_LIMIT,
    CAST(pnl.HIGH_LIMIT AS nvarchar(50))   AS HIGH_LIMIT,
    sr.MODULE_TIME                     AS MODULE_TIME,
    RTRIM(sc.SEQUENCE_FILE_PATH)       AS SEQUENCE_FILE_PATH,
    RTRIM(pnl.UNITS)                   AS UNITS,
    ur.EXECUTION_TIME                  AS EXECUTION_TIME,
    sr.ERROR_CODE                      AS ERROR_CODE,
    RTRIM(sr.ERROR_MESSAGE)            AS ERROR_MESSAGE,
    ur.UUT_ERROR_CODE                  AS UUT_ERROR_CODE,
    RTRIM(ur.UUT_ERROR_MESSAGE)        AS UUT_ERROR_MESSAGE
FROM dbo.UUT_RESULT      AS ur
JOIN dbo.STEP_RESULT     AS sr ON ur.ID           = sr.UUT_RESULT
JOIN dbo.STEP_SEQCALL    AS sc ON sr.STEP_PARENT  = sc.STEP_RESULT
LEFT JOIN dbo.PROP_RESULT       AS pr  ON sr.ID          = pr.STEP_RESULT
LEFT JOIN dbo.PROP_NUMERICLIMIT AS pnl ON pnl.PROP_RESULT = pr.ID
WHERE sr.UUT_RESULT = @UGID
ORDER BY sr.ORDER_NUMBER;
 

[Ray Abram]

here is a version without the DECLARE, which LabVIEW's Database tool kit can execute using the "Conn Execute.vi"
Here is how to use the query with LabVIEW's Database toolkit

Here is the updated query
SELECT
    RTRIM(ur.UUT_SERIAL_NUMBER)       AS UUT_SERIAL_NUMBER,
    RTRIM(CONVERT(varchar(23), ur.START_DATE_TIME, 121)) AS START_DATE_TIME,
    RTRIM(ur.USER_LOGIN_NAME)          AS USER_LOGIN_NAME,
    RTRIM(ur.STATION_ID)               AS STATION_ID,
    RTRIM(ur.UUT_STATUS)               AS UUT_STATUS,
    sr.ORDER_NUMBER,
    RTRIM(sr.STEP_GROUP)               AS STEP_GROUP,
    RTRIM(sc.SEQUENCE_NAME)            AS SEQUENCE_NAME,
    RTRIM(sr.STEP_NAME)                AS STEP_NAME,
    RTRIM(pnl.COMP_OPERATOR)           AS COMP_OPERATOR,
    RTRIM(sr.STEP_TYPE)                AS STEP_TYPE,
    RTRIM(sr.STATUS)                   AS STATUS,
    RTRIM(pr.DATA)                     AS RESULT,
    CAST(pnl.LOW_LIMIT  AS nvarchar(50))   AS LOW_LIMIT,
    CAST(pnl.HIGH_LIMIT AS nvarchar(50))   AS HIGH_LIMIT,
    sr.MODULE_TIME                     AS MODULE_TIME,
    RTRIM(sc.SEQUENCE_FILE_PATH)       AS SEQUENCE_FILE_PATH,
    RTRIM(pnl.UNITS)                   AS UNITS,
    ur.EXECUTION_TIME                  AS EXECUTION_TIME,
    sr.ERROR_CODE                      AS ERROR_CODE,
    RTRIM(sr.ERROR_MESSAGE)            AS ERROR_MESSAGE,
    ur.UUT_ERROR_CODE                  AS UUT_ERROR_CODE,
    RTRIM(ur.UUT_ERROR_MESSAGE)        AS UUT_ERROR_MESSAGE
FROM dbo.UUT_RESULT      AS ur
JOIN dbo.STEP_RESULT     AS sr ON ur.ID           = sr.UUT_RESULT
JOIN dbo.STEP_SEQCALL    AS sc ON sr.STEP_PARENT  = sc.STEP_RESULT
LEFT JOIN dbo.PROP_RESULT       AS pr  ON sr.ID          = pr.STEP_RESULT
LEFT JOIN dbo.PROP_NUMERICLIMIT AS pnl ON pnl.PROP_RESULT = pr.ID
WHERE sr.UUT_RESULT = '%s'
ORDER BY sr.ORDER_NUMBER;