Query examples

Discover the benefits of SIA Connect and MS SQL utilizing user-written queries to select and insert any OT-data and execute stored procedures

+ More

Items in an MS SQL instance can be used to execute any queries, that the user is authorized to perform. Below is an outline of some of the more common queries.

This article will be split in two sections:

Reading from a server
Writing to a server

Reading from a server

When reading from a server, the user can input the desired query into the Query Default in the item setup. Below are some standard queries

SIA Connect will return values from the database in JSON formats. To unwrap the JSON and use the raw values, please refer to this guide

Query	Result	Notes
`select * from (table name)`	`[{"Test1":"1","Test2":"2","id":"12"},{"Test1":"15","Test2":"25","id":"33"}]` The result will be an array of JSON objects, where each object will represent a row in your table	Selecting all, especially in a large table, can be hard to process. This should be used carefully, as some tables can contain thousands or millions of entries. These will all be present as the value of your item
`select top(1) * from (table name)`	`[{"Test1":"1","Test2":"2","id":"12"}]`	This will give you a single entry from the table. The top(1) can be changed to any other number, and will be reflected in the amount of rows returned. It will return the first entry/entries in the table, not the last ones
`select top(1) * from (table name) order by id desc`	`[{"Test1":"15","Test2":"25","id":"33"}]`	This will return the last entry/entries in the table. The top(1) can be changed to any other number and will be reflected in the amount of rows returned. This is only possible because the ID is created in the table. You will need to order the query by an existing column
`select * from (table name) where (column name) = %SIA_Item.VALUE%`	`[{"Test1":"SIA Item Value","Test2":"25","id":"33"}]`	This will return any rows where the column value is equal to the value in the SIA Connect item. This is useful when you want to use scripting in your queries

SIA Connect scripting variables

To learn more about scripting variables, please refer to this guide

Writing to a Server

When writing to a server, the user can input the desired query into the Input Template in the item setup. Below are some standard queries

Mappings

Mappings will trigger the value of the Input Template, and execute the query accordingly. To learn more about mappings, please visit this guide

Query Result Notes

Query	Result	Notes
`insert into (table name) values (%Sender_item.VALUE%)`	A new row is created with the current value of the sender item	Sender_item is a placeholder. Please replace this with your own sender's item-name
`update (table name) set (column name) = %Sender_item.VALUE% Optional where (identifier) = (example_identifier)`	Updates the value of the column in all rows with the value of the sender item	This query will update the value of all the affected rows Note: If there is no WHERE-clause, the query will update all values
`exec STORED_PROCEDURE_NAME Optional @variable1 = %Sender_item.VALUE%`	This will execute a stored procedure. Some stored procedures have variables attached to them, which will have to be filled out

insert into (table name) values (%Sender_item.VALUE%)

A new row is created with the current value of the sender item

Sender_item is a placeholder. Please replace this with your own sender's item-name

update (table name) 
set (column name) = %Sender_item.VALUE%
Optional
where (identifier) = (example_identifier)

Updates the value of the column in all rows with the value of the sender item

This query will update the value of all the affected rows

Note: If there is no WHERE-clause, the query will update all values

exec STORED_PROCEDURE_NAME
Optional
@variable1 = %Sender_item.VALUE%

This will execute a stored procedure. Some stored procedures have variables attached to them, which will have to be filled out

questions search queries

Contact Us