Configuration

Channel set up

The first step when configuring the SqlClient is to create a connection to a database. This is represented by a channel. In this example, the name of the new channel is SQLServer, since it is communicating with an SQL Server database.

Figure 3: SqlClient settings for SQL Server databases

Each channel requires the following parameters to be set:

• Enable data collection: When disabled, the channel will remain inactive and no transactions will be executed. The default value is set to enabled.
• Timing:
  • Query timeout: Time waiting for a valid response before retrying or moving to the next transaction, displayed in milliseconds. The valid range is 100ms to 600000ms. The default value is 3000ms.
  • Retry attempts: Number of communication retries before considering the target database to be unreachable or the query invalid and moving to the next transaction. The minimum value is 0 (no retries are attempted) and the maximum value is 1000 retries. The default value is 3, which means that for each unresponsive transaction, the driver will make a total of 4 attempts (1 initial request and 3 retries).
  • Inter-request delay: Time between one transaction finishing and the next transaction starting, displayed in milliseconds. This parameter can be used when a target database is not responding due to receiving too many queries too quickly. The valid range is 0ms to 3600000ms. The default value is 0ms, which means that the next request will be sent as soon as a valid response has been received.

• Connector:
  • Type: Determines the target database.
    • MySQL: Connects to Oracle MySQL database.
    • MariaDB: Connects to MariaDB database.
    • SQL Server: Connects to Microsoft SQL Server database.
    • PostgreSQL: Connects to PostgreSQL database.

Figure 4: Different database types supported by SqlClient

• Connection to Oracle MySQL and MariaDB:

When connecting to Oracle MySQL or MariaDB, the following options are displayed:

Figure 5: MySQL connector configuration

• Connection:
  • Host: Hostname or IP address of the target database.
  • Port: TCP port of the target database. The valid range is 1 to 65535. The default value for MySQL and MariaDB is 3306.
  • Default database: Unless left blank, this database will be used as the default database. Otherwise, all queries must use the full table name (database.table).
  • Connection timeout: Specifies the maximum time permitted for establishing a connection to the database, displayed in milliseconds. Default value is 10000ms.
  • Reconnect delay: Waiting time before trying to reconnect after a failed connection attempt, displayed in milliseconds. Default value is 60000ms.
• Authentication:
  • Username: Specifies the username used to authenticate with the database.
  • Password: Specifies the password used to authenticate with the database.
  • Insecure authentication: When enabled, the legacy authentication method will be used to connect to the database.
• SSL:
  • Reject unauthorized: When set to false, certificates that are not signed by a trusted Certificate Authority can be used. Otherwise, the connection will be automatically dropped if the certificate is not signed by a trusted Certificate Authority.
  • CA: Certified Authority certificate file used for authentication. The CA certificate must be in PEM format.
  • Private key: Private key file used for authentication. The key must be in PEM format.
  • Certificate: Certificate file used for authentication. The certificate must be in PEM format.
• Additional options:
  • Custom timezone: Specifies the timezone configured in the MySQL server. This is used when casting values to JavaScript Date objects.

• Connecting to SQL Server

     When connecting to an SQL Server, the following options are displayed:

Figure 6: SQL Server connector configuration

• Connection:
  • Host: Hostname or IP address of the target SQL Server database.
  • Port: TCP port of the target database. The valid range is 1 to 65535. The default value for SQL Servers is 1433.
  • Default database: Unless left blank, this database will be used as the default database. Otherwise, all queries must use the full table name (database.table).
  • Connection timeout: Specifies the maximum time permitted for establishing a connection to the database, displayed in milliseconds. The default value is 15000ms.
  • Idle timeout: Maximum time the connection to a database can remain idle for before being automatically disconnected to reduce server load, displayed in milliseconds. The default value is 30000ms.
  • Reconnect delay: Time between new connection attempts after the current one fails.
• Authentication:
  • Username: Specifies the username used to authenticate with the database.
  • Password: Specifies the password used to authenticate with the database.
  • Domain: Specifies the user domain for this connection. When left blank, this field refers to a database user rather than a domain user.
• Additional options:
  • Application name: Application name used in this connection.
  • Encrypt: Specifies whether a connection needs to be encrypted or not. Encryption is required when connecting to a Microsoft Azure SQL Database.

• Connecting to a PostgreSQL:

When connecting to a PostgreSQL, the following options are displayed:

Figure 7: PostgreSQL connector configuration

• Connection:
  • Host: Hostname or IP address of the target database.
  • Port: TCP port of the target database. The valid range is 1 to 65535. The default value for PostgreSQL is 5432.
  • Default database: Unless left blank, this database will be used as the default database. Otherwise, queries must use the full table name (database.table).
  • Connection timeout: Specifies the maximum time permitted for establishing a connection to the database, displayed in milliseconds. Default value is 15000ms.
  • Idle timeout: Maximum time the connection to a database can remain idle for before automatically being disconnected to reduce server load, displayed in milliseconds. The default value is 30000ms.
  • Reconnect delay: Time between new connection attempts after the current one fails.
• Authentication:
  • Username: Specifies the username used to authenticate with the database.
  • Password: Specifies the password used to authenticate with the database.
• SSL:
  • Reject unauthorized: When set to false, certificates that are not signed by a trusted Certificate Authority can be used. Otherwise, the connection will be automatically dropped if the certificate is not signed by a trusted Certificate Authority.
  • CA: Certified Authority certificate file used for authentication. The CA certificate must be in PEM format.
  • Private key: Private key file used for authentication. The key must be in PEM format.
  • Certificate: Certificate file used for authentication. The certificate must be
      in PEM format.

Transactions

Each database connection can execute one or more queries. These queries are organized into transactions, which are used to either retrieve data from the database (using a query and a parser) or to insert data into the database (using a serializer and a query). There are two types of transactions: StrandardTransaction and HistoryTransaction, each with a distinct purpose. 

• StandardTransaction: This type of transaction is used to read data from a database and assign it to tags, whether acting directly as the tag data source or by writing to tags whose source is another driver (such as ModbusClient or SiemensClient). Standard transactions can also be used to insert data from N3uron (passed as parameters) into a database. For example, in order to synchronize data between a PLC and a database. An example StandardTransaction configuration can be seen in the below screenshot:Figure 8: StandardTransaction configuration 

• Enable data collection: Enables or disables data collection in the specified transaction. The default value is set to enabled. If disabled, the transaction will not be executed.
• Triggers: List of triggers that will cause this transaction to be executed. More information can be found in Triggers.
• Parameters: List of parameters that will be available to all scripts in this transaction. More information about parameters can be found in $.parameter.
• Query: Query used in this transaction, either for inserting or updating data to the database or to read data from the database:
  • Query type: Determines how the query will be generated. Currently, only custom queries are supported (queries generated by running a custom JavaScript script).
  • Script: The Script that will be used to generate the query. The output of this script (which is controlled by setting the $.output variable) must be a string. Alternatively, it can be set to empty, in which case the current execution of the transaction will be aborted. More information about custom scripts can be found in User defined scripts.
• Parser: Controls how the query results are converted into N3uron data and subsequently written to tags.
  • Parser type: Selects how the data will be parsed. Valid options are:
    • None: No parsing will be done. Any data returned from the database is discarded. This setting can be used when executing an INSERT or UPDATE query, since no record set will be returned from the database in these types of queries.
    • Custom: Parses the data received from the database by running a custom JavaScript script.
  • Script: When the parser is set to custom, this field contains the script that will be used to parse the incoming Dataset into a N3uron compatible format. This script receives a Dataset (or a Dataset array if the query returns multiple record sets) as the input (in the $.input property) and must create an array of tag data objects (composed of a tag and value, with optional quality and timestamp settings) that will then be automatically saved to tags. This is done by executing a tag update (if the transaction is configured as the tag source) or a tag write (if the source of the tag is any other driver). If both fail, the event is discarded. More information can be found in User defined scripts.
• Auto-demotion: Defines if a transaction should be temporarily set to "off-scan" when the database is not responding. If set to "off-scan", the query will not be sent to the database. By placing a non-responsive transaction offline for a specific time period, the driver can continue to optimise communications with other transactions in the same channel. Once this demotion period has been reached, the driver will re-attempt to execute the non-responsive transaction. If the transaction is responsive, it will be set to "on-scan". Otherwise, the "off-scan" time period will restart after the specified number of failures has been reached.
  • Enabled: When set to enabled, if the specified number of successive failures is reached, data collection for this device will be set to “off-scan” during the specified interval.
  • Failures: Specifies how many consecutive rounds of request timeouts and retry attempts must occur before the device is set to "off-scan". The minimum value is 1 and the default value is 3.
  • Delay: Time the transaction is set to "off-scan" for when the max consecutive failures value has been reached. When the specified interval expires, the driver will reset the transaction to "on-scan" and allow another communication attempt to be made. The minimum value is 1000 and the default value is 60000 milliseconds.

• HistoryTransaction: HistoryTransactions are used to store historical data from N3uron to the database. This is done by storing all events received from tags into a buffer, and inserting these events into the database when the transaction is executed. See below for a HistoryTransaction configuration example:Figure 9: HistoryTransaction configuration                                                                                                                                                                                                                                                                                                                                                  

• Enable data collection: Enables or disables data collection in the specified transaction. The default value is set to enabled. If disabled, the transaction will not buffer and send data to the database.
• Mode:Determines how the transaction will buffer the events from N3uron.
  • Event: The transaction will buffer any event from tags that match the tag filters.
  • Snapshot: The transaction will not buffer events. Instead a snapshot of all tags that match the tag filters will be taken whenever the transaction is triggered.
• Triggers: List of triggers that will cause this transaction to be executed. More information can be found in Triggers.
• Parameters: List of parameters that will be available to all scripts in this transaction. More information about parameters can be found in $.parameter.
• Serializer: Controls how N3uron tag events are transformed from tag data objects into a Dataset or Dataset array that will then be sent to the database:
  • Serializer type: Determines how data will be serialized. Currently, only custom serializers are supported (those that run a custom JavaScript script to serialize the tags events into a Dataset).
  • Script: The Script that will be used to serialize the N3uron tag events into one or more Datasets. This script receives an array of tag data objects as the input (in the $.input property) and must create a Dataset or Dataset array. Afterwards, each Dataset will be sent to the query as the input in order to generate the query. More information can be found in User defined scripts.
• Query: Query used to insert data from N3uron into the database:
  • Query type: Selects how the query will be generated. Currently, only custom queries are supported (queries generated by running a custom JavaScript script).
  • Script: The Script that will be used to generate the query. This script receives a Dataset as the input (in the $.input property) and this Dataset can be used to generate the query. If there are multiple Datasets available from the serializer, the query builder (and the query) will execute once per Dataset until all Datasets have been processed.
    The output of this script (which is controlled by setting the $.output variable) must be a string. Alternatively, it can be set to empty, in which case the current execution of the transaction will be aborted. If there are multiple Datasets available from the serializer, only the current Dataset will be skipped. More information about custom scripts can be found in User defined scripts.
• Auto-demotion: Defines whether a transaction should be temporarily set to "off-scan" when the database is not responding. If set to "off-scan", the query will not be sent to the database. By placing a non-responsive transaction offline for a specific time period, the driver can continue to optimise communications with other transactions in the same channel. Once this demotion period has been reached, the driver will re-attempt to execute the non-responsive transaction. If the transaction is responsive, it will be set to "on-scan". Otherwise, the "off-scan" time period will restart after the specified number of failures has been reached.
  • Enabled: When set to enabled, if the specified number of successive failures is reached, data collection for this device will be set to “off-scan” during the specified interval.
  • Failures: Specifies how many consecutive rounds of request timeouts and retry attempts must occur before the device is set to "off-scan". The minimum value is 1 and the default value is 3.
  • Delay: Time the transaction is set to "off-scan" for when the max consecutive failures value has been reached. When the specified interval expires, the driver will reset the transaction to "on-scan" and allow another communication attempt to be made. The minimum value is 1000. The default value is 60000 milliseconds.
• Tag filters: A list of tag filters that will be buffered by the HistoryTransaction and stored to the database. If the list is left empty, all of the events will be buffered. If there are multiple tag filters, only one filter needs to match a tag in order for it to be buffered. More information can be found in Tag filter.
  

Triggers

Each transaction can have one or more triggers that dictate when it will be executed. Each trigger is checked continuously and whenever one becomes active, the transaction will be executed. The following are the list of available triggers: 

• Periodic: This type of trigger becomes active after a specified period has passed. It is configured as per the below example: Figure 10: Periodic trigger configuration

• Scan rate: Specifies the time period between each time the trigger is activated, displayed in milliseconds.
• Type: Determines how this time period is calculated. Valid values are:
  • Fixed time: The trigger will activate as soon as the specified scan rate has passed since the previous execution ended. For example, if a trigger is set to execute every 30 seconds, and the first execution occurred at 12:30:17, the next execution will be at 12:30:47.
  • Fixed interval: The trigger will execute at specific intervals based on the scan rate. As per the previous example, if the scan rate is 30 seconds, the trigger will execute at 12:30:00, 12:30:30, regardless of when the module started.
• Reschedule timer: This option is only applied when the type is set to Fixed time and a transaction has multiple triggers. If enabled, the transaction activation time will be rescheduled whenever another trigger is activated. For example, if the scan rate for a periodic trigger is 30 seconds, and the last activation was at 12:30:17, the next activation would be at 12:30:47. However, if another trigger trips at 12:30:29, then the timer of the periodic trigger will be rescheduled to activate at 12:30:59.

• TagCondition: This type of trigger activates whenever a tag based condition is met. It is configured as per the below example:Figure 11: TagCondition trigger configuration

• Tag: Path to the tag used in this tag condition trigger.
• Property: The property of the tag used to trigger the transaction (Value, Quality, or Timestamp).
• Initial change: If enabled, the transaction will be triggered by the initial subscription to the tag events as soon as the module is started (or whenever the tag model changes). Otherwise, the initial event won't be considered for the tag condition.
• Condition: The condition the tag must be in for the trigger to become active. If the tag is a string, it will be compared alphabetically (for example, aa > ba returns true, while aa > ab returns false).
• Value: Specifies the value used in the condition. It can be a number, a string, or a Boolean.
• Condition type: Determines how the condition will be evaluated:
  • If true: The action will trigger when the condition becomes true. It can only be triggered once (or once per tag update) depending on the value of the "Trigger on update" option.
  • If false: The action will trigger when the condition becomes false. As per the case above, it can only be triggered once (or once per tag update).
  • While true: The action triggers once when the condition becomes true, and then repeats periodically, according to the specified period.
  • While false: Same as above, except it triggers while the condition is false.
• Trigger on update: If true, the action will trigger whenever there is a new tag update that still satisfies the condition. Otherwise, it will only trigger the first time the condition becomes true. This option is only used when the condition type is set to If true or If false.
• Reset trigger: If enabled, the tag acting as the trigger will be reset after the transaction is executed. If disabled, the trigger must be manually cleared. Only used for If true and If false conditions.
• Reset value: Specifies the value that will be used to reset the tag that is acting as the trigger. Only used when Reset trigger is set to true or for If true and If false conditions
• Period: The time period between executions when the condition is set to While true or While false, displayed in milliseconds. The minimum value is 1000 milliseconds.

• BufferSize: This type of trigger only applies to HistoryTransaction. It's used to limit the number of events in the buffer by activating the trigger when the buffer size has surpassed a certain number of events. The following screenshot shows an example configuration:Figure 12: BufferSize trigger configuration

• Buffer limit: Determines how many events are needed to activate the trigger. This does not limit the number of events allowed in the buffer. The number of events can surpass the limit if the events are received before the trigger executes the transaction. 
  

Tag filter

Tag filters are used to select which tags from the tag model will be buffered by a HistoricalTransaction. By default, no filters are applied, which means that all tags in the node will be included. In order to reduce the number of tags, one or multiple filters can be applied to the tagpath of the tags. The following screenshot shows the tag filter configuration:

Figure 13: TagFilter configuration

• RegEx pattern: The Regular Expression used to filter the tagpath of the tags. Only tags that match the regular expression will pass the filter. The regular expression is applied to the entire tagpath. More information can be found in RegExr.
• Ignore case: Sets the "ignore case" flag for the regular expression. When configured, the regular expression will ignore the casing on the tag path. For example, the pattern /SqlClient with the ignore case flag set will match /SQLclient, /sqlclient or /SQlcLiEnt.
  

Tag configuration

StandardTransactions can be used to send data from field devices to a database or to retrieve data from the database and send it to field devices (PLCs for instance). This is a common scenario when dealing with recipes where specific production values are transferred from databases to the PLCs in the production lines and vice versa. In this case, the value of the tag quality or timestamp is driven by the connecting driver to the field devices (ModbusClient, SiemensClient, OpcDaClient, etc) and therefore, cannot be overwritten by the SqlClient.

StandardTransactions can also be used as the tag data source. In this case, the database acts as the data source, just like a field device, and the SqlClient controls the tag Value, Quality, and Timestamp. In this case, the tag needs to be configured to use SqlClient as its source. The tag uses the following configuration:

Figure 14: Example of a tag using SqlClient as data source

Source configuration settings contain the following parameters:

• Enabled: When disabled, tags won’t be updated with the values received from the device. Instead, they will essentially act as memory tags. When set to enabled, the tag value will be continuously updated with the values received from the field device, or in this case, the database. The default value is set to disabled.
• Module type: Defines the driver type used to retrieve values from the field. In this example, SqlClient must be selected from the drop-down menu. If SqlClient does not appear in the drop-down menu, this means that this driver has not been installed on this machine yet and must be installed.
• Module name: Selects which instance of the SqlClient module will provide the data for this tag.
• Config:
  • Transaction: Selects which transaction will provide data to this tag. The transaction must already exist in the selected SqlClient instance and must be set as a StandardTransaction. The format for this property is Channel/Transaction.
  • Tag address: The tag address is used to associate this N3uron tag with the tag retrieved from the database. This property can be left blank, in which case the full tag path must be used. If a tag address (or alias) is used, then the tag can be referred to in the transaction parser using this tag address (or alias), instead of the full tag path.