Inserting Multiple Tag Items if not using Litmus Edge Integration
Litmus Production Record Database allows for several ways to insert data into its tables.
The recommended way when using Litmus Edge, is to make use of the MSSQL Integrations to insert data directly into the table PROREC_Misc_Transactions. As it allows to make use of the integrated store and forward capability, preventing data loss on a connection interruption.
But for cases, where this is not an option, a stored procedure can be called by a program or code snippet to insert data data. Any application which provided the capability to use an ODBC connection can be used like:
- Python
- C#
- .Net
- Excel
- Go
- Java
Execute Stored Procedure PROREC_Misc_WriteIntoTransactionTables
Variable name | Datatype | Description | Example |
|---|---|---|---|
@deviceid | VARCHAR(8000) | Pipe symbol "|" separated list of device names associated with each tag | Asset1|Asset2|Asset3 |
@registerid | VARCHAR(8000) | Pipe symbol "|" separated list of tag names | Temperature|Pressure|Speed |
@tagname | VARCHAR(8000) | Pipe symbol "|" separated list of Data Items for the Tag | Value|Alarm|5Min_Average |
@datatype | VARCHAR(8000) | Pipe symbol "|" separated list of timestamps in unix millisecond format enclosed by "##" -> this is also used to distinguish between a Production Record Item and a Tag | ##1678279064681## | ##1678279064682## | ##1678279064681## |
@value | VARCHAR(8000) | Pipe symbol "|" separated list of values for each tag | 45|High|780 |
@success | VARCHAR(8000) | Pipe symbol "|" separated list of quality of each tag (1 = Good, 0 = Bad) | 1|1|0 |
To insert a multiple value(s) for Tag Item(s), call the stored procedure PROREC_Misc_WriteIntoTransactionTables using the T-SQL EXEC or EXECUTE keyword and provide the Input variables. Below is an example using T-SQL in SQL Server Management Studio:
The stored procedure will verify that the provided values are correctly formatted and then write them into the table PROREC_ProRec_Transactions. This will trigger their immediate processing. The result can be read back from the log table PROREC_Tag_Log.
Below is an example using T-SQL in SQL Server Management Studio:
With the result returned
Message | Meaning | Example which Triggers |
|---|---|---|
One or more parameters are NULL, Please provide the correct values for each parameter. | One or more of the Input variables have no value provided. | EXEC [LE_ProductionRecord].[dbo].[PROREC_Misc_WriteIntoTransactionTables] |
One of the provided deviceid is invalid. The value is either the devicename for Tags or a comma separated list of the Node and optional Levelnames. | For at least one value for the Input Variable @deviceid a Unique Identifier such as "07045DE1-5AC1-4E12-B8F5-608EFC9C23C2" was provided | EXEC [LE_ProductionRecord].[dbo].[PROREC_Misc_WriteIntoTransactionTables]
@deviceid = N'07045DE1-5AC1-4E12-B8F5-608EFC9C23C2|Asset2|Asset3',
@registerid = N'Temperature|Pressure|Speed',
@tagname = N'Value|Alarm|5Min_Average',
@datatype = N'##1678279064681##|##1678279064682##|##1678279064681##',
@value = N'45|High|780',
@success = N'1|1|0' |
One of the provided registerid is invalid. The value is either the tagname for Tags or a comma separated list of the Identifiers. | For at least one value for the Input Variable @registerid a Unique Identifier such as "07045DE1-5AC1-4E12-B8F5-608EFC9C23C2" was provided | EXEC [LE_ProductionRecord].[dbo].[PROREC_Misc_WriteIntoTransactionTables]
@deviceid = N'Asset1|Asset2|Asset3 ',
@registerid = N'07045DE1-5AC1-4E12-B8F5-608EFC9C23C2|Pressure|Speed',
@tagname = N'Value|Alarm|5Min_Average',
@datatype = N'##1678279064681##|##1678279064682##|##1678279064681##',
@value = N'45|High|780',
@success = N'1|1|0' |