Database Import Driver

Overview

Database Import Driver is designed to read current data from a third-party database, as well as to write to a third-party database using commands. Supported DBMS are Microsoft SQL Server, Oracle, PostgreSQL and MySQL. The driver is included in the Rapid SCADA distribution and does not require separate installation.

Configuring

Create a new communication line and a device of the DB Import type using the wizards called up by the and buttons. The communication line is responsible for connecting to one database. The communication channel type of the line is Undefined. The following figure shows a communication line node in the project explorer.

Communication line node

Open the properties of the created device. The device settings specify receiving tag values ​​and sending commands via SQL queries.

Connection options

It is assumed that the database that serves as the source of information for the driver already exists and contains the necessary tables. Contact the database administrator for information about database connection options.

The figures below show the query parameters for getting device tag values.

Query parameters
Query parameters

In the Tags field, enter the device tag codes. Each line contains the code of one tag. The SQL field contains the query text. If the Single row result check box is checked (Query 1), the query should return a single row, each field of which contains a tag value. If the checkbox is unchecked (Query 2), the query should return a set of rows, each containing the tag code and value. In the second case, the query result must contain the code and val columns.

Command parameters

Due to command support, the driver can write information to the database by a command sent by an operator or automatically. The Command code must match the tag code of the channel through which the command is sent. In the SQL query text, the value and data of the command are available through the @cmdVal and @cmdData parameters.

Once the device properties are configured, create channels in the configuration database using the wizard called up by the button.

If the configuration is correct, the device data page will display the obtained values.

Device data

SQL Scripts

Example scripts for PostgreSQL:

CREATE SCHEMA IF NOT EXISTS drv_db_import
    AUTHORIZATION postgres;

CREATE TABLE IF NOT EXISTS drv_db_import.table1
(
    id integer NOT NULL,
    val1 double precision,
    val2 character varying,
    val3 timestamp with time zone,
    CONSTRAINT table1_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS drv_db_import.table2
(
    id integer NOT NULL,
    code character varying,
    val double precision,
    CONSTRAINT table2_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS drv_db_import.table_out
(
    create_time time with time zone DEFAULT now(),
    cmd_val double precision,
    cmd_data bytea
);

-- Query 1
SELECT val1, val2, val3 FROM drv_db_import.table1 LIMIT 1

-- Query 2
SELECT code, val FROM drv_db_import.table2

-- Command
INSERT INTO drv_db_import.table_out (cmd_val, cmd_data) VALUES (@cmdVal, @cmdData)