Using values from lUA in SQL lookup

[Trond Hoyem]

Hi

I am trying to make a script that sets the constant light control to manual mode if one is pressing a button (the function is not working properly in the PIR).

What I need to do is locate the room number and the address for the manual control. As I do not want to greate one script for each room in the buliding I am lookin for a shortcut. So far I am able to find addresses in the DB dependent on different criterias. Now my problem is that I need to get one value from the sending group address, I.E the room number, and then locate the appropriate address for setting the controller to manual mode.

so far I have this;

Code:

room = string.sub(grp.find(event.dst).name, 1, 4) --Identify the room


log(room) -- this returns the correct room number.
nyGA = db:getall('SELECT name FROM objects WHERE name LIKE "%..room..%" AND name LIKE "%set auto%"')
log(nyGA) -- this returns nil as no address contains the text 'room'

The problem is that the variable called room does not work in my SQL query. How can I insert the room number in the query?

Every group address for manual control is tagged with the same tag, and this should then all run in one script, if that is not to demanding on the processor....

[admin]

Why not just use some addressing rules between source and control objects? Like 1/X/1 and 1/X+1/1?
Your script should probably look like this:

Code:

nyGA = db:getall('SELECT name FROM objects WHERE name LIKE "%' .. room .. '%" AND name LIKE "%set auto%"')

[Trond Hoyem]

Why not just use some addressing rules between source and control objects? Like 1/X/1 and 1/X+1/1?
Your script should probably look like this:

Code:

nyGA = db:getall('SELECT name FROM objects WHERE name LIKE "%' .. room .. '%" AND name LIKE "%set auto%"')

Well, the project is existing, and there are no such rules there. I do not want to redesign it all together.

On the other hand, I am no big fan of rules like that as the rule always fail as there is always one or more rooms/ functions that do not fit the rule. Good naming usually works much better imo.

The fix you gave me did it BTW! Thanks a lot!

---

One more issue here:

I can't seem to find the object name. When I am logging the result from the DB i get something that to me looks like a table within a table:
manInput 30.01.2019 14:30:26
* table:
[1]
* table:
[name]
* string: 3407 landskap lys set auto

The string called [name] is correct, but how to I get to it? When I log the result like this;
nyGA = db:getall('SELECT name FROM objects WHERE name LIKE "%' .. room .. '%" AND name LIKE "%set auto%"')
log(nyGA)
ga = nyGA.name
log(ga)

I get nil in return.

[admin]

Use getrow instead of getall.

[Trond Hoyem]

Use getrow instead of getall.

Perfect!
Thanks again!