Connecting an OPC Client/Server to an SQL Database Using Datatag Link

This tutorial will guide you through the process of connecting an OPC client or OPC server to an SQL database (for example, MySQL, MariaDB, PostgreSQL, MS SQL Server) using our Datatag Link software. Here, we explain how to configure a connection and set the necessary parameters.

Why connect OPC to an SQL database?

The following use cases are typical:

• Enterprise Integration: Feed real-time OPC data into your ERP, BI, or analytics platforms. It's like giving your business a 360-degree view of operations.
• Centralized Monitoring: Aggregate data from multiple OPC servers into a single database.
• Historical Data Storage: OPC tag data archive and logging for historical trend and problem analysis.
• Automated Reporting: Set up SQL-based reports that trigger automatically when OPC values hit certain thresholds. It's like having a personal assistant for your data.

Step-by-step Setup Using Datatag Link

How it works

Datatag Link bridges OPC and SQL systems for seamless data exchange:

1. OPC side: The software connects to an OPC server (DA or UA), subscribes for configured tags, and starts receiving OPC values in real-time.
2. SQL side: The program connects to your database and starts polling data from the specified SQL table/column.
3. Bidirectional support: If data on any side is changed or updated, the program transmits it to the opossite side.
4. Real-time logging: Our software logs all transactions in real-time to a text protocol file for auditing and troubleshooting.

Step 1: Install and launch Datatag Link

1. Download and install: If you haven't already, download Datatag Link from our website. When you install the program, choose "OPC Client" or "OPC Server" on the left side, and "SQL" on the right side.
2. Launch the program: Open Datatag Link to access the main window with the Tag List, Toolbar, and Status Panel.

Step 2: Configure data storage

If you haven't done it before, please create a table in your database using your preferred tool, such as pgAdmin, SQL Management Studio, or MySQL Workbench. The table should contain a minimum of two columns:

• ID: this column needs a unique ID so the program can find the necessary row and get/set a value in the second column. We recommend using a numerical value in this column (e.g., an auto-increment number as a primary key).
• Value: the program will read the value in the column and send it to the other side of your link. The program will try to convert the column to the reception side if it has the 'VARCHAR' data type.

Step 3: Configure the OPC client/server connection

1. Add a new OPC connection:

• Click the Add button on the toolbar.
• Click the Add button again in the new window to set up the connection settings for your OPC server.
• Server Name: Enter the display name of your OPC server's.
• IP: To find OPC remote servers, type the host name or IP address of the computer (like "localhost" for a local server) and click "Find".
• Click OK to save the OPC settings. The connection will appear in the Connections list. You can re-use it later for other OPC tags.

2. Specify OPC tag parameters:

• Tag name: Specify the OPC tag path (e.g., 'node1.node2.tag1'). In the OPC client mode, you can click the "Select tag" button and select the necessary items in the server's address space.
• Data type: Choose the data type (e.g., Integer, Float, or Auto to match the paired SQL column).
• Process valid values only: Enable this to transmit only valid OPC values.
• Force read values: Enable periodic polling (e.g., every 5000 ms) if the OPC server doesn't send updates automatically.

Step 4: Configure the SQL database connection

1. Add a new SQL connection:

• Click the Add button again on the SQL side of your link.
• Set SQL database parameters:
• Name: Assign a descriptive name (e.g., "Production Database").
• Type: Select your SQL database type (e.g., MySQL, PostgreSQL).
• Server: Enter the database server's IP/hostname (e.g., '192.168.1.100').
• Database: Specify the database name (e.g., 'sensor_data').
• Login/Password: Provide credentials for database access.
• Test Connection: Click this button to verify connection with your database.

Step 5: Link the OPC tag to the database table and column

• Table and Column Mode:
• Table: Enter the target table name (e.g., 'opc_data').
• Column: Specify the column where OPC data will be stored (e.g., 'temperature').
• Row ID Field: Define the unique value in your primary key column (e.g., 'id').
• Custom SQL Mode:
• Write custom INSERT/UPDATE statements. There, you can use placeholders like ':my_value' that the program with replace with a real value.
• Transmission Direction: Choose One-Way (OPC → SQL) or Bidirectional (if writing back to OPC).

Step 6: Save and test the configuration

1. Save the configuration:

• Click OK to save the OPC-SQL link settings. The configured link will appear in the Tag List.
• Go to File → Save or click the Save button on the toolbar to save the current configuration and apply the changes.

2. Test the connection:

• Check the Status Panel for '<' (OPC to SQL) or '>' (SQL to OPC) symbols.
• Look at the Transactions File window (Main menu → View → Transactions log) to confirm data is being written to the SQL database.
• Check 3rd party tools and database management utilities to double-check transferred values.

For more detailed instructions, refer to the full Datatag Link Manual.

FAQ

Q1: Can I link multiple OPC tags to multiple SQL tables?

A: Yes! You can add up to 65K links in Datatag Link. Configure each OPC tag to map to a different SQL table or column. But keep in mind, that each database our OPC connection require some system resources.

Q2: How does Datatag Link handle situations when OPC data type mismatches with SQL?

A: The software tries to automatically convert values with different data types (e.g., OPC Float to SQL DECIMAL). You can also manually set data types for precision. It is better to use similar data types on both side to avoid potential data truncation (e.g., OPC Float and SQL DOUBLE).

Q3: How do I troubleshoot failed database writes?

A: Follow these steps:

• Check the Message Log (in the File menu) for SQL syntax or permission errors.
• Verify the SQL table name, column, the row ID (if it is used), and user permissions.
• Test the SQL connection using 3rd party tools like MySQL Workbench or pgAdmin.

Connecting Two OPC Servers: From OPC DA Server to OPC UA Server.
OPC Tag Grouping From Different OPC DA Servers To OPC UA.
Connecting an OPC Client/Server to an SQL Database Using Datatag Link.
Connecting an OPC Client to an MQTT Broker In Real-Time.
Linking Tags In Two MODBUS Slave Devices In Real-Time.
How to Connect a MODBUS Slave Device to an SQL Database.
Log OPC DA, UA, HDA, AE data to SQL or Excel.
Data Logger Suite - log data from various data sources (OPC, MQTT, MODBUS, SNMP, etc.).