Datasets Module enables bi-directional real-time communication between your solution and SQL databases.
Modules → Datasets| Tutorial | How-to Guide | Reference
Datasets Module Overview
It enables
Introduction to the Datasets Module
Datasets Module is designed for data exchange with SQL databases and text files from a diverse set of sources. Essentially, the Datasets Module drivesbi-directional real-time communication between
your solution and
SQL databases, managing data exchange with various database platforms and file formats.
On this Page:
Table of Contents maxLevel 2 minLevel 2 indent
10px exclude Steps style none
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
Key Concepts and Terms
in ASCII, Unicode, or XML formats
Configuration Workflow
Datasets Module Configuration Workflow
Action
Comments
Define database connections
Create the DatasetDB connection mapping out the databases you solution is going to use
Prepare Queries
Paste our queries, or use the built-in SQL Query Editor, or the VisualQueryBuilder, to define your queries.
Use the Queries
Onde defined, the queries can be used on Displays, Reports and Scripts
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
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
Client request (display, script, device)
Dataset service receives request
TServer executes database operation
Results returned to Dataset service
Data mapped to tags or returned to client
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\
| Excerpt |
|---|
Datasets Module LinksExplanation - to understand concepts→ Modules / Business Operations / Datasets Module Tutorials - to learn by doing→ Tutorials / Business Operations / Datasets Module Tutorial How-to Guides - to accomplish specific tasks→ How-to Guides / I Business Operations / Datasets Module How-to Guide Reference - technical details→ Technical Reference / Business Operations / Datasets Module Reference |
In this section...
| Page Tree | ||||
|---|---|---|---|---|
|
Runtime Execution
Providing Query Results
The Datasets Module enables the solution to interact with SQL databases seamlessly,acting as a Service to other modules.
The data retrieved from databases can be utilized in various ways throughout your solution. For example:
- In the Displays Module: Visualization tools like DataGrids can present query results on screens and dashboards, creating custom views of the data that are accessible and easy to understand for your users.
- In the Scripting Module: Custom scripts can reference query results and trigger specific actions, such as sending notifications, updating tags, or performing calculations, thereby implementing complex logic based on database data.
- Devices: Sending data from field equipments to a SQL database, or applying settings from the database to the field equipments.
Visualization and Interaction
During runtime, the DataGrid component connects with the server to disapxlsy queries and DataTable tags.
Features Highlights
- SQL Query Support: Effortlessly extract, manipulate, and transform data from diverse sources using efficient SQL queries.
- Integration with External Data Sources: Seamlessly collect and analyze data from various external sources like SQL databases and CSV files.
- Visual Query Editor: Easily create and edit advanced SQL queries without needing extensive SQL knowledge, simplifying the process.
- Distributed Architecture: Supports simultaneous requests and provides flexibility in processing data on either the client or server side.
- Stored Procedures Execution: Allows for advanced data manipulation and analysis directly within the platform.
- Real-Time Execution: Execute SQL queries with real-time data, enabling parameter customization and timely analysis of generated data.
Built-in Embedded SQL
Every system comes with a fully functional embedded SQL Lite database at no additional cost, requiring no setup. This offers several benefits:
- Secure Project Configuration: It serves as a secure repository for your entire project configuration.
- Historian Database: Suitable for small to medium systems, it can function as a historian database for logging tags, alarms, and events (up to 10GB of data).
- Store and Forward: For large systems, it serves as a local Store and Forward location, - stepping in when the remote database is unavailable.
- Storage for Local Data: It's an ideal system for storing local runtime settings, retentive information, local recipes, schedules, tables, and queries needed for report preparation.
Multiple Database Connections
Our projects support multiple databases, seamlessly integrating with SQL and other database technologies such as ADO.NET, ODBC, OleDB, and more.
SQL Statement Editor
Our editor enhances code editing by offering Intellisense and helpful code suggestions, making the process faster and more efficient.
Secure Data Gateway
Our platform offers a built-in data gateway that's firewall-friendly, ensuring secure data query routing from clients without requiring direct access to the database.