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.

[an error occurred while processing this directive]

[an error occurred while processing this directive]

[an error occurred while processing this directive]

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.

[an error occurred while processing this directive]

[an error occurred while processing this directive]

[an error occurred while processing this directive]

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.

[an error occurred while processing this directive]

[an error occurred while processing this directive]

[an error occurred while processing this directive]

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