Page History

Datasets → Tutorial | Concept | How-to Guide

| Reference

Understanding Database Integration

Industrial automation requires seamless data flow between control systems and enterprise databases. The Datasets Module bridges this gap by providing:

• Real-time synchronization between tags and databases
• Abstracted data access independent of database technology
• Secure connectivity without exposing database credentials
• Reliable data exchange with automatic buffering

Key Concepts

• DatasetDB

• :

• Database connection configuration defining how to connect to external databases

• DatasetQuery

• : SQL

• statement or stored procedure with execution properties and

• real-time parameters

• DatasetTable

• :

• Direct table mapping

• linking tags to database columns for automatic synchronization

• DatasetFile

• : File I/O

• configuration for recipes, settings, and data exchange in ASCII, Unicode, or XML formats
• Synchronous vs Asynchronous: Execution modes affecting UI responsiveness and script flow
• Store and Forward: Buffering mechanism ensuring no data loss during connection failures

What It Does

The Datasets Module provides comprehensive data management:

• Connects to multiple SQL databases simultaneously
• Executes queries and stored procedures in real-time
• Maps database tables to tags for automatic synchronization
• Reads and writes data files in various formats
• Provides query results to displays, scripts, and reports
• Manages Store and Forward for database reliability

Configuration Workflow

Architecture Overview

Service-Based Design

The module operates as a service provider:

• Client isolation - No direct database access from displays
• Centralized management - Single point for credentials
• Query abstraction - Change databases without reconfiguring displays
• Connection pooling - Efficient resource usage

Data Flow

1. Client request (display, script, device)
2. Dataset service receives request
3. TServer executes database operation
4. Results returned to Dataset service
5. Data mapped to tags or returned to client

Datasets Module Configuration Workflow

Step

Action

Description

Define Connections

Create DatasetDB

Configure database connection strings and settings

Prepare Queries

Build SQL statements

Use Query Editor or Visual Query Builder

Map Tables

Configure DatasetTables

Link tags to database columns for sync

Configure Files

Setup DatasetFiles

Runtime Behavior

Query Execution

The module acts as a service provider, executing queries on-demand from other modules. Supports both client-side and server-side processing for distributed architectures.

Data Integration Points

• Displays

• : DataGrids show query results in real-time

• Scripts

• : Access query results for custom logic and automation

• Devices

• : Exchange data between field equipment and databases

• Reports

• : Generate reports from database queries
• Historian: Store time-series data in SQL databases

Built-in SQLite Database

Every

solution includes an embedded SQLite database providing:

• Secure project configuration storage
• Historian for small/medium systems (up to 10GB)
• Local Store and Forward buffer
• Storage for runtime settings and recipes

• Alarm and audit trail records
• Retentive tag values

Feature Highlights

• Multiple Database Support

• -

• SQL Server, Oracle, MySQL, PostgreSQL simultaneously

• Visual Query Builder

• - Create complex queries without SQL knowledge

• SQL Editor with IntelliSense

• - Code completion and syntax highlighting

• Stored Procedures

• - Execute

• with input/output parameters

• Real-time

• Parameters - Dynamic

• query execution with tag values
• Secure Data Gateway

• - Firewall-friendly routing

• Store and Forward

• - Automatic buffering

• during outages

• Cross-platform Support

• - ADO.NET, ODBC, OleDB connectivity

• Distributed Architecture

• - Client or server-side

• processing

• File Integration

• -

• CSV, XML, and text

• file support

In this section...