Relational Database ODBC Datasource

This section will show you how to create and configure a Relational Database ODBC Datasource. The ODBC Datasource is a type of Datasource that enables you to connect to an SQL database and schedule periodic SQL queries. The ODBC datasource can use Lua scripting to perform data processing tasks with returned data, including creating objects in the I/O Model.

To create and use the ODBC Datasource, you should first have installed a Connector under the Core and should have an available ODBC/SQL database to connect to and test against. In this example we use the Demo Data objects and the ODBC database example as an example of an ODBC database but the same principles can be used to connect to your own ODBC/SQL database.

Creating a Relational Database ODBC Datasource

  1. Select a Connector in the I/O Model and right-click. Select Admin  New  External Interfaces  Datasource from the context menu. In the Create Object wizard, enter a name for your Datasource and, optionally, an Object Description. Click Next.

    Create Datasource Wizard
    Figure 1. Create Datasource Wizard
  2. On the Server Type page of the wizard, select "Relational Databases (ODBC)" from the drop down menu.

    Create Datasource Wizard - Server Type
    Figure 2. Create Datasource Wizard - Server Type
  3. Enter a name for the ODBC Connection then under the ODBC Settings, first enter the Datasource Name. This is equivalent to the DSN name (if you have a DSN set up) or a connection string can be entered directly of the form:

    Driver={SQL Server Native Client 11.0}; Server=someserver;Uid=someuser;Pwd=somepassword;

    In this example we will just use the DSN that was set up in the ODBC database example. We must also enter a valid username and password for the connection.

    Connection Settings
    Figure 3. Connection Settings
  4. The Keep Open checkbox can be ticked if you wish to keep the database connection open between calls, for this example we will leave it unchecked. In the Query Execution settings, click the Query …​ button to open an editor to enter the following SQL query:

    SELECT *,GETDATE() [timestamp] FROM [InmationDataDemo].[dbo].[Tags]

    This returns all columns from the InmationDataDemo Tags table and uses the SQL GETDATE() function to get values with the current system timestamp. Click OK to save the query then in the wizard, change the Schedule Recurrence to every 30 seconds (this will perform the database query every 30 seconds - we will use this initially just to check the connection).

    Create Datasource Wizard - Query and Recurrence settings
    Figure 4. Create Datasource Wizard - Query and Recurrence settings
  5. Click Create to create the datasource in the I/O Model and then select the newly created OBDC datasource and open the Server Type Diagnostics property compound in the Object Properties panel. The Process State field indicates the time until the next data query call and any errors will be displayed in the Last Error field.

    Figure 5. Diagnostics
    The Datasource object will only display 2 green lights in the I/O model when a successful connection and first query is made. If the Datasource is still in a neutral communication state (bottom light remains grey) after repeated calls then you should check your connection settings for errors.
  6. Click on the Data button to open a table of data from the last query. The data retrieved should correspond to the query entered in the Query property. This data table can be exported in different file formats if you click on the export button.

    Data Table from last Query
    Figure 6. Data Table from last Query
  7. To view statistics for the last database connection, right-click on the Statistics property and select "JSON Viewer" form the context menu. This will open the JSON containing all the information about the current (or last) connection to the database.

    Database Connection Statistics
    Figure 7. Database Connection Statistics

Adding Data Processing Lua Scripts

Lua Scripts can be used to process incoming ODBC data either on a line-by-line basis or for a complete table at once.

The ODBC Datasource is able to process the data retrieved from the database by attaching Lua scripts to either do line by line processing or to process the whole data at once. In both examples, the data returned from the database will be used to create objects under the datasource and historize the data in the time series repository.

Preparing the Examples

  1. Firstly we will change the SQL Query field to make it so it retrieves time series data from the 'Data' table. Click the Query …​ button in the Object Properties panel of the ODBC Datasource to open the SQL Query Editor and enter the following query:

    SELECT TOP 3 T.Name, D.Value, D.Quality, D.timestamp
    FROM Tags AS T
    JOIN Data AS D ON T.ID = D.TagID
    ORDER BY D.timestamp DESC

    This query retrieves the names of the Tags from the 'Tags' table (there are 3 tags) then does a JOIN with the 'Data' table to return the last VQT for each tag. CLick Ok to commit the query.

  2. Once the next query cycle has completed, check the Diagnostics section to see if the query was successful (if successful the Datasource should show 2 green light in the I/O Model, check the Last Error field to get more details of any errors returned by the SQL Server). Then open up the Data table to see the result of the last query and to check that the right data is returned.

    ODBC Datasource - Diagnostics Data Table
    Figure 8. ODBC Datasource - Diagnostics Data Table

Line By Line Data Processing

Lua sripts hosted in the Row Script property process each line which is returned for the data query individually.

  1. Go to the Data Processing property compound and open the Row Script editor by clicking the …​ icon. The Row Script defines the Lua script that will be executed on every row in the returned data table. The function has two arguments "data" which is the row from the returned table and "index" which is the row number. For this script execution, we will create Variable objects below the Datasource object corresponding to the Tag names in the database. The returned VQT will then be written to the variable, and historized. To make the object creation (and subsequent upserting) easier we will use an internal ´esi-variables´ library to create the Variable and automatically configure it to historize data. To do this, enter the following script into the Row Script editor and click OK to confirm.

    local VAR = require'esi-variables'
    return function(index, data)
    	VAR:SETVARIABLE(data.NAME, data.Value, data.Quality, data.timestamp)
  2. When the next query is made after 30 seconds, the script will be executed on each row of the returned data table. The Variable objects wil be created underneath the Datasource in the I/O Model.

    Row Script - Variables under Datasource
    Figure 9. Row Script - Variables under Datasource
  3. Add the Variable objects to a History Grid Display to see the historized values.

Table Processing

As an alternative to line-by-line processing done by the script hosted in the Row Script property, the script in the Final Script property processes the complete returned data table at once.

The maximum number of rows returned for the query is limited by the value configured in the Top Rows property in the Diagnostics compound of the ODBC Datasource object’s properties. Rows beyond this limit are ignored.
  1. In the Data Processing property compound, open the Final Script editor by clicking the …​.

  2. The Final Script defines the Lua script that will be executed once, on the complete returned data table. The only argument for this function is "data" - the whole table which is returned for the query. Making use of the ´esi-variables´ library again, the following simple example adds the complete query result as a JSON string in a Variable object underneath the Datasource object. Enter this script into the Final Script editor and click OK to confirm.

    local VAR = require'esi-variables'
    return function(data)
    	VAR:SET("Final/data", data)
  3. Select Final / Data underneath the ODBC Datasource object and right-click onto the Faceplate in the Property Panel.

    The Query Result in the Faceplate
    Figure 10. The Query Result in the Faceplate
  4. From the context menu select the JSON Viewer.

  5. In the JSON Viewer, in the left hand column, select Record 1, then click on the {} icon in the toolbar to show the whole JSON containing the query result.

    The Query Result in the JSON Viewer
    Figure 11. The Query Result in the JSON Viewer

Dynamic Variables

When specifying the SQL query it is possible to use dynamic variables in the query that can be defined in the Dynamic Variables table property under the Query Execution property compound. The dynamic variable is accompanied by a script function that will retrieve a value for the variable at runtime.

Dynamic variables must be decorated with a double underscore at either side of the variable name. For example: *__MYVAR__*

The __STARTTIME__, __STARTTIMELOCAL__, __ENDTIME__ and __ENDTIMELOCAL__ dynamic variables are already available to be used without needing to be added to the Dynamic Variables table.

To demonstrate the use of dynamic variables, we will use the example where the user wishes to query 10 rows of the database for each cycle. A dynamic variable will be used to retrieve the last processed row for the cycle (which will be stored in a Variable object) so the next query will begin on the next row from where the last cycle finished.

  1. Firstly, create a Variable object) underneath the ODBC Datasource in the I/O model named "last" (you can choose to historize the values if you wish). This Variable will be used to write the row ID number for each cycle.

  2. Open the Dynamic Variables table property for the ODBC Datasource and enter __last__ in the Variable column (remember to use double underscores). In the Script Function column, enter the following script:

    return function()
    local ok,
    	res = pcall(function() return syslib.getvalue("./last") end)
    		if ok then return res
    		else return 0

    This script will get the value from the Variable created in the first step. Click OK to save the dynamic variables table.

    Dynamic Variable Table
  3. Now open the Query property and enter the query shown below and click OK. The query utilizes the __last__ dynamic variable to pick the next set of rows for the next cycle.

    SELECT TOP 5 * FROM Data WHERE Data.ID > __last__
    SQL query containing dynamic variable
  4. Now open the Script property of the OBDC Datasource under the Data Processing property compound and enter the Lua script below. This takes the ID of the last returned row of the SQL query and sets the value on the Variable object created in the first step. Click OK to save the script.

    local VAR = require"esi-variables"
    return function(index, data)
    		local last = tonumber(data.ID)
    		VAR:SETVARIABLE("last", last)
  5. Wait for the next query cycle to finish then open the Data table property of the ODBC datasource under the Diagnostics property compound.

    Returned Data - Dynamic variable

    As you refresh the table after each cycle, the row IDs will update to show the results of the last query following on from the rows queried in the cycle before.