Page History

Datasets Module Execution

The Dataset module , specifically the "TRunModule.exe (Dataset)" process, reads all project configurations relevant to this module but does not directly interface with the databases. Database access services are provided by TServer, with the Dataset module consuming these services.

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.

For each configured database (DB), there's only one access connection. This signifies a single execution thread per DB. However, for parallel execution of commands to the same database, additional DBs can be configured, all pointing to the same database.

The Dataset module is a vital component of the system, functioning independently via the process "TRunModule.exe (Dataset)". It's important to understand that this module does not directly interact with the databases, but rather, it reads project configurations related to the module and utilizes services within TServer to access databases. As a result, all database connections and calls occur in TServer.

In terms of database connections, for each database configured, there's a single access connection created. This design follows a one-thread-per-DB approach. However, should the need arise for parallel command execution to the same database, you have the option to create more than one DB pointing to the same database.

Synchronous and Asynchronous Methods in Tables and Queries

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.

-------------------

The system provides both synchronous and asynchronous methods within Tables and Queries. A trigger in the properties, such as the "Select" property, results in asynchronous execution. This means the execution proceeds normally post-trigger, and in parallel, this property is propagated from its point of origin (screen or script) to the server. It is then synchronized with the Dataset module, which in turn calls the necessary TServer's bank access service. Once the TServer returns with the Select execution for the Dataset module, the module will update the properties and attributes with the result, perform a mapping treatment with tags, and make the result available to the entire project.

In contrast, the system's available methods execute synchronously. For example, calling the SelectCommand method will lock the execution at this method until the Dataset module executes the TServer service call, which interfaces with the database to return the result to the Dataset module. The Dataset module then processes the mapping with tags and provides the result in the form of the method's return, allowing the execution to continue.

This distinction is crucial when considering synchronous calls within code-behind screens, which are mono-thread, whether they are WPF or HTML5 screens. Mechanisms like Script task or Action Server Request can help create an asynchronous execution without needing to use the attributes, especially when there's a need to handle the result even after an asynchronous execution.

---------------

The properties in Tables and Queries can trigger execution, such as triggering the "Select" property, which makes the execution asynchronous. This means that after the trigger in the property, the execution continues normally. In parallel, this property propagates from its trigger source (screen or script) to the server, then synchronizes with the Dataset module. The module then makes necessary calls to TServer's bank access service. After TServer's return with the Select execution for the Dataset module, the result gets updated in the module's properties and attributes, is processed for tag mapping, and is finally made available to the entire project.

Contrarily, available methods execute synchronously. For instance, calling the SelectCommand method locks the execution at this method while the Dataset module executes the call of the TServer service, interfaces with the database, and returns the result to the Dataset module. The Dataset module then processes the tag mapping and provides the result in the form of the method's return, allowing the continuation of the execution.

Special caution is required with synchronous calls within code-behind screens (mono-thread, WPF or HTML5 screens). Mechanisms like using Script task or Action Server Request can help create an asynchronous execution without needing to use attributes, when there's a need to handle the result even after an asynchronous execution.

Usage of Client-Type Tags in the Dataset Module's Tag 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.

----------------

Client-type tags can be used in the Dataset module's tag mapping. During execution of a synchronous or asynchronous command, the assignment in the tags gets executed by the Dataset module (not by TServer) in the original domain of the call. Hence, if the call was executed on a client (screen or client script), after the result's return from TServer to the Dataset module, the mapping between received values and tags gets executed. Client domain Tags can be used in the mapping, which allows for specific control over the scope of availability of the value - either for the entire project (using server domain tags) or just for the client's scope (using client domain tags).

---------

Tags Domain Client can be used in the Dataset module's tag mapping. When executing a synchronous or asynchronous command, the assignment in the tags is executed by the Dataset module (not by TServer) in the original domain of the call. This design allows for the use of client-type tags in the Dataset module's tag mapping. When a call is executed on a client (screen or client script), the mapping is executed between received values and tags after the return of the result from TServer to the Dataset module. Depending on the project's needs, you can use server domain tags for execution that makes the value available to the entire project, or client domain tags for client's scope only.

Attributes and Server Domain Properties (Concurrency Management)

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.

-----------

All module attributes are server domain. Therefore, if multiple Tasks or clients are using the same attributes, there might be competition and confusion in execution. For example, if a client and a Task both set the SQLStatement of a query simultaneously with different values, the last value in the SQLStatement will be used in the operation.

--

You must remember that all the module's attributes are server domain. This can lead to competition and confusion in execution when multiple Tasks or clients are using the same attributes. For instance, a client and a Task may set the SQLStatement of a query simultaneously but with different values. During command execution, the last value in the SQLStatement is what will be used in the operation.

Use of Tags of type DataTable

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.

----------

The return of Select methods is a .Net DataTable object. This can be manipulated directly in the script or assigned to a Tag of the DataTable type, allowing the result to propagate to other modules like clients or reports.

The Select methods return 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. Alternatively, you can assign it to a Tag of the DataTable type, which 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.

(Meint TK methods to Convert on Datatable and Array of Templates)

Dataset Module Properties

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.

-----------

There are several server domain properties within the Dataset module, including a .Net DataTable type property called AsyncContents. When the Select property is triggered, initiating an asynchronous execution, the column values of the first row are assigned to the tags configured in the Mapping. The table used for this navigation is stored in the AsyncContents property and is

====================================================================

Navigation Through AsyncContents Property

The AsyncContents property is used for navigation through the DataTable. If Select is triggered again while AsyncContents still contains data, the Dataset module navigates to the next row, and the column values are assigned to the tags configured in the Mapping. This navigation continues until the last row. After the last row, if Select is triggered again, the module will request a new execution to TServer.

Use of Other Asynchronous Properties

Other asynchronous properties like AsyncFirst, AsyncNext, AsyncPrevious, and AsyncLast can be used to navigate through the rows of AsyncContents without triggering a new Select. They can also be used to reset the navigation (AsyncFirst) or to navigate to a specific row (AsyncLast).

Control over Data Navigation

A property named AsyncEOF (End of File) indicates if the navigation has reached the end of the AsyncContents. This property is set to true when the last row is navigated. In addition, a property named AsyncBOF (Beginning of File) indicates if the navigation is at the beginning of the AsyncContents. Both properties can be used to control the data navigation.

Refreshing the AsyncContents

The AsyncRefresh property can be used to refresh the AsyncContents. When triggered, it requests a new Select execution to TServer, replacing the current AsyncContents.

Adjustments to Asynchronous Behavior

If there's a need to adjust the asynchronous behavior, the MaxRows property can be used. It defines the maximum number of rows to be returned in each Select execution, which can be used to limit the number of rows in the AsyncContents.

Synchronous and Asynchronous Execution in Scripts and Actions

Scripts and Actions can be executed synchronously or asynchronously. Using the attribute "Async" followed by the method name will make the execution asynchronous, similar to the behavior of the properties. If the method name is used without the "Async" prefix, the execution will be synchronous.

Asynchronous Execution in Screens

The "Async" prefix can also be used in the screens, making the execution asynchronous. This allows the screen to remain responsive while the execution is taking place.

Impact on Tag Mapping

The way of execution, whether synchronous or asynchronous, affects the Tag Mapping. For synchronous execution, the tags are assigned after the method's return. For asynchronous execution, the tags are assigned during the asynchronous execution, and the method's return is a boolean, indicating if the execution was successfully triggered.

Handling Asynchronous Execution Results

The handling of the result after an asynchronous execution can be done using the AsyncContents property, the Mapping, and the AsyncReturn tag. The AsyncReturn tag is set to true when the asynchronous execution is successful, and to false otherwise. This tag can be used to check the status of the execution.

OnAfterAsyncReturn Event

The OnAfterAsyncReturn event is triggered after the return of an asynchronous execution. This event is executed on the server but can be used to trigger a client script or action, which allows handling the result on the client side.

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.

--

Store&Forward functionality is only applicable to databases used to store Historian and Alarm, as this feature relies on specific control columns present in the schema of Historian and Alarm, but not in generic databases. For further details, please refer to the Store&Forward section of the Historian and Alarm modules.

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.

facilitates efficient database interactions by utilizing TServer services. While it doesn't directly interact with databases, it manages synchronous and asynchronous executions for optimal performance. Understand the differences between these methods by reading more on the Datasets Module Execution page.

Data Management

The Dataset Module offers versatile methods for managing data and concurrency within solutions, including Data Table tags and Async Contents. Understand how to avoid conflicts and optimize performance, reading more on the Data Management page.

Datasets Runtime Attributes

The Datasets Namespace exposes properties and methods from the .NET objects used by the Historian Module execution. You can use these properties and methods on your Displays or to create Scripts and Alarms. The Dataset Runtime Attributes page lists all options available.

Preventing SQL Injections

When calling Stored Procedures in the database, there's a significant concern about SQL injection, especially when passing parameters as plain text in SQL statements. To mitigate this, we utilize the .NET API, where parameters are added to a list, effectively preventing code injection. Therefore, we advise using parameters in Stored Procedure calls with the following syntax:

execute <StoredProcedureName> <@paramName1>={Tag.Tag1}, 

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: 

<@paramName2>="{Tag.TagStr}", <@paramName3>={Tag.OutValue} OUTPUT, ... , @RETURN_VALUE={Tag.RetValue} RETURNVALUE

This approach parameterizes "By using this syntax, you effectively parameterize the Stored Procedure calls and prevent guards against SQL injection attacks. This way, each Each parameter value is strictly 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., providing a critical layer of security against unauthorized database manipulation.

Network Gateway Access And Time Zone Handling

In some scenarios, specific databases might not be accessible from all computers due to network topology or security constraints. The ServerIP column can

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 databasemachine with the necessary permissions, provided you install our platform on that machine. Only TWebServer would be active in such cases, handling database interaction, receiving commands, and establishing connections, etc. This method provides offers a workaround for situations where database access is restricted to specific machines.

Handling Time Zones

All Date/The platform treats all Date and Time type tags in the software are treated as UTC. ThereforeConsequently, the dates that are written to and read in from the standard database, such as historical data and alarms, are also in UTC. If you want it is necessary to read /and write a date dates in a the local timezone, you should consider account for the time difference when preparing the SQL statement or when using the WhereCondition property. Other external data tables, from third parties or the client, can use any timezone. To assist in handling this data, you can configure the DateTimeMode column with the type of timezone used in the database (UTC or Local Time), and the system will automatically convert it when reading and writing to tags.

Dataset Module Performance

The Dataset moduleModule's performance depends on many factors, including database performance, network latency, and the complexity of the executing SQL queries being executed. The software itself is designed to platform will minimize overhead and execute queries as efficiently as possible. However, but ultimately, the performance of the Dataset module is tied to these external factors. It's important essential 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 error message will update the module's Error property (LastStatus) will be updated with the error message. This property can be monitored Last Status). You can monitor this property to handle errors in your application. Furthermore, if an error occurs during the execution of a synchronous method, the method process will return an empty DataTable Data Table and update the Error property will be updated or . Alternatively, 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. However, 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 . Feel free to seek expert advice if you 're unsure about anything.need clarification on anything.

Backup of SQLite Databases

There are several ways to back up SQLite databases in a solution. One simple method is to make a copy of the database file itself, but this method may not be suitable if the database is actively being written to or is very large. Here are a few suggestions for backing up SQLite databases in your solution:

• SQLite provides a built-in backup command that can be used to create a backup of a database file. The syntax for this command is as follows:

  Code Block
  sqlite3 source_database.sqlite .backup backup_file.sqlite

  This command creates a backup of the source database and stores it in a new file called backup_file.sqlite. You can then copy this file to a safe location for backup purposes.

• SQLite also provides an Online Backup API that you can use to create a backup of a database file while your application is still using it. This API offers several benefits over the backup command, such as the ability to perform incremental backups and monitor the progress of the backup process. Read more in the SQLite Backup API documentation.

Regardless of your chosen method, it's crucial to store your backup files in a secure location and test them regularly to ensure that you can use them to restore your database if needed.

In this section: