Working with the Lua ODBC Example

The ODBC example gets external data into system:inmation 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 Core host. To configure a simple SQL environment follow these steps:

  1. Create the sample sql database by either restoring the DemoData.bak or running DemoData.sql which needs to be unzipped from DemoDataSQL.zip from the resources page at a SQL Server instance of your choice (see note below for details of setting up an example SQL server).

  2. Create a System DSN called 'DemoData' pointing to the DemoData SQL database (can be found in Administrative tools in the control panel). Double click on ODBC Data Source Administrator (32/64bit, depending on your environment), select the System DSN tab and click Add. Choose an appropriate driver (depending on the SQL Server you are using - ODBC Driver 11 for SQL Server works for MS SQL Express)

  3. Give the System DSN a name in the dialog and connect it to the SQL Server you are using (it may not appear automatically in the drop-down menu). If using MS SQL Express this will be <hostname>\SQLEXPRESS.

  4. Set up the SQL server authentication using same authentication details as during the SQL server set up (see note below for details of setting up an example SQL server) and click Next.

  5. Change the default database to DemoData, click Next and then Finish. Test Data source and then click OK until all dialog windows are closed.

If you do not have a SQL server available, you can download the 2014 Express version here. Be sure to download the Express with Tools download package as this contains the SQL Server Management Studio. During setup, enable the mixed mode authentication and enter DemoData!1 as the 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.

Automatic Example Setup

With the SQL Environment configured, you can then have the ODBC example automatically set up in system:inmation, by running the following Lua script in the Console Display. All objects which are relevant for this example will be created in the 'Examples > LUA > HTTP Access' subtree underneath your Core object. The Demo Data, which are a prerequisite for this example, will be generated in 'Examples > Demo Data'

local exm = require('esi-examples')
exm:SETUP({{"demodata", "iomodel"}, {"odbc"}})
Lua ODBC Access Objects
Figure 1. Lua ODBC Access Objects

Database Read

The example contains a GenericItem 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.

Result of Read as current value
Figure 2. Result of Read as current value
The existing library inmation.Database is deprecated, and no longer supported by. Any usage of this library should be changed to a direct implementation of Luasql.odbc instead.

Database Write

The GenericItem called Write in this example shows how to transfer data from system:inmation to the SQL database, based on information read from SQL (what tag to write) and system:inmation (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.