Skip to main content

i4connected Knowledgebase 5.6

The SQL Adapter

Abstract

The present article describes the particular settings required in order to configure an SQL Adapter. Check this out for more details!

The present article describes the particular settings required in order to configure an SQL Adapter. The SQL Adapter is capable of reading historical data.

The SQL Adapters can be configured by accessing the Devices tile from the Administrative area of i4connected.

The particular SQL settings required to configure an SQL Device, are available within the Add Device panel.

Note

Only users with the Manage adapters permission enabled can add and edit Adapters.

sql_adapter.jpg

The SQL Settings

By clicking on the SQL query name field, the Select SQL query panel is opened, allowing the user to select the desired SQL query settings or to add new ones.

Select_SQL_adapter.jpg

The Select SQL adapter

Adding the SQL query

By clicking the toolbar button Add in the Select SQL Query panel, opens the Add SQL Query panel.

sql_preprocess.jpg

The Add SQL query panel

In the Select SQL query panel, the following settings are required to be organized:

  • Name - The name of the SQL settings, that will be displayed in the configured device, under the SQL query name field.

  • Description - the user-friendly description of the SQL Adapter.

  • Connection type - the type of connection used for the SQL adapter.

    The user can select the Connection type choosing from the drop-down list options: MSSQL or ODBC.

    connection_type.jpg
  • Connection string - The connection string, providing information about a data source and the means of connecting to it.

  • Preprocess query parameters

    • Yes

    • No

  • SQL query - The SQL query that retrieves the Signal data.

    Before saving a new SQL Query, the user can click on the Test query (max. 10 results) button, to check the connection and the query.

    If the query is correctly set up, a maximum of 10 results will be returned and displayed in the table. The SQL Adapter table is defined by the following columns:

    • Timestamp

    • Value

    • Source

    • Signal

    If there are errors an error message will be displayed, providing the user with more information about the identified issue.

  • Poll interval (ms) - The time interval (in milliseconds) between subsequent polls. By default, the value is set to 60000 ms.

  • Select owner - the name of the Adapter owner.

    IoT_owner.jpg

    Adapter owner selector

    By clicking on the owner selector, the Select Users panel is opened allowing the user to choose the Adapter's owner.

    List_of_users.jpg

    Select Users panel

    Important

    When adding a new Adapter the creator user is by default set as Adapter's owner. However, the owner can be changed after the Adapter was saved, by all users having at least the Manage adapters permission enabled.

  • Time zone - The time zone used by the new SQL Adapter. By default, the Time Zone defined for the currently logged-in user is set.

  • Trace level - The level of logging used by the new SQL Adapter. Trace levels determine which events the trace provider generates. The Trace level value can be Trace, Debug, Info, Warn, Error, Fatal, or Off.

  • Enabled - If the setting is turned on, the Adapter is enabled and functional.