Database configuration

The Database Configuration dialog allows you to configure the necessary settings to link to an external database file.

• Database combo-box: Allows you to select either Primary or Secondary. With Primary, all settings displayed in the Database Configuration window apply to the Primary Database interface. Otherwise, they apply to the Secondary Database interface. You can configure the Secondary database in the following modes in the Type list that appears when Secondary is selected:

  • Disabled: In this mode, data is saved in the Primary Database only. If the Primary Database is unavailable for any reason, the data is not saved anywhere else. This option may cause loss of data if the Primary Database is not available.

  • Redundant: In this mode, data is saved in both Primary and Secondary Databases. If one of these databases is unavailable, data is still saved in the database that is available. When the database that was unavailable becomes available again, both databases are synchronized automatically.

  • Store and Forward: In this mode, data is saved in the Primary Database only. If the Primary Database becomes unavailable, data is saved in the Secondary Database. When the Primary Database becomes available again, the data is moved from the Secondary Database into the Primary Database.

    Note: The Primary and Secondary can be different types of databases. However, they must have the same fields.

    Using the Secondary Database, you can increase the reliability of the system and use the Secondary Database as a backup when the Primary Database is not available. This architecture is particularly useful when the Primary Database is located in the remote station. In this case, you can configure a Secondary Database in the local station to save data temporarily if the Primary Database is not available (during a network failure, for instance).

• Use project default checkbox: When this option is checked, the settings configured in the Default Database are used for the task that is being configured (Connection string, User name, Password, Retry Interval, and Advanced settings). When this option is not checked, you can configure these settings individually to the current task.

• Connection string field: This field defines the database for write and read values, as well as the main parameters used when connecting to the database. Instead of writing the Connection string manually, you can press the browse button (…) and select the database type from the Data Link Properties window. This field accepts String expressions.

  

  Note: The list of Database Providers shown in the Data Link Properties window depends on the providers actually installed and available in the computer. Consult the operating system documentation (or the database documentation) for further information regarding the settings of the Provider for the database that you are using.

• User name field: User name used to connect to the database. The user name configured in this field must match the user name configured in the database. This field accepts String expressions (tag only).

• Password field: Password used to connect to the database. The password configured in this field must match the password configured in the database. This field accepts String expressions (tag only).

  Note: The Connection string, User name, and Password boxes accept String expressions. However, tag values are not encrypted when they are sent between the project runtime server and connected thin clients. Therefore, to ensure that your database credentials cannot be intercepted or compromised, you can configure only server tags (tags that have Scope set to Server; for more information, see Tag scope) in these boxes. The tags will be evaluated on the server only, and no tag values will be sent between the server and client.

• Retry Interval field: If the project is unable to connect to the database for any reason, it retries automatically to connect to the database after the number of seconds configured in this field have passed.

• Advanced button: After pressing this button, you have access to customize some settings. For most projects, the default value of these settings do not need to be modified and should be kept.

  

  • Time Zone combo box:

    • Local Time + Time Difference: Save the local time on the computer, plus the difference (bias) between the local time zone and Coordinated Universal Time (UTC).

    • Local Time: Save the local time only with no bias. This is not recommended.

    • UTC: Save the UTC time only. This is the default, and it is strongly recommended for most situations.

  • Milliseconds combo box: You can configure how the milliseconds will be saved when saving the date in the database. Each database saves the date in different formats; for example, some databases do not support milliseconds in a Date field. The following options are available:

    • Default: Uses the format pre-defined for the current database. The databases previously tested are automatically configured with the most suitable option. When selecting Default, the settings pre-configured for the current database type are used. If you are using a database that has not been previously configured, the Default option attempts to save the milliseconds in a separate field.

      Tip: The default option for each database is configured in the StADOSvr.ini file, stored in the \Bin\DatabaseGateway sub-folder. See Studio Database Gateway for information about how to configure the StADOSvr.ini file.

    • Disable: Does not save the milliseconds at all when saving the date in the database.

    • Enable: Saves the milliseconds in the same field where the date is saved.

    • Separate Column: Saves the milliseconds in a separated column. In this case, the date is saved in one field (without the milliseconds precision) and the number of milliseconds is saved in a different column. This option is indicated where you want to save timestamps with the precision of milliseconds but the database that you are using does not support milliseconds for the Date fields.

  • Database Gateway: Enter the Host Name/IP Address where the Database Gateway will be running. The TCP Port number can also be specified, but if you are not using the default, you will have to configure the Database Gateway with the same TCP Port. See Studio Database Gateway for information about how to configure the advanced settings for the ADO Gateway.

  • Disable Primary Keys: Enable this option if you are connecting to a database that does not use primary keys (such as Microsoft Excel).

    Note: This option will not affect a table that already exists, either because it was already created in the database or because it was already created by the project. Recreate the table if you want to change this option.

    Note: This option can be used with databases that use primary keys. For example, a table in Microsoft SQL Server Management Studio automatically uses the "timestamp" and "timestamp_ms" primary keys. If Disable Primary Keys is enabled, these column will not be treated as primary keys and they will accept duplicate values.

  • Disable Delimiters: Select this troubleshooting option to disable the delimiters that are used to format communications with the database. Delimiters can cause problems when a Trend Control or Grid builds a query that includes aggregates such as Min and Max.

  • Disable SQL variables: Select this troubleshooting option to disable SQL variables, such as @Value1 and ?, that are often used in SQL statements and queries. Some specific database providers do not support these variables.

    Note: If you select this option, you might need to specify the language or culture that should be used to format values in SQL statements. For more information, see "Advanced Settings" in Studio Database Gateway.

  • Enable Secure Channel: Choose whether or not use secure mTLS protocols for this TCP/IP connection. To do this, select (enable) or deselect (disable) the Enable Secure Channel check box. This is part of a three-part process, and all three parts must be correctly configured. To configure the other two parts, see:

    • Secure Channel Communication

    • Enabling the TCP/IP Secure Channel option in the Studio Database Gateway

      Historian connections are configured in the Historian dialog, not here.

Table Pane

This area allows you to configure the settings of the Table where the data will be saved. All tasks can share the same database. However, each task (Alarm, Events, Trend worksheets) must be linked to its own Table. This field is not checked for invalid configurations on this field, so verify that the configuration is suitable for the database that you are using.

• Use default name checkbox: When this option is checked (default), data are saved and/or retrieved in the Table with the default name written in the Name field.

• Automatically create checkbox: When this option is checked (default), a table with the name written in the Name field is created automatically. If this option is not checked, the table is not created automatically. Therefore, it will not be able to save data in the database, unless you have configured a table with the name configured in the Name field manually in the database.

• Name: Specifies the name of the Table from the database where the history data will be saved. This field accepts String expressions (tag only).

  Tip: To specify a sheet in a Microsoft Excel spreadsheet file, use the syntax: [sheetname$]

• Refresh button: If the database configured is currently available, you can press the Refresh button to populate the Name combo-box with the name of the tables currently available in the database. In this way, you can select the table where the history data should be saved instead of writing the Table name manually in the Name field.

Run-Time Pane

This area allows you to set runtime values. The following fields are available:

• Status (output) checkbox: The tag in this field will receive one of the following values:

  Value	Description
  0	Disconnected from the database. The database is not available; your configuration is incorrect or it is an illegal operation.
  1	The database is connected successfully.
  2	The database is being synchronized.

• Reload (output): Specify a reload tag if you are using curly brackets in any of the configuration fields. When you want to reconnect to the database using the updated values on your tags, set the tag on this field to 1. After trying to perform an action in the database, the will be reset tag back to 0 when it is finished.

See also:

Configuring a Default Database for All Task History.