MySQL Query Examples

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

+ More

Items in an MySQL 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

Depending on the server, backticks ( ` ) may not be needed

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	The result will be an array of JSON objects, where each object represents a row in your table. Selecting all, especially in a large table, can be resource-intensive. Use this carefully as some tables can contain thousands or millions of entries.
SELECT * FROM `table_name` LIMIT 1	`[{"Test1":"1","Test2":"2","id":"12"}]`	This will give you a single entry from the table. The `LIMIT 1` can be changed to any other number, and it will reflect the amount of rows returned. It will return the first entry/entries in the table.
SELECT * FROM `table_name` ORDER BY `id` DESC LIMIT 1	`[{"Test1":"15","Test2":"25","id":"33"}]`	This will return the last entry/entries in the table. The `LIMIT 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.

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%' 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
`CALL 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%' 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

CALL 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

query samples mysql queries

Contact Us