Skip to main content
Version: v3

ODBC

Send SQL queries to a local ODBC driver or to a remote OIBus Agent that can manage ODBC queries on the same machine as the server.

The OIBus agent can be installed separately, as specified in its documentation.

This connector proves valuable when retrieving data from IP21 ODBC interfaces or other ODBC-compatible applications.

Specific settings​

  • Use remote agent: If set to No, ensure that an ODBC driver is installed on the machine where OIBus is deployed. Alternatively, you can opt to install an OIBus agent on a remote machine equipped with the necessary drivers.
  • Remote agent URL: Specify the URL of the remote OIBus agent, e.g., http://ip-address-or-host:2224.
  • Retry interval: Time to wait before retrying connection.
  • Request timeout: Determine the timeout duration for each query.
  • Connection timeout: Timeout setting for establishing the connection.
  • Connection string: The ODBC connection string.
  • Password: The authentication password, securely stored within OIBus configuration. If directly included in the connection string, the password will be stored in plain text. When a password is used, OIBus appends PWD=<password> to the end of the connection string.

Please ensure that the ODBC connection string adheres to the specified driver format, and it is imperative to have the driver specified in the connection string correctly installed.

ODBC connection string example

Driver={driver name};SERVER=localhost,10014;TrustServerCertificate=yes;Database=test;UID=oibus;PWD=<secret>

The driver is either the name of the driver (Windows) or the path of the driver file (unix like systems). The port is optional, and can be replaced by PORT=10014 (be sure to replace the comma , by a semicolon ;).

Driver Installation​

On Windows, you can conveniently access the ODBC driver management tool, where you can specify the driver's name in the connection string, such as MySQL ODBC 3.51 driver or SQL Server.

On UNIX-like systems, you should first install the driver on your machine and then specify the driver's path in the connection string, like /opt/lib/libmsodbcsql.18.dylib, for example.

ODBC diver on MacOS

Install unixodbc: brew install unixodbc

Check if the installation was successful and list the ODBC config files: odbcinst -j

Check the installed drivers: cat /opt/homebrew/etc/odbcinst.ini

Using OIBus with IP21​

ODBC connections often suffer from latency issues, particularly during large historian queries, such as those involving IP21. This can place a heavy load on both the network and the server.

To mitigate these challenges, we recommend installing the OIBus Agent as a service on the same machine as IP21. OIBus can then transmit queries to its agent using the HTTP protocol, and the agent can communicate directly with IP21 using ODBC, eliminating network latency.

Of course, it's essential to ensure that the appropriate ODBC driver is installed on the agent machine and specified correctly in the connection string.

IP21 ODBC driver

To establish a connection to IP21 via ODBC, you will need the AspenTech SQLplus driver installed on your machine. Please ensure that this driver is correctly installed.

ODBC Access

Authentication can be handled either through the connection string or locally. When choosing local authentication, it's important to ensure that the OIBus agent runs with the appropriate permissions. You may need to run the service as a different user, which can be configured through the service management window.

The following connection string works with a basic IP21 installation: Driver={AspenTech SQLplus};HOST=<host>;PORT=10014"

Item settings​

In the South connector, each item can be configured to be queried according to the chosen scan mode. Multiple queries to the same database can be configured within the same South connector. OIBus will execute the queries one after another, prepare the output file, and then send it to North connectors.

Query​

The query field in the South connector accepts SQL syntax and can utilize several internal variables. These variables serve various purposes, including enhancing data stream resilience in the event of a connection failure and breaking down large intervals into smaller chunks, which helps reduce the load on the server and network. For more information, refer to the big queries section.

Query variables​

In OIBus, you can utilize the following internal variables that will be interpreted by the system:

  • @StartTime: Initially, the @StartTime variable is set to the date of the first execution of the query. When results are retrieved from the database, the @StartTime value is updated to the most recent timestamp among those results in the field used as a reference (refer to the datetime fields section).
  • @EndTime: The @EndTime variable is set to either the current time (now()) or the end of the sub-interval if a query is split.
SQL query with @StartTime and @EndTime
SELECT data_name AS dataName, value, timestamp FROM table WHERE timestamp > @StartTime AND timestamp < @EndTime

Datetime fields​

In the South connector, you can specify an array of fields that are of datetime type. Each row indicate the format in which OIBus will parse this field in order to convert it to an internal UTC date. Here are the details for configuring datetime fields:

  • Field name: The field name in the SELECT section of the query.
  • Reference field: Use this field as a reference for the next @StartTime value (refer to query variables).
  • Type: The type of data in the result.
  • Timezone (for string, Date, DateTime, DateTime2, SmallDateTime types): The timezone of the datetime stored in the database.
  • Format (for string only): The string format of the datetime stored in the database.
  • Locale (for string only): The locale to use when the format contains locale-specific strings (such as MMM format for months).

OIBus internally uses these dates in ISO UTC format, and the output datetime format can be set in the serialization section. All datetime fields are converted using the same serialization settings.

Conversion in SQL query

If the timestamp field is utilized as a reference of type string, formatted as yyyy-MM-dd HH:mm:ss, the @StartTime and @EndTime will be injected into the query as yyyy-MM-dd HH:mm:ss format strings.

In the subsequent query, the datetime field (retrieved from the database) is a DateTime object converted into a string (timestamp). OIBus will interpret the timestamp (string) field from the query as a reference field. However, injecting @StartTime and @EndTime as string variables in the same format may lead to unexpected behavior due to the inconsistency between the injected @StartTime and @EndTime variables (string type) and the datetime field (formatted as DateTime from the database).

Bad SQL query with @StartTime and @EndTime and convert
SELECT data_name AS dataName, value, convert(datetime, DATETIME) AS timestamp FROM table
WHERE datetime > @StartTime AND datetime < @EndTime

In the following case, the uniformity in formats guarantees proper functionality.

Correct SQL query with @StartTime and @EndTime
SELECT data_name AS dataName, value, convert(datetime, DATETIME) AS timestamp FROM table
WHERE convert(datetime, DATETIME) > @StartTime AND convert(datetime, DATETIME) < @EndTime

CSV Serialization​

OIBus offers the option to serialize retrieved data into CSV files, and you can customize the serialization process with the following settings:

  • Filename: The name of the file where the result will be stored. You can use several internal variables like @ConnectorName (the name of the connector) and @CurrentDate (the current date in yyyy_MM_dd_HH_mm_ss_SSS format - it is uncorrelated to the datetime format of the serialization, used for datetime fields).
  • Delimiter: The delimiter to use in the CSV.
  • Compression: Choose whether to compress the file with gzip or not.
  • Output datetime format: Specify the format of the datetime in the CSV. OIBus will only convert the datetime fields specified. The @CurrentDate variable used in the output filename won't be affected.
  • Output timezone: The timezone to use for storing the datetime.

Splitting large queries​

In situations where a query may impose a significant load on the server, especially when a large time interval is requested and the @StartTime and @EndTime query variables are utilized, you can split the query into several sub-queries with smaller intervals. This can be achieved by configuring the Max read interval field in the history settings.