Use vars in Change Node

General
1

Hi how can input vars in a Change Node

lits say i have SQL statement
UPDATE openhab.vars SET STATE='XXX' WHERE idVARS='3';

and i want the XXX to be a var that i have let's say a global

what to do? maybe something like this?
UPDATE openhab.vars SET STATE=%var% WHERE idVARS='3';

and how can i take the payload ?
UPDATE openhab.vars SET STATE=%msg.payload% WHERE idVARS='3';
i am talking about the set filed

2

You can use JSONata for that. Oddly, just what I've been doing today:

This changes the topic from something like homie/<devicename>/sensors/temperature to be a friendly name looked up from a global variable (that is now persisted to the file system! Way to go Nick). The output is a simple string. Not all JSONata output has to be JSON.

(
$t := $split(topic, "/")[1];
$dm := $globalContext("deviceMap", "file");
$room := $lookup($dm, $t);

"TEMPERATURE/" & $room & "/sensor";
)
3

Personally I'd look at the Template node rather than the Change node. Whilst JSOnata does let you do that sort of thing, if you don't need to do any processing beyond just inserting the message properties at the required points, the Template is possibly easier.

2 Likes
4

To provide a rather simpler example, to do what you want you can use a Change node configured to Set msg.payload to JSONata expression (thats J: in the dropdown, not JSON)
"UPDATE openhab.vars SET STATE=" & payload & " WHERE idVARS='3';"

5

i will try

6

i dont have JSONata expression only JSON
what am i missing?

7

Select "Expression" from the list of options ... With the j: icon.

8

getting this when i try to pass lets sat off

"Invalid JSONata expression: Syntax error: "openhab""

this is my flow a simple inject String

[{"id":"5b69b1f6.84959","type":"inject","z":"3a44a2d3.e9a7ae","name":"","topic":"","payload":"OFF","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":676.0195827484131,"y":715.0039119720459,"wires":[["160308e3.440b57","b84aa268.6b03d"]]},{"id":"b84aa268.6b03d","type":"change","z":"3a44a2d3.e9a7ae","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"UPDATE openhab.vars SET STATE=" & payload & " WHERE idVARS='3';","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":930.8828125,"y":711.13671875,"wires":[["ed1429be.299688"]]}]

9

Please edit your post and put ``` (three.back tick characters) ona new line before and after your flow JSON. That will prevent the forum from mangling some of its contents. As it currently stands, we cannot import it to have a look

10

You have missed off the quotes round the text strings. You need to enter the text as I previously posted, including the quotes
"UPDATE openhab.vars SET STATE=" & payload & " WHERE idVARS='3';"

11

Or as @knolleary proposed use a simple template node:
UPDATE openhab.vars SET STATE={{payload}} WHERE idVARS='3';

1 Like
12

Don't forget the single quotes around any text values in the SQL statement:
UPDATE openhab.vars SET STATE='{{{payload}}}' WHERE idVARS='3';

I would also use triple braces to avoid any html entity encodings...

13

And of course, if that payload comes from an untrusted source (e.g. user interface), sanitise it before feeding it into your statement!