Page History
Datasets Module Overview
The Datasets module enables bi-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 | ||||||
|---|---|---|---|---|---|---|
|
Key Concepts
- *DatasetDB*: Connection configuration for external databases
- *DatasetQuery*: SQL query configuration with execution properties and methods
- *DatasetTable*: Table mapping configuration linking tags to database columns
- *DatasetFile*: File I/O parameters for ASCII, Unicode, or XML formats
What It Does
- 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
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 |
Use the Queries
Onde defined, the queries can be used on Displays, Reports and Scripts
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.
or Visual Query Builder | ||
Map Tables | Configure DatasetTables | Link tags to database columns for sync |
Configure Files | Setup DatasetFiles | Define file formats and locatio |
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
- *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
Built-in SQLite Database
Every system 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
Features Highlights
- *Multiple Database Support* - Connect to 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 database procedures with parameters
- *Real-time Query Execution* - Dynamic parameters and live data updates
- *Secure Data Gateway* - Firewall-friendly routing without direct database access
- *Store and Forward* - Automatic buffering when databases are unavailable
- *Cross-platform Support* - ADO.NET, ODBC, OleDB connectivity
- *Distributed Architecture* - Client or server-side query processing
- *File Integration* - Read/write CSV, XML, and text files
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.