The example configuration ODBC Example.mpp is described in this section. The configuration includes simulation tags with the HDA mode switched on as well as the separate node for operation via ODBC. Basing on this example, you can develop own script adapted for your configuration and DB.
We will poll 3 tags and write them into a MS SQL table of the following structure:
Also, the ID is declared as the primary key, with the increment enabled.
Name of table - ArchiveValues.
Below is the whole code of the example.
local host = "MSSQLConnect"
local login =""
local password = ""
local env;
local CONN=nil;
local PathToDevice="Simulator.script"; -- path to device
local TagTable={}; --table of name tags
local TimeTable={}; --table of time last time HDA-tags values
function WriteToBase(NameTag,LastTime)
if CONN==nil then
return true,nil;
end
Num,Archive,LastTime=server.ReadHDAFromTag(NameTag,LastTime,false); --read archive value
if Num==0 then
return true,nil;
end;
Values=Archive[1];
Times=Archive[3];
local SQLQuery="INSERT INTO ArchiveValues VALUES "; --header of query
for i=1,table.maxn(Values),1 do
SQLQuery=SQLQuery..string.format("('%s','%s','%s')",NameTag,Values[i],Times[i]); --concating and formating string query
if i~=table.maxn(Values) then
SQLQuery=SQLQuery..", ";
end;
end;
--server.Message("SQLQuery=",SQLQuery); --summary SQL query
res,s = odbc.conn_execute(CONN,SQLQuery); --write values in the table
if res==nil then --error in request
odbc.conn_close(CONN); --close connection
odbc.env_close(env) --close ODBC object
CONN=nil;
server.Message("Error=",s);
return true,nil;
end;
return false,LastTime;
end;
-- initialize
function OnInit()
local CountTag=server.GetCountTags(PathToDevice);
for i=0,CountTag-1,1 do
local err,Tab=server.GetAttributeTagByNumber(i,PathToDevice); --get attribute tag from target device
if err==false and Tab[11]==true then --not error and this is tag is HDA
local Name=Tab[2]; --full name of tag
table.insert(TagTable,Name); --adding to table tags
table.insert(TimeTable,nil); --write nil value to table of times
end
end
end
-- deinitialize
function OnClose()
end
-- handling
function OnBeforeReading()
if CONN==nil then
env = odbc.env_create(); --create ODBC object
server.Message("Connection attempt");
CONN,s = odbc.env_connect(env,host,login,password); --connecting
if CONN==nil then
server.Message("Error=",s); -- no connected
return;
end;
end;
for i=1,table.maxn(TagTable),1 do
local err,LastTime=WriteToBase(TagTable[i],TimeTable[i]) --run function
if err==false then
TimeTable[i]=LastTime; --save last time value
end;
end
The header of the script specifies the database connection parameters - ODBC connector name, login and password. If necessary, these parameters can be set as additional device properties. The header also sets the path to the device with the tags to be exported to the database.
The OnInit section retrieves the list of HDA tags of the target device. You can use the server.GetAttributeTagByNumber function, where you can pass the path to the desired node or device as the second parameter. To get the number of tags you can use the server.GetCountTags function, which also passes the path to the desired device as an argument. In the loop through the tags of the device, check that the tag was received successfully (no error flag) and that the tag is an HDA tag (the 11th element of the received argument table). The full name of the tag is added to the tag table, and to the time table we write the value nil - using the values of this table, we will specify from which moment to sample from the HDA archive.
function OnInit()
local CountTag=server.GetCountTags(PathToDevice);
for i=0,CountTag-1,1 do
local err,Tab=server.GetAttributeTagByNumber(i,PathToDevice); --get attribute tag from target device
if err==false and Tab[11]==true then --not error and this is tag is HDA
local Name=Tab[2]; --full name of tag
table.insert(TagTable,Name); --adding to table tags
table.insert(TimeTable,nil); --write nil value to table of times
end
end
end
In the OnBeforeReading section, the presence of the ODBC object is checked and if its value is nil, an ODBC object is created using the odbc.env_connect function. Then an attempt is made to establish a connection (if it was not established earlier) using the odbc.env_connect function. If an error occurs, the script exits. Then all values of the TagTable table are looped through and for each tag - with its name and timestamp of the last value read - the user WriteToBase function is called.
If the function is invoked successfully, a timestamp of the last-time value is saved to the table of timestamps.
function OnBeforeReading()
if CONN==nil then
server.Message("Connection attempt");
CONN,s = odbc.env_connect(env,host,login,password); --connecting
if CONN==nil then
server.Message("Error=",s); -- not connected
return;
end;
end;
for i=1,table.maxn(TagTable),1 do
local err,LastTime=WriteToBase(TagTable[i],TimeTable[i]) --run function
if err==false then
TimeTable[i]=LastTime; --save last-time value
end;
end
Main operations with DB are performed in the function WriteToBase. As an argument, a tag name and a timestamp of a last-time tag value is passed to the function. An ODBC connection variable, CONN, is global and available from all functions of the script.
The function server.ReadHDAFromTag reads out a tag archive. As a result, variables are formed: number of records, an archive table, and a timestamp of the last-time value. If there are new tag values, (the variable Num is checked), 2 tables are created: Values (values) and Times (timestamps). (A table of quality tags is not used in this example). Next, an SQL query is formed. In order to insert several values to the table using a single query, the following query syntax is used:
INSERT INTO ArchiveValues VALUES (Value1,Value2),(Value2,Value3),....(ValueN,ValueN)
First, the query header initializes the variable SQLQuery. Next, all table values are searched. The function string.format concatenates 3 elements to a single string (a tag name, a value and a timestamp). The elements are separated by commas and enclosed in parenthesis. If a table element is not a last one, a comma is added. To view a final SQL query to uncomment a line server.Message("SQLQuery=",SQLQuery);
If a query is formed, it is executed using the function odbc.conn_execute. If an error occurs when executing a query, the connection is closed, and an error code is returned. Otherwise, the function returns that it is completed successfully; a timestamp of the last-time value is returned as well.
function WriteToBase(NameTag,LastTime)
Num,Archive,LastTime=server.ReadHDAFromTag(NameTag,LastTime,false); --read archive value
if Num==0 then
return true,nil;
end;
Values=Archive[1];
Times=Archive[3];
local SQLQuery="INSERT INTO ArchiveValues VALUES "; --header of query
for i=1,table.maxn(Values),1 do
SQLQuery=SQLQuery..string.format("('%s','%s','%s')",NameTag,Values[i],Times[i]); --concatenate and format string query
if i~=table.maxn(Values) then
SQLQuery=SQLQuery..", ";
end;
end;
--server.Message("SQLQuery=",SQLQuery); --final SQL query
res,s = odbc.conn_execute(CONN,SQLQuery); --write values to the table
if res==nil then --error in request
odbc.conn_close(CONN); --close connection
CONN=nil;
server.Message("Error=",s);
return true,nil;
end;
return false,LastTime;
end;
A result of configuration operation in the run-time mode is shown below:
