# Use vars in Change Node

**URL:** <https://discourse.nodered.org/t/use-vars-in-change-node/2611>\
**Category:** General\
**Created:** [27 August 2018 19:22 UTC](https://discourse.nodered.org/t/use-vars-in-change-node/2611 "2018-08-27T19:22:37Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![aihysp](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/aihysp/32/2278_2.png) [@aihysp](https://discourse.nodered.org/u/aihysp)\
**Post date:** [27 August 2018 19:22 UTC](https://discourse.nodered.org/t/use-vars-in-change-node/2611/1 "2018-08-27T19:22:37Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [27 August 2018 19:44 UTC](https://discourse.nodered.org/t/use-vars-in-change-node/2611/2 "2018-08-27T19:44:22Z")

</div>

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.

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

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

```

---

<div class="post-metadata">

**Author:** ![knolleary](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/knolleary/32/3_2.png) [@knolleary](https://discourse.nodered.org/u/knolleary)\
**Post date:** [27 August 2018 20:23 UTC](https://discourse.nodered.org/t/use-vars-in-change-node/2611/3 "2018-08-27T20:23:27Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [27 August 2018 20:24 UTC](https://discourse.nodered.org/t/use-vars-in-change-node/2611/4 "2018-08-27T20:24:12Z")

</div>

> [@aihysp](#):
>
> UPDATE `openhab` . `vars` SET `STATE` =%msg.payload% WHERE `idVARS` ='3';

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';"`

---

<div class="post-metadata">

**Author:** ![aihysp](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/aihysp/32/2278_2.png) [@aihysp](https://discourse.nodered.org/u/aihysp)\
**Post date:** [27 August 2018 22:07 UTC](https://discourse.nodered.org/t/use-vars-in-change-node/2611/5 "2018-08-27T22:07:30Z")

</div>

i will try

---

<div class="post-metadata">

**Author:** ![aihysp](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/aihysp/32/2278_2.png) [@aihysp](https://discourse.nodered.org/u/aihysp)\
**Post date:** [27 August 2018 22:23 UTC](https://discourse.nodered.org/t/use-vars-in-change-node/2611/6 "2018-08-27T22:23:30Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![knolleary](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/knolleary/32/3_2.png) [@knolleary](https://discourse.nodered.org/u/knolleary)\
**Post date:** [27 August 2018 22:32 UTC](https://discourse.nodered.org/t/use-vars-in-change-node/2611/7 "2018-08-27T22:32:24Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![aihysp](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/aihysp/32/2278_2.png) [@aihysp](https://discourse.nodered.org/u/aihysp)\
**Post date:** [27 August 2018 22:53 UTC](https://discourse.nodered.org/t/use-vars-in-change-node/2611/8 "2018-08-27T22:53:48Z")

</div>

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"]]}]

---

<div class="post-metadata">

**Author:** ![knolleary](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/knolleary/32/3_2.png) [@knolleary](https://discourse.nodered.org/u/knolleary)\
**Post date:** [27 August 2018 23:05 UTC](https://discourse.nodered.org/t/use-vars-in-change-node/2611/9 "2018-08-27T23:05:31Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [28 August 2018 07:19 UTC](https://discourse.nodered.org/t/use-vars-in-change-node/2611/10 "2018-08-28T07:19:20Z")

</div>

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';"`

---

<div class="post-metadata">

**Author:** ![cflurin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/cflurin/32/29_2.png) [@cflurin](https://discourse.nodered.org/u/cflurin)\
**Post date:** [28 August 2018 07:50 UTC](https://discourse.nodered.org/t/use-vars-in-change-node/2611/11 "2018-08-28T07:50:37Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![shrickus](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/shrickus/32/517_2.png) [@shrickus](https://discourse.nodered.org/u/shrickus)\
**Post date:** [28 August 2018 14:22 UTC](https://discourse.nodered.org/t/use-vars-in-change-node/2611/12 "2018-08-28T14:22:26Z")

</div>

> [@cflurin](#):
>
> UPDATE openhab.vars SET STATE={{payload}} WHERE idVARS='3';

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

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [28 August 2018 16:33 UTC](https://discourse.nodered.org/t/use-vars-in-change-node/2611/13 "2018-08-28T16:33:38Z")

</div>

> [@shrickus](#):
>
> 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...

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