Relational Database (ODBC) Datasource

To create and use the ODBC Datasource, you should first have an available ODBC/SQL database to connect to and test against. In this example, a sample DemoData database is used. This can be recreated in your own ODBC database server by running an SQL command contained in the following zip file - DemoDataSQL.zip. The zipfile contains a regular SQL file that will run on for example Microsoft SQL Server and a MySQL example. A DSN (System DSN) named "DemoData" should be set up according to the ODBC/SQL database vendor’s instructions or alternatively a connection string to the database with the default database set to "DemoData". The username and password for the database is also needed to connect. A DSN can then be set up with the DemoData name.

Create the DemoData example items under your Node by importing the Inmation_Examples_Mass Config.json into a Mass Config tab in the System Config app. Also download the Inmation_Examples_ODBC_MassConfig.json which will be used later to create items to read and write data in/to the example database.

Select a Folder in the I/O Model and right-click. Select Admin  New to open the Create Object wizard.

In the left-hand panel, expand the External Interfaces section and select Datasource ODBC.

In the Common property compound,, enter a name for your Datasource and, optionally, an Object Description.

Click on the ODBC Datasource section in the left hand panel and enter a Name for this ODBC connection.

In the ODBC Connection compound, first enter the Datasource Name. This is equivalent to the DSN name (if you have a DSN set up). Alternatively a connection string of the following form can be entered directly:

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

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 [DemoData].[dbo].[Tags]

SELECT *, NOW() AS timestamp FROM DemoData.Tags;

Click OK to save the query.

Then, in the Scheduling property compound, change the Schedule Recurrence property to each 30 seconds (this will perform the database query every 30 seconds - we will use this initially just to check the connection).

Click Create to create the datasource in the I/O Model.

Select the newly created OBDC datasource and, in the Object Lens panel, open the ODBC Database > Diagnostics property compound. The Process State field indicates the time until the next data query call and any errors will be displayed in the Last Error field.

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.

Firstly, create a xVariable 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 syslib.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.