Datasets and SQL Queries

Purpose and Key Concepts

Dataset DBs

In order for the Dataset Module to communicate with an external database, a connection must be created with certain parameters. These connections, which are created within the Datasets → DBs section of the module, are referred to as Dataset DBs.

Dataset Queries

In the context of the Dataset Module, a Dataset Query refers not only to an SQL query string, but also to a Project object that has a logical name, an SQL query related to that logical name, and other parameters defined within the Datasets → Queries section. There are many ways to automatically map the results of a query execution with Tags.

Dataset Tables

A Dataset Table is a logical name that is created within a project to set up access to a specific table in a connected database. These tables are listed within the Datasets → Tables section of the module. The Tags in the real-time database can easily be mapped to columns in the tables to perform insert, update, or read operations.

Dataset Files

A Dataset File is a logical name that defines parameters for reading and writing files in ASCII, Unicode, or XML formats.

Understanding the Datasets Module

Overview

The Datasets and SQL Queries module provides a powerful and flexible way to interact with databases, exchange data, and integrate this data into your projects. This module allows you to connect to various databases, execute SQL queries, and retrieve or manipulate data as required by your application.

Data exchange with the database is performed through a combination of queries and/or stored procedures. These can be executed on-demand, on a schedule, or triggered by events within your project. The module supports various database providers, including SQL Server, Oracle, and MySQL, enabling seamless integration with different database systems.

The data retrieved from the database can be utilized in various ways throughout your project. In the Displays module, you can use visualization tools like DataGrids to present query results on screens and dashboards. This allows you to create custom views of the data, making it accessible and easy to understand for your users.

In the Scripting module, you can create custom scripts that reference query results and use them to trigger specific actions, such as sending notifications, updating tags, or performing calculations. This enables you to implement complex logic based on the data from your database, making your project more dynamic and responsive to changes in the underlying data.

You can use the data retrieved from databases in the Datasets module for example to configure or update alarm conditions, triggers, and actions. This enables you to create dynamic alarm systems that respond to changes in your data, ensuring that your application stays responsive to real-world conditions.

Features Highlights

1. SQL Query Support: The Dataset Module provides support for SQL queries, allowing users to easily extract, manipulate, and transform data from a variety of sources.

2. Integration with External Data Sources: The Dataset Module can integrate with a wide range of external data sources, including databases, CSV files, and other external sources, providing a flexible and powerful tool for data collection and analysis.

3. Access Types: Access Types allow users to group and organize data points based on their usage and permissions, providing a powerful tool for managing and controlling access to data within the Dataset Module.

4. Visual Query Editor: The Visual Query Editor provides a user-friendly interface for creating and editing SQL queries, making it easy for users to define complex queries without needing extensive SQL knowledge.

5. Customizable Dashboards: The Dataset Module provides the ability to create custom dashboards and visualizations based on the data collected by the module, allowing users to easily view and analyze data in real-time.

6. Store Procedures Execution: The Dataset Module can execute Store Procedures and return the results to the platform, allowing users to perform advanced data manipulation and analysis within the context of the platform.

7. Real-Time Execution: The Dataset Module supports real-time execution of SQL queries, allowing users to monitor and analyze data as it is generated in real-time.

Datasets Data Server Service

The Datasets Data Server Service is an essential part of the Datasets Module. It is responsible for managing and providing efficient access to datasets, which are structured collections of data used in various project applications such as data analysis, reporting, and visualization. The service ensures high performance and seamless integration with other components, offering flexibility and ease of use when working with datasets.

Processing Requests from Other Modules

The Processing Requests from Other Modules feature in FactoryStudio enables smooth communication between different project modules. It handles data requests and interactions, ensuring efficient data exchange and coordination among various components such as HMI/SCADA screens, scripting, and reporting tools.

Databases Used when Running the Project

The platform has pre-configured databases that store essential project information, including real-time and historical data, alarms, events, and system configurations. These databases provide a reliable and efficient foundation for data storage and retrieval, allowing users to focus on building and customizing their projects without worrying about database setup and management.

Data Source Virtualization Benefits

Data Source Virtualization is an advanced feature that simplifies data management across multiple data sources. It offers a unified interface for accessing, querying, and manipulating data, regardless of the underlying data storage technology, ensuring flexibility and ease of use.

Agnostics, Standards, Centralized Management

FactoryStudio's Data Source Virtualization is designed to be agnostic and adhere to industry standards, allowing it to work seamlessly with various data storage technologies, such as SQL databases, OPC UA servers, or custom data sources. This approach enables centralized management of data connections and configurations, streamlining the process of integrating different data sources into your project.

Detailed and Advanced information

The execution of the Dataset module, i.e., executions of configurations made in Queries, Tables, and Files, runs in an independent process "TRunModule.exe (Dataset)" that reads all the project configurations related to this module but does not directly execute the interface with the databases. Within TServer, there are services to access the database, and connections and calls to the database occur in TServer. The Dataset module merely consumes this bank access service from TServer.

• Connections with the Database
  For each configured DB, there's only a single access connection created for this database, meaning that there's only one execution thread for each DB. If parallel execution of commands to the same database is needed, it's possible to create more than one DB pointing to the same database.

• Synchronous and Asynchronous Methods
  Both in Tables and in Queries, if the properties are used to trigger the execution, such as triggering the "Select" property, this will make the execution Asynchronous. In other words, after the trigger in the property, the execution will continue normally, and in parallel, this property will propagate from where it suffered the trigger (screen or script) to the server, then being synchronized with the Dataset module which in turn will make the necessary calls to the TServer's bank access service. After the return of the TServer with the execution of the Select for the Dataset module, the result will be updated in the properties and attributes of the module, the mapping treatment with tags will be done, and the result will be available to the entire project.
  
  As for the available methods, the execution will be synchronous. For example, when calling the SelectCommand method, the execution will be locked in this method while the Dataset module executes the call of the TServer service, which interfaces with the database returning the result to the Dataset module. The Dataset module then processes the mapping with tags and only then provides the result in the form of the method's return, allowing the continuation of the execution.
  
  Therefore, it's important to be careful with synchronous calls within code-behind screens, which are mono-thread, whether they are WPF or HTML5 screens. Mechanisms like using Script task or Action Server Request can help make an asynchronous execution without needing to use the attributes, when there's a need to handle the result even after an asynchronous execution.

• Tags Domain Client Used in Mapping
  It's possible to use client-type tags in the Dataset module's tag mapping. When executing a synchronous or asynchronous command, the assignment in the tags will be executed by the Dataset module (not by TServer) in the original domain of the call. Therefore, if the call was executed on a client (screen or client script), after the return of the result from TServer to the Dataset module, the mapping will be executed between received values and tags, and Client domain Tags can be used in the mapping. It's important to understand and consider if the project's need is an execution that makes the value available to the entire project, in this case using server domain tags, or only for the client's scope, in this case using client domain tags.

• Attributes and Server Domain Properties (concurrence management)
  It's important to know and remember that the module's attributes are all server domain. Therefore, if there are multiple Tasks or clients using the same attributes, there may be competition and confusion in execution. For instance, a client sets the SQLStatement of a query, and at the same time, a Task also sets this SQLStatement with a different value from the client. During the command execution (for example, a select – triggering the Select property which is also a server domain), the last value in the SQLStatement is what will be used in the operation.

• Use of Tag DataTable
  The return of the Select methods is a .Net DataTable object. If you're not using mapping to assign each value from a specific row's columns to Tags, you can manipulate this returned dataTable directly in the script, or you can assign it to a Tag of the DataTable type. This allows the result to propagate to other modules, like clients or reports, and be easily presented in a dataGrid, PieChart, or even for XY chart presentation using the CallBack GetSamples.

• DataTable Properties Available in the Dataset Module
  As mentioned, there are several server domain properties within the Dataset module. There is a property called AsyncContents, which is a .Net DataTable type. When the Select property is triggered, initiating an asynchronous execution, after the return, the values of each column of the 1st row are assigned to the tags that were configured in the Mapping. When the Next property is triggered, the Dataset module navigates to the next row and assigns the column values to the tags, even those configured in the Mapping. The table used for this navigation is stored in the AsyncContents property and is updated only when the select is executed. It's possible to modify the CursosIndex to jump or go back to a specific position in the row navigation.
  For synchronous commands, the method returns the dataTable, but it's also stored in the LocalContents property; it will be overwritten with the result of each executed command.

• Store&Forward
  Only banks used to store Historian and Alarm can have the Store&Forward functionality enabled because this feature requires the use of some control columns that exist in the schema of Historian and Alarm, and not in generic banks. Please refer to the Store&Forward section of the Historian and Alarm modules for more details.

• Data Traffic Volume Between TServer, Dataset Module, and Other Modules
  When a select is executed in the bank, the WhereCondition property of the Dataset>Table defines the return condition just like the SQLStatement property of the Dataset>Query. Therefore, the volume of data that can be returned can vary from no row to the entire table (when there's no filter or a WhereCondition). There is no software-side protection concerning the size of the return DataTable, and obviously, the larger the table size, the more allocated memory, the longer the traffic time of this table between TServer and the Dataset module, and then its final destination. Care should be taken with the scalability of these tables' size, as well as having suitable hardware to support the expected data volume.

• Specific Syntax Used in the SQLStatement of Database
  The SQLStatement property of Dataset>Queries allows for the full SQL statement to be executed, therefore it's possible to use SQL syntax specific to each bank. For example, SQLite supports the LIMIT clause, which in SQLServer is called TOP. Just as care must be taken with the separators of table and column names, some use [] others "". As much as possible, the software will try to normalize and use the correct separators when using the WhereCondition and filters on objects with the alarm object, but in SQLStatement, it's up to the user to use the correct syntax.

• Protection Against SQL Injection
  In the database, calling Stored Procedure, there is a great concern about this part of "injection," because if parameters are passed as plain text in SQL Statement, the "injection" could be possible. Against this, we use the .Net API where parameters are added to a list, making it impossible to code injection.
  
  Therefore, it's advisable to use parameters in the Stored Procedures calls following the following syntax:
  
  "execute <StoredProcedureName> <@paramName1>={Tag.Tag1}, <@paramName2>="{Tag.TagStr}", <@paramName3>={Tag.OutValue} OUTPUT, ... , @RETURN_VALUE={Tag.RetValue} RETURNVALUE"
  
  By using this syntax, you effectively parameterize the Stored Procedure calls and prevent SQL injection attacks. This way, each parameter value is interpreted strictly as a value and not part of the SQL command. This practice is fundamental to the security of your application, preventing unauthorized manipulation of the database.

• Using ServerIP to Access Remote Database Providers
  There are times when certain databases are not accessible from any computer. By using the ServerIP column, it's possible to redirect database access commands to a computer that has the required permissions. The prerequisite for this is that the software should also be installed on that computer. In this scenario, only TWebServer would be running. This system is responsible for interfacing with the database, receiving commands, establishing connections, etc. This method provides a workaround for situations where database access is restricted to specific machines.

• Time Zones
  All Date/Time type tags in the software are treated as UTC. Therefore, the dates that are written and read in the database are also in UTC. If you want to read/write a date in a local timezone, you should consider the time difference when preparing the SQL statement or when using the WhereCondition property.

• Dataset Module Performance
  The Dataset module's performance depends on many factors, including database performance, network latency, and the complexity of the SQL queries being executed. The software itself is designed to minimize overhead and execute queries as efficiently as possible, but ultimately the performance of the Dataset module is tied to these external factors. It's important to design your database schema and queries with performance in mind, and consider investing in high-quality hardware and network infrastructure to ensure optimal performance.

• Error Handling
  Error handling in the Dataset module is straightforward. If an error occurs during the execution of a command, the module's Error property (LastStatus) will be updated with the error message. This property can be monitored to handle errors in your application. Furthermore, if an error occurs during the execution of a synchronous method, the method will return an empty DataTable and the Error property will be updated or you can call methods like SelectCommandWithStatus where the status will be an output parameter in the method.

These are some of the key points you should keep in mind while using the Dataset module. It's a powerful tool that provides a lot of flexibility, but it also requires careful use and understanding of its features and limitations. Always keep best practices in mind when designing and implementing your database interactions, and don't hesitate to seek expert advice if you're unsure about anything.

Configuring the Dataset Module

Learn how to connect to data sources, create queries, and optimize performance for efficient data management.

This section provides essential guidance for setting up and customizing the Dataset Module, including:

• Configuration Workflow
• Managing DB Connections
• Datasets and SQL Queries
• Datasets and SQL Queries
• Datasets and SQL Queries

Configuration Workflow

The typical configuration workflow for the Dataset Module has the following sequence:

Create the required database connections (DBs)

1. Go to Datasets → DBs
2. Collect the information to connect with the databases required for your project.
3. Use the built-in SQLite database as a temporary development tool if one of your connected databases is not available yet.
4. The virtualization model with logical names for queries and tables will make your project work directly with the new connection with the production database, without having to change anything on the Project Configuration other than that database connection.

Prepare the Queries the Project uses

1. Go to Datasets → Queries
2. Either use the Visual Query Editor or get the query string from IT or plant facilitator.
3. Collect and create the logical names Dataset.Query to identify those queries.

Modify the Query to add real-time tags

1. Go to Datasets → Queries
2. Easily modify the query with the parameters that need to be connected with real-time values.
3. For instance, a query that has the text "WHERE col1 = 5" can be modified to "WHERE col1 = {{tag.Test}}". The value of the Tag will be added to the proper position when the query is executed.

Prepare the Tables the Project uses

1. Go to Datasets → Tables
2. When you need to Insert or Modify data, you need to access the Database Table directly.
3. In some cases, all the information you need is in one table, so there is no need to create a Query.
4. You can easily connect the contents that are inserted in the table with Tags in the Project.

Configure the Stored Procedures

1. Go to Datasets → Queries
2. The Module Database can execute Stored Procedures; just define it using the same interface for the queries.

Configure data exchange with Files

1. Go to Datasets → Files
2. If necessary to exchange values of Tags with plain text or XML files, set that configuration.

Use your Dataset logical objects

1. The logical object names created for Queries, Tables, and Files can be used in any part of the project.
2. Examples: Script calculation, Display visualization, and others.

Managing DB Connections

When using SQLite databases, the Module Dataset can automatically create the Database if necessary; for other ones, the Database itself must already exist before you set your connection. Users with any Permission groups can create new connections in the Project, but only the Administrator can configure databases password logins.

To create a new Database connection:

1. Go to Datasets → DBs.

2. Click Create New. The Create New Database Connection window displays.

3. Enter or select information, as needed.

4. Click OK. The database is added as a new row in the table.

5. Edit the row fields to modify the required settings.

Please check the Connecting to SQL Server and Connecting to Excel for additional information.

There are four database connection already created in any new Project:

Any of them can be customized to any type of database. 

The selection of best storage location depends on all kind of factors, from internal company procedures to the volume of data and how the data shall be used. Therefore, that is decision to each Project according to its requirements.

If needed to use another database for the pre-defined connections, execute the following steps:

1. Rename or Delete the previous DB. This step is necessary, as the system would not allow to create two objects with the same name. 

2. Crate a new DB with the same name of the previous DB, with the required Database and connection strings.

ConnectionString example for SQL Express 

• Data Source: The server path and instance that will have the databases.
• Initial Catalog: The name of the database that will be used.

Project Test Databases and Example

Assume you need to transition the AlarmHistorian configuration to a Microsoft SQL production database within your organization.

During the development and testing phases of the application, you may prefer not to publish alarm events to that database yet.

In other platforms, it would be necessary to manually switch connections between test and production environments or devise workarounds to handle this situation.

In our framework, we provide a built-in, optional feature: configure the AlarmHistorian DB to target the production database, regardless of its current availability or intended use. Subsequently, run the project in Test Mode, storing data in the local SQLite file <projectName>.dbAlarmHistorianTest.

When running the Project in Validation Mode, there is a configuration, which is true by default, that can override the connection of the pre-defined DB, using testing ones instead.

Those are database files that can be enabled to use when running in Validation Mode:

Dataset Queries Configuration

You can configure queries to perform more advanced functions with SQL statements to work with data from external databases.

To configure Dataset queries:

1. Go to Datasets → Queries.
2. Enter the field values as needed.

With the Visual Query Editor, users can drag and drop tables, define relationships, and add filters and conditions using a simple graphical interface. Once the query is created, it can be saved and executed like any other query within the Dataset Module.

Check the Visual SQL Query Builder page for complete information.

Dataset Tables Configuration

To configure dataset tables:

1. Go to Datasets → Tables.
2. Enter the field values as needed.

Dataset Files Configuration

To configure dataset files:

1. Go to Datasets → Files.

2. Enter the field values as needed.

Working with the Dataset Module

Runtime Execution

One of the key features of the Dataset Module is the ability to execute SQL queries and retrieve data in real-time. Here are some ways to leverage the runtime execution features of the Dataset Module:

• Create SQL queries to retrieve data from external databases.
• Use query results to trigger events and actions within the platform environment.
• Configure event triggers based on specific query criteria, such as changes to a specific data point or a threshold value being exceeded.

The Dataset Module can be easily integrated with other modules within the software environment. Here are some examples of how the Dataset Module can be used in conjunction with other modules:

• Alarm Manager: Configure alarms based on query results to trigger notifications and actions.
• Visualization: Display query results on screens and dashboards using DataGrids and other visualization tools.
• Scripting: Use query results to trigger custom scripts and perform complex data processing and analysis.

By leveraging these integration options, users can gain greater insight and control over their data sources within the platform. With the ability to execute SQL queries and trigger actions based on query results, the Dataset Module provides a powerful set of tools for working with data.

Monitoring Databases Connections

Monitoring Database Connections is an essential aspect of maintaining a reliable and efficient system within the platform. By keeping track of database connections, you can ensure that your data is being accessed and updated correctly. Here are some ways to monitor database connections:

1. IsStarted: This property indicates if the dataset is started, meaning it has been initialized and connected to the database. You can use it to check if the dataset is currently running. 

bool isStarted = @Dataset.DB.YourDatabaseName.IsStarted;

2. Query Execution Time: You can measure the execution time of a query by checking the time before and after executing the query. 

DateTime startTime = DateTime.Now;
@Dataset.Query.YourQueryName.SelectCommand();
DateTime endTime = DateTime.Now;
TimeSpan executionTime = endTime - startTime;

3. ConnectionString: You can check the connection string used for the database to ensure it is configured correctly.

string connectionString = @Dataset.DB.YourDatabaseName.ConnectionString;

Showing DataGrids Tables on Displays

One of the key features of the Dataset Module is the ability to display query results on screens and dashboards using visualization tools like DataGrids. Here are some steps for using DataGrids to display query results:

1. Create a query in the Dataset Module to retrieve the desired data.
2. In the Displays Module, add a DataGrid control to the screen or dashboard.
3. Configure the DataGrid to display the query results by selecting the data source and column mappings.
4. Save and preview the screen or dashboard to display the query results on the DataGrid.

Using Query Results on Scripts and Tags

Users can use query results to trigger actions in custom scripts and tags. Here are some steps for using query results in scripts and tags:

1. Create a query in the Dataset Module to retrieve the desired data.
2. In the Scripting Module, create a custom script that references the query results.
3. Use the query results to trigger specific actions within the script, such as sending notifications or updating tags.
4. Save and execute the script to perform the desired actions.

Check the Using Stored Procedures page for additional information.

Troubleshooting and Best Practices

Common #Issues and Solutions

Best Practices and #Recommendations

Dataset Runtime Attributes

The Dataset namespace exposes properties and methods of the .NET objects used by the Dataset Module execution.

For more information on namespaces and objects, go to Objects and Attributes.

This section describes only some commonly used properties, for the full list properties and methods, go to the Namespaces Reference.

In this section...