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:

12

  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:

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162

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:

1

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:

1234567891011121314151617181920212223242526272829

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:

12345678910111213141516171819202122232425262728293031323334353637

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:

12345678910111213

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:

12345678910111213

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