How to connect Alleantia ISC Software to SQL?

Learn how you can use SQL service through Alleantia ISC Software.

It is possible to configure a database (MySQL, Microsoft SQL Server, PostgreSql, Oracle Sb) as a destination of all data, events and alarms, taken from the IOT SCADA Software.

In the Configuration / Cloud Services area, select 'SQL'. Then, select the type of database to connect to IoT SCADA application, from the dropdown menu in the configuration window (Database type)

 

Note: Alleantia is compatible with UTF8 encoding. For custom encoding contact the support

Then, on your Database, create a compatible structure with IoT SCADA application. To do this, click “Download script” to download the script of the creation of a new database on your SQL platform, that contains the data from the Alleantia application.

This is the example of a script compatible with SQL Server.

The Script creates different tables:

  • LOG_DATA (used for sending data read by the machines connected to Alleantia ISC Software)
  • VAR_EVENTS & CONFIG_VAR_EVENTS (contain the events customized within ISC)
  • ALARMS (contains the alarms customized within ISC )
  • CONFIG_DEVICES (contains the devices configured within ISC)
  • CONFIG_VERSIONS (contains informations about the ISC Software version).

CREATE TABLE LOG_DATA_suffix (SN VARCHAR(255), LOG_TIMESTAMP DATETIME2 NOT NULL, LOG_TIMESTAMP_UTC DATETIME2 NOT NULL, DEVICE_ID INT NOT NULL, VARIABLE_ID INT NOT NULL,  NUMERIC_VALUE FLOAT, BOOLEAN_VALUE BIT, STRING_VALUE VARCHAR(255), QUALITY BIT NOT NULL DEFAULT 0, ALARM_PK BIGINT, EVENT_PK BIGINT, PRIMARY KEY(SN, LOG_TIMESTAMP_UTC, DEVICE_ID, VARIABLE_ID));

                                                                   

                                                                 LOG_DATA

logdata

 

CREATE TABLE  VAR_EVENTS_suffix (SN VARCHAR(255), PK BIGINT, TIMESTAMP_UTC DATETIME NOT NULL, DELAYED_TIMESTAMP_UTC DATETIME NOT NULL, ID SMALLINT NOT NULL, STATE BIT NOT NULL, NUMERIC_VALUE FLOAT, BOOLEAN_VALUE BIT, STRING_VALUE VARCHAR(255), QUALITY BIT NOT NULL DEFAULT 0, PRIMARY KEY(SN, PK));

 

                                                                   VAR_EVENTS

 

CREATE TABLE CONFIG_VAR_EVENTS_suffix (SN VARCHAR(255), CONFIG_VERSION INT NOT NULL, ID INT NOT NULL, DESCRIPTION VARCHAR(255), EVT_TYPE VARCHAR(255), EVT_CONDITION VARCHAR(255), SNAPSHOT_IDS VARCHAR(4000), PRIMARY KEY(SN, CONFIG_VERSION, ID));

                                                     TABLE CONFIG_VAR_EVENTS

 

CREATE TABLE ALARMS_suffix (SN VARCHAR(255), ALARM_PK BIGINT, TIMESTAMP DATETIME NOT NULL, EVENT_ID BIGINT, STATE BIT, MSG VARCHAR(511), DETAIL VARCHAR(8000), PRIMARY KEY(SN, ALARM_PK)); 

                                                            TABLE ALARMS

 

CREATE TABLE CONFIG_DEVICES_suffix (SN VARCHAR(255), CONFIG_VERSION INT NOT NULL, DEVICE_ID INT NOT NULL, DEVICE_SUPPLIER VARCHAR(255), DEVICE_MODEL VARCHAR(255), DEVICE_VERSION VARCHAR(255), PRIMARY KEY(SN, CONFIG_VERSION, DEVICE_ID));

                                                          TABLE CONFIG_DEVICES

 

CREATE TABLE CONFIG_VERSIONS_suffix (SN VARCHAR(255), CONFIG_VERSION INT NOT NULL, START_DATE_UTC DATETIME NOT NULL, PRIMARY KEY(SN, CONFIG_VERSION));

                                                              TABLE CONFIG_VERSIONS

 

After having entered the connection parameters to the database (IP address, Port, Database name, Tables suffix, Username, Password), check the connection by clicking “Connection test”. the message “Connection Success” will be shown.

Click “Copy”, the configurations of variables and devices, configured in IoT SCADA application will be forwarded to SQL database. Please NOTE - If you add or remove devices in Alleantia ISC Software, the structure of the tables remains the same so you won't need to re-create the script. Just click on Copy again to syncronize the changes with the DB.

The time interval for data forwarding can be set independently of the logging time of the variables. Set it in h, m, and s fields.

Select the desired variables to send by clicking on the "Telemetry variables" button:

You also do this with Custom alarms and events

 

Check “Send only changed variables in the time interval” box to forward only the variables, which did not change their value during the fixed forwarding time, to SQL platform.

In addition, a line will appear, indicate there the number of periods (1 period=forwarding time), after this number of periods the variables will be forwarded to the database, even if their value remained the same as the last entry.

After that, the service is completely configured, so check the “Enable SQL service” box to enable the service.

Click Save to apply the changes.

IoT SCADA software also sends registered data to more SQL platforms at the same time. The maximum number of SQL platforms to forward data depends on the purchased license.