Working with the Lua ODBC Example

The ODBC example gets external data into AspenTech Inmation OT DataFabric from an ODBC data source, as well as writing data from objects in the system, to an ODBC data source. With the aid of the scripting engine, it is fairly easy to read/write data from/to any kind of SQL database.

This example covers:

Reading from a database
Writing to a database

Preparing the SQL Environment

The provided sample scripts work with a very simplistic SQL demo database, and requires a System DSN configured to access this database on the Node host. To configure a simple SQL environment follow these steps:

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 in the IO Model (under IO-Home folder) by importing the Inmation_Examples_Mass Config.json into a Mass Config tab in the System Config app and creating the items. Also download the Inmation_Examples_ODBC_MassConfig.json which will create items to read and write data in/to the example database.

If you do not have a SQL server available, the Microsoft SQLExpress server will work with this example and is free to download (we also recommend that you download the SQL Server Management Studio to help troubleshoot configuration issues). During setup, enable the mixed mode authentication and enter a password for the system administrator (sa) account. This is important for this example as the embedded Lua script uses this authentication to access the SQL database. To restore the DemoData.bak database, open the management studio and right-click on Databases in the left hand panel and select Restore Database. Select Source → Device → "…" and click Add to browse to the .bak file.

Database Read

The example contains a Generic Item called _Read which connects to the DemoData SQL database and executes a simple select statement.

local tagname = "PC4711"
local sqlcmd = "Select Description, EU, EU_Low, EU_High from Tags Where Name = '" .. tagname .. "'"

--Setup odbc environment (in this case locally within the script)
local driver = require"luasql.odbc"
local env = driver:odbc()
local conn, err = env:connect('DemoData','sa','DemoData!1')
if conn == nil then
	error(err)
end

--execute the SQL statement
local cursor, errorString = conn:execute(sqlcmd)
row = cursor:fetch ({}, "n")

-- close everything
cursor:close()
conn:close()
env:close()

return row[1] .." ("..tostring(row[3]).."-"..tostring(row[4]).." "..row[2]..")"

Note how the luasql.odbc library is included in the script in line 5 (part of the supported Luasql module). The select statement is executed in line 10, and the result fetched in line 11. The returned information is not written to any other item in this example, but instead returned to GenericItem’s own value.

The existing library inmation.Database is deprecated, and no longer supported. Any usage of this library should be changed to a direct implementation of Luasql.odbc instead.

Database Write

The Generic Item called Write in this example shows how to transfer data from AspenTech Inmation OT DataFabric to the SQL database, based on information read from SQL (what tag to write) and AspenTech Inmation OT DataFabric (what value to write). Like in the read example the script starts with setting up the luasql environment and executing the select statement.

On automatic installation of the example, the Write object may be in red state, since it immediately tried to write to one of the Demo Data objects ('PC4711') - before the generation of the Demo Data was finished. After a brief moment, the write will be repeated and the state of the Write object will turn green.

--Setup odbc environment (in this case locally within the script)
local driver = require"luasql.odbc"
local env = driver:odbc()
local conn, err = env:connect('DemoData','sa','DemoData!1')
if conn == nil then
	error(err)
end
local sqlcmds = {}

--execute the SQL statement
local cursor, errorString = conn:execute("Select ID, Name from Tags Where Name like '%4711'")

The select fetches multiple rows from the Tags table in the DemoDatabase. Then it iterates over the result and tries to find an equally named items in the DemoData example.

while row do
	--find the folder that has the real time processdata demo
	local pdatafldr = syslib.findobjects("ProcessData",0)[1]
	--find the data item matching the name from SQL tags table
	local tagitem = syslib.findobjects(pdatafldr:path() "/"   row[2], 0, false, true)[1]
	--read the value, quality, timestamp of the item
	local v = syslib.getvalue(tagitem:path())

To do so, it gets the ProcessData folder (line 13), that is part of the DemoData example, and hosts the demo items. With the combination of the parent folder path and the name from the SQL query, it gets the particular demo item (line 15) and finally reads its current value (line 17) into a local variable v.

-- prepare the insert statement
local year, month, day, hour, min, sec, msec = syslib.gettimeparts(syslib.currenttime())
local sqltime = math.floor(year) .. "-"
             .. math.floor(month) .. "-"
             .. math.floor(day) .. " "
             .. math.floor(hour) .. ":"
             .. math.floor(min) .. ":"
             .. math floor(sec)
local insertsql = "INSERT INTO [dbo].[Data] VALUES ("
                .. row[1]
                .. "," .. string.format("%.2f", v)
                .. ", 'Good'"
                .. ", '" .. sqltime
                .. "')"
-- store insert SQL cmds temporarily
table. insert(sqlcmds, insertsql)

The next step in the loop is preparing a SQL insert statement (line 26), that contains information read from SQL and the value read from the object. The statement is stored temporarily in a Lua table variable (line 33).

--execute the sql cords for insert
for k,v in pairs(sqlcmds) do
    conn:execute(v)
end

--clean up odbs environment
cursor:close()
conn: close()
env:close()

--return sqlcmds
return tostring(#sqlcmds) .. " SQL commands executed"

Finally, the temporarily stored insert commands are executed (line 39), then the ODBC environment is closed and the number of commands executed is returned to GenericItem’s own value. The Write item is executed once per minute by default, and the results can also be checked in the SQL database.