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

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.

Figure 1. 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.

--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.