This data source allows reading and writing data in a supported SQL database. The program will periodically check a database for new data, and if a value has changed, it will transmit it to the linked data source.
Fig. 14. SQL.
Database - the program allows setting up connections to several different databases at the same time. Use the list of previously configured connections to select a connection, or click the Add button to configure a new connection (Fig. 16). The other buttons in the window are used to change connection settings and delete connections.
Data Type - defines the data type of the value to be read from the database. It must match the data type of the column where the data is stored.
Mode – the function is used to select a method of receiving and recording data.
Interval - the function sets an interval for checking availability of new data in the database.
Table and Column Mode
It is required to specify the name of the table and column, and the program will automatically generate SQL queries for reading, writing and updating data.
The row ID field is used to indicate the column name with a unique value for each row (Primary key). The program uses that name to receive a value from the corresponding row in the database. If there is no such row available when data are written into the database, a new row will be added.
If Exact Match is specified in the Type field, the program will find that single value by the identifier specified in the Value field. In this case, a table is a matrix of values in which a new value always overwrites the old one.
When the Addition value is selected in the Type field, it is assumed that the unique identifier will increase when a new value is added. While reading data, the program selects from the database all new values whose identifier is greater than the last received identifier value. When writing a value to the database, the program will always add a new line. In this case, the table will contain all modifications of a value. And it is up to the user to remove old or unnecessary values from the table.
Custom SQL Mode
In this mode, it is the user who creates an SQL query to read and write data (Fig. 15). It is convenient to use this function to receive and write data through a stored procedure on an SQL server using a complex data processing algorithm.
In this mode, when writing data, the place of a value in the SQL query is marked with a special parameter ":my_value". When inserting values, the data type of value is regarded.
When reading data from the database, the program takes the value from the first column that the SQL query returns. An SQL query can return multiple rows.
Fig. 15. SQL queries.
Database Connection Parameters
Fig. 16. Database Connection Parameters.
Name is a text description of a database that is displayed in the list, as in Fig. 14.
Type – the type of database.
Server is the IP address or name of the computer on which a database is running.
Database - the name of a database.
Login and Password - this function enables user authorization.
Test Connection - the function checks if it is possible to connect to the database.