bg

Export data to the SQL database using a stored procedure

Problem scenario:

I would like to export data to my SQL database through a stored procedure because I want to make additional check before inserting data.

What is a stored procedure:

A stored procedure (also stored proc) is a subroutine available to applications that access a SQL database. Such procedures are executed and process data on the server side.

When do you need a stored procedure:

  • Process data on your server.
  • Insert the same data into multiple tables.
  • Add primary or foreign key values to inserted data.
  • Conditionally update or insert new data.

Requirements:

It is assumed that:

You've prepared parser items for export.

For this tutorial, all items were prepared in the previous part.

Also, you may read other examples of data parsing (different parser types)

Solution:

Note: You should have SQL programming skills related to your database because a stored procedure is a subroutine.

It is assumed that you have access to the database and can create database object in it. First of all, create a table in a database for incoming data. Select an existing database or create a new one. The simplest way to do it is using database manufacturer utilities:

  • SQL Server Management Studio for MSSQL.
  • MySQL Workbench for MySQL.
  • pgAdmin for PostgreSQL.

Please refer to manuals for the corresponding utility for more information and instructions.

We've prepared a table example below (fig.1). It is a prototype. You can change it for your needs or use your existing table.

CREATE TABLE dbo.[DATA] (
   [ID] decimal(10,0)  IDENTITY(1,1) NOT NULL,
   [DATE_TIME_STAMP] datetime NULL,
   [DATA_SOURCE_NAME] nvarchar(32) NULL,
   [DEVICE_ID] nvarchar(32) NULL,
   [V1] real NULL,
   [V2] real NULL,
   [V3] real NULL,
CONSTRAINT [PK_DATA] PRIMARY KEY ([ID] ASC)
WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE `DATA` (
   `ID` int(11) NOT NULL auto_increment,
   `DATE_TIME_STAMP` DATETIME NULL,
   `DATA_SOURCE_NAME` VARCHAR(32) NULL,
   `DEVICE_ID` VARCHAR(32) NULL,
   `V1` DOUBLE NULL,
   `V2` DOUBLE NULL,
   `V3` DOUBLE NULL,
PRIMARY KEY  (`ID`)
) ENGINE=InnoDB;


CREATE TABLE "DATA" (
   "ID" SERIAL,
   "DATE_TIME_STAMP" timestamp DEFAULT NULL,
   "DATA_SOURCE_NAME" varchar(32) DEFAULT NULL,
   "DEVICE_ID" varchar(32) DEFAULT NULL,
   "V1" real DEFAULT NULL,
   "V2" real DEFAULT NULL,
   "V3" real DEFAULT NULL,
PRIMARY KEY (ID)
);

Prepare a stored procedure in your database and grant "Execute" rights to an user. The following SQL statements create a stored procedure that checks an incoming value and inserts data into the table.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF EXISTS(SELECT 1 FROM   INFORMATION_SCHEMA.ROUTINES WHERE  ROUTINE_NAME = 'data_insert')
 DROP PROCEDURE data_insert
GO

CREATE PROCEDURE [dbo].[data_insert]
  @DATE_TIME_STAMP datetime,
  @DATA_SOURCE_NAME nvarchar(32),
  @DEVICE_ID nvarchar(32),
  @V1 float,
  @V2 float,
  @V3 float
WITH EXECUTE AS OWNER
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

 IF (@V1 > 100) OR (@V2 > 100) OR (@V3 > 100) BEGIN
   RETURN;
 END;

 UPDATE DATA SET
    DATE_TIME_STAMP = @DATE_TIME_STAMP,
    V1 = @V1,
    V2 = @V2,
    V3 = @V3
 WHERE
   DATA_SOURCE_NAME = @DATA_SOURCE_NAME AND DEVICE_ID = @DEVICE_ID;

END;
GO

DROP PROCEDURE IF EXISTS `data_insert`;


CREATE PROCEDURE `data_insert`(
        IN `DATE_TIME_STAMP` DATETIME,
        IN `DATA_SOURCE_NAME` VARCHAR(32),
        IN `DEVICE_ID` VARCHAR(32),
        IN `V1` DOUBLE,
        IN `V2` DOUBLE,
        IN `V3` DOUBLE)
BEGIN
 SET @DATE_TIME_STAMP = DATE_TIME_STAMP;
 SET @DATA_SOURCE_NAME = DATA_SOURCE_NAME;
 SET @DEVICE_ID = DEVICE_ID;
 SET @V1 = V1;
 SET @V2 = V2;
 SET @V3 = V3;

 IF (@V1 <= 100) AND (@V2 <= 100) AND (@V3 <= 100) THEN
  
    UPDATE `DATA` SET
     `DATE_TIME_STAMP` = IFNULL(@DATE_TIME_STAMP, NOW()),
     `V1` = @V1,
     `V2` = @V2,
     `V3` = @V3
    WHERE
     `DATA_SOURCE_NAME` = @DATA_SOURCE_NAME AND `DEVICE_ID` = @DEVICE_ID AND;

 END IF;

END;
-- split line


CREATE OR REPLACE FUNCTION data_insert(
  IN DATE_TIME_STAMP_1 timestamp,
  IN DATA_SOURCE_NAME_1 varchar(32),
  IN DEVICE_ID_1 varchar(32),
  IN V1_1 real,
  IN V2_1 real,
  IN V3_1 real)
RETURNS void SECURITY DEFINER AS $$

BEGIN
 IF (V1_1 > 100) OR (V2_1 > 100) OR (V3_1 > 100) THEN
  RETURN;
 END IF;

 UPDATE DATA SET
   DATE_TIME_STAMP = DATE_TIME_STAMP_1,
   V1 = V1_1,
   V2 = V2_1,
   V3 = V3_1
 WHERE
   DATA_SOURCE_NAME = DATA_SOURCE_NAME_1 AND DEVICE_ID = DEVICE_ID_1;

END;
$$ LANGUAGE plpgsql;
-- split line

Select and configure the "SQL Database Professional" plugin to call a stored procedure on your server (fig. 1).

SQL Database Professional Plugin
Fig. 1. Selecting the SQL Database plugin.

Go to the "SQL Database Professional" plugin configuration window and click the "Connection mode" button on the left of the window (fig. 2)

Enabling connection
Fig. 2. SQL Database plugin. Enabling connection.

  1. Uncheck the "Temporarily disable" option #1.
  2. Option #2 specifies the plugin to maintain an active connection with a database. It increases a performance if your data flow is fast.
  3. Option #3 will enable you to reconnect to a database when a connection is lost.

On the second "Connection parameters" page (fig. 3), you can select a database type and configure connection parameters for your database. Please look at the manual for the SQL plugin for detailed explanations. You should successfully test the connection by clicking the "Test connection" button (pos. 2) before going to the next step.

Connection parameters
Fig. 3. Connection parameters.

On the third "Handling errors" page (fig. 4), specify how the software should react to errors that occur while the plugin writes data to a database.

Handling errors
Fig. 5. Handling errors.

The option at position #2 allows you to write data to a temporary file while your database is offline and restore it back after successful connection.

7. The last "SQL queue" page (fig. 6) is significant. You can add a SQL statement and bind parser's variables to SQL query parameters.

SQL queue
Fig. 7. SQL queue.

You can add a SQL query by selecting the "Action →Add SQL to queue" menu item (fig. 7, pos. #2), and then open the SQL editor (fig. 8) by clicking a button with dots, which appears by clicking on the "SQL text" field (fig. 7).

SQL editor
Fig. 8. SQL editor.

Specify a SQL query to call a stored procedure (for the corresponding SQL server type). Parameters like ":TIMESTAMP" or ":V1" allow you to pass a parsed variable to the SQL query. Every parameter appears in the SQL queue (fig. 7) where you should specify the data type (Database column's data type) for it and bind a parsed variable (Parser item name). The data type should match the data type of the corresponding parameter of your stored procedure. Both data types should match a data type of the parsed value.

exec data_insert :TIMESTAMP, :DS_NAME, :DEVICE_ID, :V1, :V2, :V3

call data_insert(:timestamp, :ds_name, :device_id, :v1, :v2, :v3);

select data_insert(:timestamp, :ds_name, :device_id, :v1, :v2, :v3);

Click the "OK" button to close all dialog windows and save the settings.

Suppose you configured the SQL Database Professional module correctly. In that case, the data logger displays a message after each database operation in the drop-down box in the main window.

FAQ

How many parameters can I use to call a stored procedure?

The number depends on the database type and SQL server version. The optimal maximum is 64 parameters.

The data type of a parameter in my stored procedure does not match exactly the data type of a parsed value. Is it the problem?

The plugin can convert some data types automatically. For example, integer number with different size, integer numbers to floats, any numbers to string.

Related articles: Export data to the SQL database using a stored procedure

Advanced OPC Data Logger - Read more about:

OPC Logger RS232 pinout and signals Cables and signals Data monitor cables