Read/write mysql database

[admin]

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.

[pentadom]

You can create a scheduled script that will push values to SQL database every minute or so.

I need to push values every 15 minutes to the database of almost 50 objects, but I'm not a programmer. If someone could help me would be great

[admin]

Can you provide table structure and what data do you want to add there apart from object values (group address, name, date/time etc)?

[pentadom]

Can you provide table structure and what data do you want to add there apart from object values  (group address, name, date/time etc)?

The structures could be like this:

- Table 1 "trending_definition" including [trending id, group address, object name, data type, date/time last update]
- Table 2 "trenging_values" including [value id, trending id, value, date/time]

I accept any advice or suggestion about the structure.

[admin]

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:

1234567891011121314151617181920212223

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()

[pentadom]

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:

1234567891011121314151617181920212223

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()

This code creates the tables (trends_values, trends_definition) automatically or I must create it manually in the database?

[admin]

You have to create tables manually and fill trends_definition table with data.

[Erwin van der Zwart]

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:

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119

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

[pentadom]

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:

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119

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

1234567891011121314151617181920212223

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()

---

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:

1234567891011121314151617181920212223

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?

---

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. 

[Erwin van der Zwart]

Hi,

Yes you can, you have seen the script for fetching the values from a trend, combine it with this script and modify the sql queries and you have what you need, it’s just a matter of creating a script with the samples you have..

BR,

Erwin

[pentadom]

Hi,

Yes you can, you have seen the script for fetching the values from a trend, combine it with this script and modify the sql queries and you have what you need, it’s just a matter of creating a script with the samples you have..

BR,

Erwin

Thanks Erwin, it's difficult for me because I'm not a programmer but I'll try 

I did a test fetching values from a trend with the next code:

Code:

123456789101112

require('trends') name = 'testswitch' dates = {} dates['start'] = { year = 2020, month = 5, day = 17 } dates['end'] = { year = 2020, month = 5, day = 18 } -- resolution (in seconds) is optional -- default trend resolution is used when not set resolution = nil res = trends.fetch(name, dates, resolution) log(res)

but I have an error when I run script:

[Daniel]

Does this trend testswitch exist?

[admin]

Have you tried clicking Save before Run script?

[pentadom]

Does this trend testswitch exist?

Yes, here it is:

---

Have you tried clicking Save before Run script?

Yes. I tried "save" and "save and close"

I'm testing on the demo LM from openrb web. You can see all the configuration there if you want. The script called "SQL" is in the scheduled scripts.

[admin]

Some features are disabled on demo LM so this might not work. Do you have another LM to test this?

[pentadom]

Some features are disabled on demo LM so this might not work. Do you have another LM to test this?

No at the moment, I have it in my workplace. I'm confined because of the COVID-19 

I supposed then it's because of a disabled feature really?

[Daniel]

On my LM this script works fine.

[pentadom]

On my LM this script works fine.

Thank you so much for the information Daniel. I must test it on another LM.

[pentadom]

Hello,

I did some tests with the next code. I want to insert all the trending values in my SQL database. When I run the script all the code executes correctly without errors in the logs but in the database I don't see the new values.. 

Code:

1234567891011121314151617181920212223242526272829303132333435363738394041424344

require('trends') require("luasql.mysql"); --Get a list of all trendings names = db:getlist('SELECT name FROM trends') log (names) --Defines a period of the last day dates = {} dates['start'] = os.date('*t') dates['start'].day = dates['start'].day - 1 dates['end'] = os.date('*t') env = luasql.mysql(); --DATABASE CONNECTION -> (DB_NAME, USERNAME, PASSWORD, MYSQL_SERVER_IP, MYSQL_SERVER_PORT) dbcon, err = env:connect('CONFIDENTIAL', 'admin','CONFIDENTIAL', 'lm-database.CONFIDENTIAL.us-east-1.rds.amazonaws.com', '3306'); log(dbcon, err) if dbcon then   -- Iterates all the trendings   for _, name in ipairs(names) do         -- Get all the trending values     data = trends.fetch(name, dates)         -- Iterates all the trending values     for id, val in pairs(data) do       -- Insert the trending values in the database.       dbcon:execute('INSERT INTO trending-definition (id, value) VALUES (id,val)')      end   end else   log('mysql connection failed ' .. tostring(err)) end   dbcon:close() env:close()

I'm not a programmer and It's my first time doing a script with LUA so I apologize in advance for the posible errors.

[admin]

Use this table structure for data. trend_id will be the same id as in LM.

Code:

123456

CREATE TABLE `trends_data` (   `trend_id` int(11) NOT NULL,   `dt` datetime NOT NULL,   `value` double DEFAULT NULL,   PRIMARY KEY (`trend_id`,`dt`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Updated script:

Code:

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051

require('trends') require('luasql.mysql') env = luasql.mysql() --DATABASE CONNECTION -> (DB_NAME, USERNAME, PASSWORD, MYSQL_SERVER_IP, MYSQL_SERVER_PORT) dbcon, err = env:connect('CONFIDENTIAL', 'admin', 'CONFIDENTIAL', 'lm-database.CONFIDENTIAL.us-east-1.rds.amazonaws.com', 3306) if dbcon then   items = db:getall('SELECT id, name FROM trends')   trends.NaN = 'NULL'   dates = {     ['start'] = os.date('*t'),     ['end'] = os.date('*t'),   }   dates.start.day = dates.start.day - 1   date = string.format('%04d-%02d-%02d',     dates.start.year, dates.start.month, dates.start.day)   for _, item in ipairs(items) do     id = item.id     data = trends.fetch(item.name, dates)     values = {}     if data and #data > 0 then       step = 1440 / #data -- in minutes       for i, val in ipairs(data) do         mins = (i - 1) * step         datetime = string.format('%s %02d:%02d:00',           date, math.floor(mins / 60), mins % 60)         values[ #values + 1 ] = string.format('(%d,"%s",%s)',           id, datetime, tostring(val))       end     end     if #values > 0 then       query = 'INSERT INTO trends_data (trend_id, dt, value) VALUES ' .. table.concat(values, ',')       dbcon:execute(query)     end   end   dbcon:close() else   log('mysql connection failed ' .. tostring(err)) end    env:close()