+- LogicMachine Forum (https://forum.logicmachine.net)
+-- Forum: LogicMachine eco-system (https://forum.logicmachine.net/forumdisplay.php?fid=1)
+--- Forum: Scripting (https://forum.logicmachine.net/forumdisplay.php?fid=8)
+--- Thread: Logger All Datagrams KNX BUS to mysql (/showthread.php?tid=2620)
Logger All Datagrams KNX BUS to mysql - Hyxion14 - 29.04.2020
Hello I have a project in which I need to save in a bbdd mysql all the states of all KNX groups in real time.
In other words, I need to listen to the KNX BUS and send all the datagrams that are written to the BUS to a mysql DB.
Can LM do this?
Can somebody help me?.
Thank you.
RE: Logger All Datagrams KNX BUS to mysql - admin - 30.04.2020
See this thread on a basic example of bus monitoring. It can be easily extended to send data to mysql or any other database:
https://forum.logicmachine.net/showthread.php?tid=273
RE: Logger All Datagrams KNX BUS to mysql - Hyxion14 - 30.04.2020
Maybe an idea would be, to mark all the objects with "LOG" and every hour, to synchronize the objects of the LM bbdd with the DB of mysql?
RE: Logger All Datagrams KNX BUS to mysql - admin - 30.04.2020
You can modify CSV example to export to mysql: https://openrb.com/example-export-last-hour-csv-object-log-file-to-external-ftp-server-from-lm2/
RE: Logger All Datagrams KNX BUS to mysql - gjniewenhuijse - 01.05.2020
This is my code, maybe not perfect but it works
Code:
--[[
* table:
[datalen]
* number: 4
[datahex]
* string: 453FE000
[data]
* number: 3070
[srcraw]
* number: 8448
[dstraw]
* number: 17930
[type]
* string: groupwrite
[src]
* string: 2.1.0
[dst]
* string: 8/6/10
--]]
if not client then
require('genohm-scada.eibdgm')
objects = grp.all()
datatypes = {}
for _, object in ipairs(objects) do
datatypes[ object.id ] = {object.name, object.datatype}
end
function writemysql(I_datalen,I_datahex,I_data,I_srcraw,I_dstraw,I_type,I_src,I_dst,I_name)
require('luasql.mysql')
env = luasql.mysql()
dbcon = env:connect('xxxxxxxxx', 'xxxxxxx', 'xxxxxxxx', '192.168.12.25', '3306')
if dbcon then
if I_type == 'groupread' then
cursor = dbcon:execute( 'insert into datalog (datetime,datalen,srcraw,dstraw,type,src,dst,dstname) values (now(),'..I_datalen..','..I_srcraw..','..I_dstraw..',"'..I_type..'","'..I_src..'","'..I_dst..'","'..I_name..'") ' )
else
cursor = dbcon:execute( 'insert into datalog (datetime,datalen,datahex,data,srcraw,dstraw,type,src,dst,dstname) values (now(),'..I_datalen..',"'..I_datahex..'",'..tostring(I_data)..','..I_srcraw..','..I_dstraw..',"'..I_type..'","'..I_src..'","'..I_dst..'","'..I_name..'") ' )
end
--log(cursor)
dbcon:close()
env:close()
end
end
function writehandler(event)
if datatypes[ event.dstraw ] then
name = datatypes[ event.dstraw ][1]
dpt = datatypes[ event.dstraw ][2]
else
name = ''
end
if dpt then
event.data = knxdatatype.decode(event.datahex, dpt)
else
event.data = nil
end
--log(event)
writemysql(event.datalen,event.datahex,event.data,event.srcraw,event.dstraw,event.type,event.src,event.dst,name)
end
function readhandler(event)
if datatypes[ event.dstraw ] then
name = datatypes[ event.dstraw ][1]
else
name = ''
end
--log(event)
writemysql(event.datalen,event.datahex,'',event.srcraw,event.dstraw,event.type,event.src,event.dst,name)
end
-- knx connection
client = eibdgm:new({ timeout = 1 })
client:sethandler('groupwrite', writehandler)
client:sethandler('groupresponse', writehandler)
client:sethandler('groupread', readhandler)
end
-- handle knx
client:step()