(20.11.2019, 11:25)Erwin van der Zwart Wrote: Hi,
Here is a script i created a few years ago that checks for tables and if not exist it creates them, maybe this can be helpful for you (:
Code:-- Check if MySQL connection is loaded and available otherwise perform DB connection loading if not env or not dbcon then -- Setup MySQL connection require('luasql.mysql') env = luasql.mysql() -- Set database information and credentials, make sure remote access to MySQL server is enabled (see notes on bottom of this script) DB_NAME = 'WiserforKNX' USERNAME = 'root' PASSWORD = 'MyPassword' MYSQL_SERVER_IP = 'www.myserveraddress.com' -- remote access host MYSQL_SERVER_PORT = 6033 -- remote access port (asymetric forwarding 6603 ext to 3306 internal to avoid internet MySQL known port scanning) TIMEOUT = 3 -- timeout to avoid blocking when MySQL server could not be reached (set timeout in seconds) -- Connect to MySQL database dbcon, err = env:connect(DB_NAME, USERNAME, PASSWORD, MYSQL_SERVER_IP, MYSQL_SERVER_PORT, TIMEOUT) -- Error handler if err then log(err) env:close() env = nil alert('Connection to MySQL could not be established, system will try to restart connection') return end if dbcon then -- Set connected variable to true connected = true -- Set DB connection autocommit to true dbcon:setautocommit(true) -- Check if table for recording excist inside the database and holds data cursor = dbcon:execute('SHOW TABLES LIKE "objectdata"') -- Loop trough fetched data to see if rows are available while true do row = cursor:fetch({}, 'a') if row then -- do nothing log("Table already exist, resuming script") break else -- Drop DB table first before create new one dbcon:execute('DROP TABLE objectdata') -- Create DB table cursor = dbcon:execute('CREATE TABLE objectdata (ID int AUTO_INCREMENT, address varchar(255) NOT NULL, name varchar(255) NOT NULL, value varchar(255) NOT NULL, updatetime int NOT NULL, timestamp varchar(255) NOT NULL, PRIMARY KEY (ID))') log("Table for objectdata is created, resuming script") break end end else -- Exit script for new try return end end -- Init client for listening to all KNX events if not client then require('genohm-scada.eibdgm') --handler for all event types eventhandler = function(event) objectinfo = grp.find(event.dst) -- fix for auto discovered objects without a name if objectinfo.name == nil then objectinfo.name = "unknown" end -- fix for new created objects without a having set value once if objectinfo.value == nil then objectinfo.value = "no value" else objectinfo.value = tostring(objectinfo.value) end -- fix for time object if type(objectinfo.value) == "table" then objectinfo.value = string.format("%02d", objectinfo.value.hour) .. ":" .. string.format("%02d", objectinfo.value.minute) .. ":" .. string.format("%02d", objectinfo.value.second) end -- fix for boolean object if type(objectinfo.value) == "boolean" then objectinfo.value = tostring(objectinfo.value) end -- calculate human readable timestamp timestamp = os.date("%Y-%m-%d %H:%M:%S", objectinfo.updatetime) -- write data to MySQL database cursor, err = dbcon:execute('INSERT INTO objectdata (address, name, value , updatetime, timestamp) VALUES ("' .. objectinfo.address .. '", "' .. objectinfo.name .. '", "' .. objectinfo.value .. '", ' .. objectinfo.updatetime .. ', "' .. timestamp .. '")') -- Get result of transaction result = dbcon:commit() -- Error handler if not result or err then if connected == true then alert('Connection to MySQL is broken or data is not processed correct, system will buffer data until connection is restored') end connected = false else if not connected then connected = true end end end -- Create client client = eibdgm:new() -- Set group communication handlers client:sethandler('groupread', eventhandler) client:sethandler('groupwrite', eventhandler) client:sethandler('groupresponse', eventhandler) end -- Next cycle client:step()
BR,
Erwin
Dear Erwin,
How would be the code to create automatically the tables I need.
This is my code now:
Code:
require('luasql.mysql')
env = luasql.mysql()
dbcon, err = env:connect(DB_NAME, USERNAME, PASSWORD, MYSQL_SERVER_IP, MYSQL_SERVER_PORT)
if dbcon then
map = {
[1] = '1/1/1',
[2] = '1/1/2',
}
for id, addr in pairs(map) do
value = grp.getvalue(addr)
dbcon:execute('INSERT INTO trends_values (trend_id, value, created_at) VALUES (' .. id .. ', ' .. value .. ', NOW())')
dbcon:execute('UPDATE trends_definition SET updated_at=NOW() WHERE id=' .. id)
end
dbcon:close()
else
log('mysql connection failed ' .. tostring(err))
end
env:close()(19.11.2019, 14:07)admin Wrote: Example script. Map table keys are trend ID from external database, values are group addresses to get values from. Don't forget to change access details in connect function.
Code:require('luasql.mysql') env = luasql.mysql() dbcon, err = env:connect(DB_NAME, USERNAME, PASSWORD, MYSQL_SERVER_IP, MYSQL_SERVER_PORT) if dbcon then map = { [1] = '1/1/1', [2] = '1/1/2', } for id, addr in pairs(map) do value = grp.getvalue(addr) dbcon:execute('INSERT INTO trends_values (trend_id, value, created_at) VALUES (' .. id .. ', ' .. value .. ', NOW())') dbcon:execute('UPDATE trends_definition SET updated_at=NOW() WHERE id=' .. id) end dbcon:close() else log('mysql connection failed ' .. tostring(err)) end env:close()
I dont't want to modify the code every time I create a new object.
Is there any way to fill the map automatically when I create new objects in LM?
(19.11.2019, 09:49)admin Wrote: There's no direct way of doing this. You can create a scheduled script that will push values to SQL database every minute or so. A better approach is to use a database that is meant for time-series data like InfluxDB.
But if I push values directly from the objects and the connection with the database fails, this values will be lost. It's important to know that my database is in the cloud.
Can I push values from the trends. In this case I always have the values recorded locally in the LM and I do a backup once a day for example.