Read/write mysql database

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

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