Databases

Add Database

With the “Databases” navigation node selected, click on the “Add” button to add a database.

This will open a dialog to enter database connection parameters.

Note

For SQLite, database connection parameters are automatically filled in and the database is added without showing the dialog first. The SQLite Connector does not support username or password credentials.

Database Settings

Database configuration options are:

• Name

  The name of the database as it will appear elsewhere in the application.

• Connection String

  Enter a connection string appropriate for the relevant database connector. See notes below.

• Identifiers

  This setting determines how identifiers are constructed for use in database queries; such identifiers may be table and tag names configured within topics of the database connector itself or tag names sent to the database connector by other connectors.

  • Substitute invalid characters with underscore

    In this case any character in a tag name that is not alphanumeric is replaced by the underscore _ character. For example, a tag name containing a space as in Motor RPM is replaced with identifier Motor_RPM in database queries. This setting is suitable if a database is only written to or if database tables columns read from the database already have alphanumeric names. If names have other characters then select one of the other options.

  • Enclose in double quotation marks

    Table and column names are enclosed in double quotation marks for database queries. This setting retains the original tag name. For example, a tag name Motor RPM is replaced with "Motor RPM" for database queries. Most databases support quoted identifiers, although this option may not be enabled by default.

  • Enclose in square brackets

    Tag names are enclosed in square brackets. This setting retains the original tag name. For example, a tag name Motor RPM is replaced with [Motor RPM] for database queries. Microsoft SQL Server and Microsoft Access support this syntax by default.

  • Enclose in backtick characters

    Tag names are enclosed in backtick characters. This setting retains the original tag name. For example, a tag name Motor RPM is replaced with `Motor RPM` for database queries. MySQL supports this syntax by default.

  Tip

  To use different identifier settings with the same database, add another Dataristix database pointing to the same underlying data source.

• Create a ‘Generic’ topic

  ‘Generic’ topics are covered in the next chapter.

• Using integrated security

  With this setting no further database login information is required or provided when the Database Connector service attempts to connect to the database. Note that, by default, the Database Connector service runs under the Local Service account and the database must either be configured to grant access to NT_AUTHORITY\LocalService or the Database Connector service needs to be configured to run as a user with sufficient access rights.

• Prompt for credentials

  In this case the application will prompt the user for credentials when a task is started that needs to connect to the database.

• Save credentials for future use.

  You can allow the Database Connector to store credentials for future use. Credentials are stored in encrypted form and are retrieved whenever a task requires a connection to the database.

Use the “Test Connection” button to verify that a connection can be made before clicking “Add” to add the database.

You can change settings later in the database settings panel that is shown when you select the database in the navigation panel.

Note

• ODBC Connection Strings

  The connection string can either be a DSN (Data Source Name) configured for the system in the form “DSN=DataSourceName” or any other valid connection string for the target database.

  Use the Windows system’s ODBC Data Source Administrator tool to preconfigure ODBC data sources for use of “DSN” data source references, or define the connection string in the form, for example, “Driver={SQL Server}; Server=myhost\SQLEXRESS; Database=mydatabase;”. Adjust settings for different database drivers, database engines, and databases.

  Note that it is the ODBC Connector background service that needs access to the database and the data source should therefore be a System data source. Alternatively, configure the Rensen ODBC Connector Service to run as a user with sufficient rights.

  Using the latest available ODBC drivers is recommended. It is worthwhile checking whether a newer version of your database ODBC driver is available because preinstalled ODBC drivers may be outdated.

• SQL Server Connection Strings

  Enter a connection string in the form (for example):

  `Server=localhost;Database=IntegrationTest;TrustServerCertificate=Yes;TrustedConnection=True;`
  

• Oracle Connection Strings

  Enter a connection string in the form (for example):

  `Data Source=//localhost:1521;User ID=userid;Password=password;`
  

• MySQL Connection Strings

  Enter a connection string in the form (for example):

  `server=localhost;Database=mysql;uid=userid;pwd=password;`
  

• PostgreSQL Connection Strings

  Enter a connection string in the form (for example):

  `Host=localhost;Username=postgres;password=password;`
  

• SQLite Connection Strings

  The connection string for SQLite is automatically generated from the database name, in the form:

  DataSource=<databasename>.db
  

  The database storage folder path itself is omitted. The default storage folder for databases is:

  C:\ProgramData\Rensen\Dataristix\modules\SQLite Connector\Databases
  

  To change to a different folder, edit the “appsettings.json” file located in folder

  C:\Program Files\Rensen\Dataristix\Services\modules\SQLite Connector\service
  

  and add or modify an entry “DatabaseFolder” within the file, i.e.:

  ...
  DatabaseFolder="C:\temp"
  ...
  

Advanced Settings

Once the database is added, click on the database to view its properties. Expand the “Advanced” panel at the bottom of the properties panel to edit advanced settings.

Advanced settings are:

• Auto-create table prefix

  The prefix is used for topics that are configured to automatically create matching database tables. The table name will be derived from the topic name and the configured prefix. For example, for a topic named “Topic1” and a table prefix “U_” the derived table name will be “U_Topic1” . This helps distinguish automatically created tables and may also prevent altering other, already existing tables. You may enter a different table prefix or a blank table prefix if no prefix is required.

• Timestamp time zone

  Timestamps are date and time fields that are automatically added when storing samples; these include the “sample_datetime” field or value time stamp fields if a topic is configured to store individual value timestamps. By default, timestamps are stored as UTC (recommended). You may choose a different time zone if required.

• Prefer subsecond timestamp precision

  By default, timestamps are stored with sub-second precision where available and, for automatically created tables, column data types are chosen that are capable of storing such timestamps. Unticking this option has the effect that timestamps are truncated to full seconds and, when automatically creating tables, timestamp data types of lesser precision may be chosen capable of storing timestamps in second accuracy only.

Tip

To use different advanced settings with the same database, add another Dataristix database pointing to the same underlying data source.

Remove Database

If a database is no longer needed then click on “Databases” in the main navigation panel and select the database in the content panel; click on the “Remove” button to remove the database.

Warning

Note that all topics configured for the database will be also be removed. This affects all tasks using these topics.