Datasets DBs Reference

Register and manage database connections.

Dataset DBs (Reference) define database connections for data storage, retrieval, and manipulation within the FrameworX platform. It provides:

SQL database connectivity
Pre-configured system databases
Connection string management
Development/production isolation
Multi-provider support

Each DB represents a connection to a database used for tags, alarms, historian, or custom data operations.

Configuration Properties

Property	Description	Required
Name	Unique database identifier	Yes
Provider	Database technology (SQLite, SqlClient, etc.)	Yes
Database	Database type/instance	Yes
ConnectionString	Connection parameters	Yes
LogonPassword	Authentication password (admin only)	No
ServerIP	Remote gateway for security	No
FilterColumns	Query optimization columns	No
CustomOptions	Additional configuration	No
Description	Documentation text	No

Pre-Defined Databases

Four system databases are included in every solution:

DB Name	Purpose	Default Location
Retentive	Stores retentive tag values	`<SolutionNameAndPath>.dbRetentive`
RuntimeUsers	Dynamic user management	`<SolutionNameAndPath>.dbRuntimeUsers`
AlarmHistorian	Alarm and audit trail records	`<SolutionNameAndPath>.dbAlarmHistorian`
TagHistorian	Time-series data storage	`<SolutionNameAndPath>.dbTagHistorian`

Creating Database Connections

Navigate to Datasets → DBs
Click Plus icon
Configure:
- Name: Unique identifier
- Provider: Select from list
- Database: Choose type
- Description: Documentation
Click OK

ConnectionString Configuration

Structure

Provider=<provider>;Data Source=<source>;Additional Parameters

Examples

SQLite (Local):

Provider=System.Data.SQLite;
Data Source=_SolutionPathAndName_.db

SQL Server Express:

DataSource=.\SQLEXPRESS;
Initial Catalog=myDatabase;
User Id=sa;Password=pwd

PostgreSQL:

Host=localhost;Database=mydb;
Username=user;Password=pass;Port=5432

ConnectionString Macros

Use these macros for portable configurations:

Macro	Description	Example
`_SolutionPath_`	Solution directory	C:\Solutions\MyApp\
`_SolutionName_`	Solution name only	MyApp
`_SolutionPathAndName_`	Full path with name	C:\Solutions\MyApp\MyApp
`_ExecutionPath_`	Working directory	Current runtime folder
`_ExecutionPathAndName_`	Working path with name	Runtime location
`_ProductPath_`	Installation directory	C:\Program Files\FrameworX\
`_Transfers_`	Default transfer folder	Public\Documents\Transfers\
`_ThirdParty_`	Third-party components	MyDocuments\ProductName\ThirdParty\

Supported Providers

Provider	Supported Databases
System.Data.SQLite	SQLite
System.Data.SqlClient	SQL Server, SQL Server Express
Npgsql	PostgreSQL
MySql.Data.MySqlClient	MySQL
Oracle.DataAccess.Client	Oracle
System.Data.Odbc	Any ODBC-compatible
System.Data.OleDb	Access, Excel, CSV

Database-Specific SQL Syntax

Database	Syntax Example	Special Considerations
SQL Server	`SELECT TOP 10 * FROM Table`	Use TOP for limiting
SQLite	`SELECT * FROM Table LIMIT 10`	Use LIMIT clause
MySQL	`SELECT * FROM \`Table` LIMIT 10`	Backticks for names
PostgreSQL	`SELECT * FROM "Table" LIMIT 10`	Case-sensitive names
Oracle	`SELECT * FROM Table WHERE ROWNUM <= 10`	ROWNUM for limiting

Connection Management

Single Thread per DB

Each DB configuration creates ONE connection
Single execution thread per database
Sequential command execution

Parallel Execution

For concurrent operations to same database:

DB1 → Production Database (Thread 1)
DB2 → Production Database (Thread 2)
DB3 → Production Database (Thread 3)

Create multiple DB connections to same database for parallelism.

Connection Pooling

Reuse existing connections
Minimize connection overhead
Configure pool size appropriately

Time Zone Management

Default Behavior:

Platform stores all DateTime values as UTC
Historian and Alarm data always in UTC
Automatic conversion for display

Configuring External Databases:

DateTimeMode Settings:
- UTC: No conversion needed
- LocalTime: Platform converts automatically
- Custom: Handle in SQL statements

Local Time Queries:

sql

-- Adjust for local time zone (example: EST -5 hours)
WHERE Timestamp >= DATEADD(hour, -5, @Tag.StartTimeUTC)

Development vs Production

Execution Profiles

Automatic database switching for development:

Production DB	Development Override
`<name>.dbRetentive`	`<name>.dbRetentiveDev`
`<name>.dbRuntimeUsers`	`<name>.dbRuntimeUsersDev`
`<name>.dbAlarmHistorian`	`<name>.dbAlarmHistorianDev`
`<name>.dbTagHistorian`	`<name>.dbTagHistorianDev`

Configuration

Configure production databases normally
Run in Development profile
Data automatically redirects to Dev databases
No manual switching required

Security Considerations

Password Management

Only administrators can set LogonPassword
Passwords encrypted in configuration
Use Windows Authentication when possible

Remote Access

Configure ServerIP for gateway security:

Requires TWebServices on remote computer
Provides secure tunnel for database access
Isolates database from direct access

Network Security Architecture

Gateway Configuration for Restricted Databases:

Identify Restriction: Database accessible only from specific servers
Install Gateway: Deploy platform with TWebServer on authorized machine
Configure ServerIP: Point Dataset connections to gateway machine
Result: Secure database access through controlled gateway

Benefits:

Maintains network security policies
Centralizes database connections
Enables audit logging
Supports DMZ architectures

Utilities

SQLite Admin

Built-in tool for SQLite management:

Browse tables
Execute queries
Import/export data

Visual Query Builder

Interactive SQL query creation:

Drag-and-drop interface
Join visualization
Query testing

Store & Forward Limitations

The Store & Forward feature has specific requirements:

Applies Only To: Historian and Alarm databases
Required Schema: Must include control columns
Not Available For: Generic application databases
Alternative: Implement custom buffering for generic databases

For Store & Forward configuration, see Historian Archiving Process documentation.

Best Practices Checklist

Use macros - Portable connection strings
Test connections - Verify before deployment
Document databases - Clear descriptions
Secure passwords - Admin-only access
Use Dev profiles - Protect production data
Regular backups - Especially SQLite files
Monitor performance - Check query execution

Troubleshooting

Connection failed:

Verify provider installed
Check connection string syntax
Confirm database exists
Test network connectivity

Access denied:

Check credentials
Verify permissions
Review firewall rules

Performance issues:

Add indexes
Optimize queries
Check network latency
Review connection pooling

Page tree