SQL challenge yet again

[Trond Hoyem]

Hi

I am again struggeling with the SQL lookup...

What I need is to create a table with all objects within a certain address range. I am already able to calculate the start and stop ID from the address table, but when I try to look it up with the SQL, I am not as successful.

The code I am using is; 

Code:

  adresser = db:getall('SELECT ID FROM objects WHERE ID >= "%' .. startID .. '%" AND ID <= "%' .. stopID .. '%"')
  log(adresser)

 
The log gives an empty table.
Any hints as to what I am doing wrong?

[Daniel]

Hi
I'm not sure what is your end game here but you can take an idea from this script how to grab range of objects from DB and do some action on them.
This script will create tags on object based on the range and the separators selected and the object name.

Code:

seperators = '.,_ =+'

-- Start address of objects (min: 0/0/1 = main/sub/address)
start_main = 0
start_sub = 0
start_address = 1

-- End address of objects (max: 31/7/255 = main/sub/address)
end_main = 15
end_sub = 7
end_address = 255

------------------------------------------ End Parameters ------------------------------------------
------------------------------ DON'T CHANGE ANYTHING UNDER THIS LINE -------------------------------

function split(source, delimiters)
        local elements = {}
        local pattern = '([^'..delimiters..']+)'
        string.gsub(source, pattern, function(value) elements[#elements + 1] =     value;  end);
        return elements
end

-- Calculate start address to DB format
start_objectaddress = ((start_main * 2048) + (start_sub * 256) + start_address)

-- Calculate end address to DB format
end_objectaddress = ((end_main * 2048) + (end_sub * 256) + end_address)

-- Get all objects from DB
all_objects = db:getall('SELECT address, name, tagcache FROM objects')
for _, object in ipairs(all_objects) do
  -- Check if object is inside given range
  if object.address >= start_objectaddress and object.address <= end_objectaddress then
     currentrowaddress = object.address
    myobject = grp.find(currentrowaddress)
    nameTable = split(myobject.name, seperators)  --spliting  object string in to table
   
  --  log(nameTable)
    for _, tag in ipairs(nameTable) do
      tagvalue = tag
   
       -- Check if object tag already exists inside DB
     current_object_tag = db:getall('SELECT object, tag FROM objecttags WHERE object = ' .. currentrowaddress .. ' AND tag = "' .. tagvalue .. '"')
     object_tagcache = db:getone('SELECT tagcache FROM objects WHERE address = ' .. currentrowaddress .. '')
     if #current_object_tag == 0 then
        -- Add object to table objecttags
        db:insert('objecttags', {object = currentrowaddress, tag = tagvalue, })
          -- Check if object already has other tag values inside DB and add new values to tagcache
        if object_tagcache == nil or object_tagcache == "" then
          db:update('objects', { tagcache = tagvalue }, { address = currentrowaddress })
            else
          tag_string = "" .. object_tagcache .. ", " .. tagvalue .. ""
               log(tag_string)
          db:update('objects', { tagcache = tag_string }, { address = currentrowaddress })
          end
     end
   
    end
  end
end

script.disable(_SCRIPTNAME)

[admin]

You can use ? placeholders which are replaced by additional arguments passed to getall(). This will properly escape values to prevent SQL injections when parameters come from external sources. There's also SQL BETWEEN operator which is more readable than two comparisons.

Code:

adresser = db:getall('SELECT id FROM objects WHERE id BETWEEN ? AND ?', startID, stopID)

[Trond Hoyem]

Thanks for tips.

I have not been at the project today, so not been able to test anything, but will have a look at this.

@Daniel; what I want to do is to delete a range of objects from the SL by script. The reason is that in a project I have 30 SpaceLYnks as BACnet GW, and I only want a certain range of group addresses on each SL. But I also do not want to edit the ESF-file before importing it to the different SL's, but simply have a script that removes the surplus objects once a day or something.

[Trond Hoyem]

OK, so I was able to do it with the tips I got. Thanks for help!

If anyone alse need this function, just copy this code and modify to your liking;

Code:

start = '26/0/0'
stop = '26/0/255'

------------------------------------------------------------------------------------
--------------------NO CHANGES BELOW THE LINE---------------------------------------

startHG = tonumber(string.split(start, '/')[1])
startMG = tonumber(string.split(start, '/')[2])
startUG = tonumber(string.split(start, '/')[3])

startID = (startHG * 2048) + (startMG * 256) + startUG


stopHG = tonumber(string.split(stop, '/')[1])
stopMG = tonumber(string.split(stop, '/')[2])
stopUG = tonumber(string.split(stop, '/')[3])

stopID = (stopHG * 2048) + (stopMG * 256) + stopUG

if stopID < startID then
  log('Start has higher value than stop. Delete is not possible')
else
  log('Addresses within range are deleted')
  adresser = db:getall('SELECT address, name FROM objects WHERE ID BETWEEN "' .. startID .. '" AND  "' .. stopID .. '"')
  for _, addr in ipairs(adresser) do
    log(addr.name)
    grp.delete(addr.name)
  end
end

[Daniel]

You will have quite a lot of logs there

[Trond Hoyem]

You will have quite a lot of logs there

Yep, I used it during debugging and forgot to remove it :-)

It is no longer there in my live script.

---

Updated version, now as function so that one can add several ranges to delete. 

Now without extensive logging 

Code:

function deleteGA(start, stop)
    startHG = tonumber(string.split(start, '/')[1])
    startMG = tonumber(string.split(start, '/')[2])
    startUG = tonumber(string.split(start, '/')[3])
    
    startID = (startHG * 2048) + (startMG * 256) + startUG
    
    
    stopHG = tonumber(string.split(stop, '/')[1])
    stopMG = tonumber(string.split(stop, '/')[2])
    stopUG = tonumber(string.split(stop, '/')[3])
    
    stopID = (stopHG * 2048) + (stopMG * 256) + stopUG
    
    if stopID < startID then
      log('Start has higher value than stop. Delete is not possible')
    else
      log('Addresses within range are deleted')
      adresser = db:getall('SELECT address, name FROM objects WHERE ID BETWEEN "' .. startID .. '" AND  "' .. stopID .. '"')
      for _, addr in ipairs(adresser) do
        --log(addr.name)
        grp.delete(addr.name)
      end
    end
end





start = '26/0/0'
stop = '26/0/10'
deleteGA(start, stop)

start = '26/0/51'
stop = '26/0/70'
deleteGA(start, stop)

[Erwin van der Zwart]

Hi,

Here is a simplified version (:

Code:

function deleteGA(start, stop)
  startID = knxlib.encodega(start)
  stopID = knxlib.encodega(stop)
  if stopID < startID then
    log('Start has higher value than stop. Delete is not possible')
  else
    result = db:query('DELETE FROM objects WHERE ID BETWEEN ? AND ?', startID, stopID)
    log(result .. ' address(es) within range is/are deleted')
  end
end

deleteGA('26/0/0', '26/0/10')
deleteGA('26/0/51', '26/0/70')

BR,

Erwin

[Trond Hoyem]

Hi,

Here is a simplified version (:

Code:

function deleteGA(start, stop)
  startID = knxlib.encodega(start)
  stopID = knxlib.encodega(stop)
  if stopID < startID then
    log('Start has higher value than stop. Delete is not possible')
  else
    result = db:query('DELETE FROM objects WHERE ID BETWEEN ? AND ?', startID, stopID)
    log(result .. ' address(es) within range is/are deleted')
  end
end

deleteGA('26/0/0', '26/0/10')
deleteGA('26/0/51', '26/0/70')

BR,

Erwin

OK, that is a little more elegant.

Is there any place where one can find all these commands that are used for LM/SL? Like knxlib.encodega()? That would be really helpful to have a list of those, like we have already for the lua commands (https://openrb.com/docs/lua.htm).

[Erwin van der Zwart]

Hi,

I don't have a list, this is a command that is used in a lot of samples on this site, that is also where i got the info, when i see a new command like knxlib i usually log it log(knxlib) to see the included sub functions.

BR,

Erwin