Relational Database ODBC Datasource

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.

Figure 1. Create Datasource Wizard

On the Server Type page of the wizard, select "Relational Databases (ODBC)" from the drop down menu.

Figure 2. Create Datasource Wizard - Server Type

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;

Figure 3. Connection Settings

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]

Figure 4. Create Datasource Wizard - Query and Recurrence settings

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

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.

Figure 6. Data Table from last Query

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.

Figure 7. Database Connection Statistics

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

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.

Figure 8. ODBC Datasource - Diagnostics Data Table

Go to the Data Processing property compound and open the Row Script editor by clicking the …​. 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)
end

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.

Figure 9. Row Script - Variables under Datasource

Add the Variable objects to a History Grid Display to see the historized values.

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.

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 inmation.getvalue("./last") end)
		if ok then return res
		else return 0
		end
end

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__

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)

end

Wait for the next query cycle to finish then open the Data table property of the ODBC datasource under the Diagnostics property compound.