Datasets Module (Reference) provides comprehensive database connectivity, query execution, and data management capabilities for the FrameworX platform. The Datasets Module enables:
The Datasets Module enables:
- Multi-database connectivity
- SQL query execution
- Table-level CRUD operations
- File-based data exchange
- Store procedures support
- Real-time parameter embedding
The module serves as the data storage backbone for Tags, Alarms, Historian, and custom application data.
In this page:
Module Components
Configuration Sections
| Section | Path | Purpose |
|---|---|---|
| DBs | Datasets → DBs | Database connections |
| Queries | Datasets → Queries | SQL statements and procedures |
| Tables | Datasets → Tables | Direct table operations |
| Files | Datasets → Files | Recipe and text file management |
Configuration Workflow
| Step | Action | Location | Purpose |
|---|---|---|---|
| 1 | Define connections | Datasets → DBs | Establish database links |
| 2 | Prepare queries | Datasets → Queries | Create SQL statements |
| 3 | Map tables | Datasets → Tables | Direct table access |
| 4 | Configure files | Datasets → Files | Recipe management |
Pre-Defined Databases
Four system databases included in every solution:
| Database | Purpose | Location |
|---|---|---|
| Retentive | Retentive tag values | <SolutionNameAndPath>.dbRetentive |
| RuntimeUsers | Dynamic user management | <SolutionNameAndPath>.dbRuntimeUsers |
| AlarmHistorian | Alarm and audit records | <SolutionNameAndPath>.dbAlarmHistorian |
| TagHistorian | Time-series data | <SolutionNameAndPath>.dbTagHistorian |
<ac:structured-macro ac:name="info"> ac:rich-text-body SQLite databases are created automatically if they don't exist. Other database types must exist before connection. </ac:rich-text-body> </ac:structured-macro>
Runtime Architecture
Service Architecture
- TRunModule.exe (Dataset) - Module process
- TServer - Database access services
- Client isolation - No direct database access
- Data virtualization - Abstract data sources
Request Processing
- Client request (display/script)
- Server routing
- Database execution
- Result propagation
- Tag mapping
Data Access Methods
Queries
SQL statements with dynamic parameters:
sql
SELECT * FROM Production
WHERE BatchID = {{Tag.Batch_ID}}Tables
Direct CRUD operations without SQL:
- Automatic schema management
- WhereCondition filtering
- Tag mapping
Files
Text-based data exchange:
- Recipe management
- Configuration import/export
- XML/ASCII/Unicode formats
Display Integration
DataGrid Control
xml
<DataGrid DataSource="{Dataset.Query.ProductionQuery}" />Script Access
csharp
DataTable result = @Dataset.Query.MyQuery.SelectCommand();
@Tag.DataTableTag = result;TK Methods
csharp
TK.CopyDataTableToTag(result, @Tag.Template);Monitoring and Diagnostics
Runtime Monitoring
Access Datasets → Monitor during runtime:
- Operation status
- Row counts
- Error tracking
- Performance metrics
Namespace Properties
csharp
int status = @Dataset.Query.MyQuery.LastStatus;
string error = @Dataset.Query.MyQuery.LastStatusMessage;
int rows = @Dataset.Table.MyTable.RowCount;Security and Administration
SQL Injection Prevention
- Use parameterized queries
- Avoid string concatenation
- Validate inputs
- Use stored procedures
Network Gateway
Configure ServerIP for secure access:
- Requires TWebServices
- Routes through gateway
- Isolates database
Time Zone Handling
- UTC storage default
- DateTimeMode configuration
- Local/UTC conversion
Troubleshooting
Common Issues
Connection Loss
- Check timeout settings
- Verify power settings
- Monitor network stability
- Review idle disconnect
Query Errors
- Validate SQL syntax
- Check table/column names
- Verify data types
- Test in database tool
Performance Issues
- Optimize queries
- Add indexes
- Reduce data volume
- Check network latency
Module Down
- Verify module running
- Check TServer status
- Review error logs
Database-Specific Syntax
Row Limiting Examples:
SQL Server:
sql
SELECT TOP 10 * FROM tableSQLite:
sql
SELECT * FROM table LIMIT 10Oracle:
sql
SELECT * FROM table FETCH FIRST 10 ROWS ONLYOracle Limitations
NCHAR columns limited to 1999 bytes (auto-adjusted)
Best Practices Checklist
- Error Handling - Check LastStatus after operations
- Connection Testing - Use Test button before deployment
- Query Optimization - Design efficient SQL
- Schema Respect - Match DataTable to database
- Case Sensitivity - Use UPPER/LOWER for consistency
- Backup Strategy - Regular SQLite backups
- Performance Design - Consider all factors
Advanced Topics
→ [Dataset Engine] - Internal operations → [Dataset Security] - SQL injection prevention → [Data Management] - Concurrency handling → [Dataset Namespace] - Runtime properties