(19.11.2019, 09:49)admin Wrote: You can create a scheduled script that will push values to SQL database every minute or so.

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

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

(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

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

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

(17.05.2020, 11:52)Erwin van der Zwart Wrote: 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

(18.05.2020, 07:38)Daniel. Wrote: Does this trend testswitch exist?

(18.05.2020, 07:50)admin Wrote: Have you tried clicking Save before Run script?

(18.05.2020, 07:58)admin Wrote: Some features are disabled on demo LM so this might not work. Do you have another LM to test this?

(18.05.2020, 08:06)Daniel. Wrote: On my LM this script works fine.