SQL Driver is designed to communicate with External/Additional Database System that has its own schema and data source. It can establish the link with any brand of SQL Database so long it supports ODBC. Using SQL Driver is pretty straightforward, first is to define a Ethernet port that having the IP address point to where the database is running. Secondly is to add an entry for SQL driver and choose its polling rate accordingly. Third step is different as compare to other drivers whereby the Path field shall be entered as Database Connection String. For instance, user may enter the following for MySQL database: "Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=igxlog;User=root;Option=3";Initial Catalog=igxlog". The Address field is not used for device entry.

Configuring Port for SQL driver

Note that user could choose to delete the data upon fetching by enabling this option on the far right.

Similarly for one step down the tag tree node, Address field for tag entry also not used but the Path field requires to be entered with a specific syntax for associating with a Table column within a Database Schema. There are several possible database schema as shown at the following diagram.

Various possible database schema for interaction.

The syntax of the parameter is straightforward as per the following format. It was designed to be as close as per the SQL statement, yet simple enough for beginner to pickup, and most importantly it works across different database:

database_table_name/ function_name: data_column_name [sql_where_condition ]@time_stamp_column_name

The main structure of the syntax that consists of mandatory database_table_name and data_column_name is pretty self explanary, except the optional function_name:, whereby user may perform algorithms like min:, max:, avg: & count:. Note that count: will return numbers of row instead of the value of the specified column.

Another optional item that need to be written in square bracket sql_where_condition can be entered with any formation that compliant to SQL statement, or further trailed with order by sorting method so the top row will be associated to the targetted tag value.

The last optional portion of the syntax is time_stamp_column_name, this option allow user to associate external time stamp into IntegraXor live time stamp. This mean, when the tag is used into Alarm Task or Trending Task , the external associated time stamp will be used instead of the actual SCADA time stamp upon receiving the data.

In any case that when the read-from and write-to database table is different, user may insert a semicolon as separator and then append the same syntax to the end for the write operation. Below is an sample syntax when read & write are of different table with different conditions.

log1/read_fr_column[tag_name='tag' order by time_stamp desc];log2/write_to_column[data is not NULL]

Project Editor is designed for both mouse and keyboard oriented users. User may enter all syntax directly, copy & paste the content from one field to another or click on the browse button [...] on the right of the syntax entry cell. A syntax builder will pop up to help user in entering the relevant parameter.

Entering SQL driver syntax via composer

Note

Be aware that there's no verification of syntax or whatsoever fool-proof checking before the database operation is performed, user must perform the test before deployment to site machine.