SQL - Instance & item setup
How to setup a SQL instance and item in SIA
The SQL Connector SIA is compatible with the MS SQL and MySQL databases.
There is support for Oracle database but with another Connector.
Parameters
When using the SQL Connector the instance has specific parameters. These parameters are required as they are needed to establish connection. The parameters for the item are the name and the SQL query to be passed to the database
Instance
The instance needs the following parameters to establish a connection to the database:
| Parameter | Description |
|---|---|
| Name (instance default) | User given name for the instance |
| Address (instance default) | The IP address of the SQL database |
| Username | Database username |
| Password | Database password |
| Database | Database name |
| Connection String (MS SQL only) | Additional connection parameters |
Item
Each item reflects a SQL query for SELECT rows from a table in a database.
An item are automatically created called "Query write" which are to be called for writing queries to the database table.
| Parameter | Description |
|---|---|
| Query | SQL query to the database |
Returned values
Example where "id" and "value" are the two columns in the table "siademo":
Name |
Response Value |
SQL Query |
|---|---|---|
Latest row table values |
{"id":"5","value":"1337"}] |
SELECT TOP 1 * FROM siademo ORDER BY ID DESC |
Latest value |
1337 |
SELECT TOP 1 value FROM siademo ORDER BY ID DESC |
Oldest row Table values |
[{"id":"1","value":"38"}] |
SELECT TOP 1 * FROM siademo |
Oldest 5 rows from Table VALUES |
[{"id":"1","value":"38"},{"id":"2","value":"55"},{"id":"3","value":"777"},{"id":"4","value":"7575"},{"id":"5","value":"1337"}] |
SELECT TOP 10 * FROM siademo |
The return of the query selecting one or more rows is a JSON formatted string where each object in the array corresponds to a row from the database table.
The return of the query selecting one value is a string containing the value of column at the specified row.