# Read SQL value and convert to Domoticz MQTT Topic - payload issue

**URL:** <https://discourse.nodered.org/t/read-sql-value-and-convert-to-domoticz-mqtt-topic-payload-issue/14504>\
**Category:** General\
**Created:** [19 August 2019 20:48 UTC](https://discourse.nodered.org/t/read-sql-value-and-convert-to-domoticz-mqtt-topic-payload-issue/14504 "2019-08-19T20:48:20Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![domotic](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/domotic/32/11084_2.png) [@domotic](https://discourse.nodered.org/u/domotic)\
**Post date:** [19 August 2019 20:48 UTC](https://discourse.nodered.org/t/read-sql-value-and-convert-to-domoticz-mqtt-topic-payload-issue/14504/1 "2019-08-19T20:48:20Z")

</div>

**Goal: Read value from SQL database and present data as MQTT Topic for Domoticz**

I have 2 issues:

**Issue 1:** SQL data will be succesfully read when WHERE statement is predefined in the SQL syntax. But when I want to use the system data in combination with the payload data from the Date/Time Formatter it remains empty. In my opinion the output data is exactly the same as the predefined data. I have tried as many methods with { [ " and ' but no success.

**Issue 2:** The typical string for a Domoticz MQTT string _{"idx":41,"nvalue":0,"svalue":"4500"}_  
can be manually configured in the node-red function SQL\_Value\_2\_Domoticz which is succesfully posted as readable string for Domoticz. But how do you describe the right syntax for using the payload data from the SQL data

Hope that someone can help me to get a working flow.

 ![SQL2DOMOTICZ](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/4/467ede369044423359ffc61a39021dc535be07dd.jpeg)

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [19 August 2019 23:36 UTC](https://discourse.nodered.org/t/read-sql-value-and-convert-to-domoticz-mqtt-topic-payload-issue/14504/2 "2019-08-19T23:36:49Z")

</div>

Please provide your flow. Make sure to use three backtic's ``` on the line before and after your flow. That will make it easier to look at what you are doing.

---

<div class="post-metadata">

**Author:** ![domotic](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/domotic/32/11084_2.png) [@domotic](https://discourse.nodered.org/u/domotic)\
**Post date:** [20 August 2019 05:29 UTC](https://discourse.nodered.org/t/read-sql-value-and-convert-to-domoticz-mqtt-topic-payload-issue/14504/3 "2019-08-20T05:29:38Z")

</div>

```auto
[{"id":"2709204f.b61f8","type":"tab","label":"Flow 1","disabled":false,"info":""},{"id":"deaf74a4.e5a378","type":"mqtt out","z":"2709204f.b61f8","name":"e_consumption","topic":"domoticz/in","qos":"0","retain":"true","broker":"c2ee527.c62d7b","x":1020,"y":220,"wires":[],"inputLabels":["Test"]},{"id":"57e86193.a35c7","type":"mysql","z":"2709204f.b61f8","mydb":"2a831904.05f906","name":"logdata","x":635,"y":320,"wires":[["307aa134.c6608e","c698aada.736658"]],"l":false},{"id":"7c66ddff.9b2cc4","type":"inject","z":"2709204f.b61f8","name":"Periodic timestamp","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"*/5 0 * * *","once":false,"onceDelay":0.1,"x":140,"y":100,"wires":[["1ef734b.1737dcb"]]},{"id":"c698aada.736658","type":"debug","z":"2709204f.b61f8","name":"a1","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"payload[0].count_e","targetType":"jsonata","x":750,"y":380,"wires":[]},{"id":"7a599381.d51a3c","type":"function","z":"2709204f.b61f8","name":"SQL_Query","func":"msg = {};\npayload = {};\nmsg.payload=payload;\n//msg.topic = \"SELECT SUM(1000*kWh_periode) AS count_e FROM data_e WHERE Datum = {msg.payload}\";\nmsg.topic = \"SELECT SUM(1000*kWh_periode) AS count_e FROM data_e WHERE Datum = '01-08-2019'\";\nreturn msg;","outputs":1,"noerr":0,"x":530,"y":260,"wires":[["57e86193.a35c7","8c73b53a.a66198"]]},{"id":"307aa134.c6608e","type":"function","z":"2709204f.b61f8","name":"SQL_Value_2_Domoticz","func":"msg = {};\npayload = {};\nmsg.payload=payload;\nmsg.payload.idx=41;\nmsg.payload.nvalue=0;\n//msg.payload.svalue=payload;\nmsg.payload.svalue='4500';\nmsg.topic=\"\";\nreturn msg;","outputs":1,"noerr":0,"x":790,"y":280,"wires":[["deaf74a4.e5a378","e9f2b45a.966ef8"]]},{"id":"e9f2b45a.966ef8","type":"debug","z":"2709204f.b61f8","name":"a2","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","x":990,"y":340,"wires":[]},{"id":"1ef734b.1737dcb","type":"moment","z":"2709204f.b61f8","name":"","topic":"","input":"","inputType":"msg","inTz":"Europe/Amsterdam","adjAmount":0,"adjType":"days","adjDir":"add","format":"'DD-MM-YYYY'","locale":"nl_NL","output":"","outputType":"msg","outTz":"Europe/Amsterdam","x":340,"y":200,"wires":[["d1106606.0d3128","7a599381.d51a3c"]]},{"id":"d1106606.0d3128","type":"debug","z":"2709204f.b61f8","name":"converted","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","x":520,"y":140,"wires":[]},{"id":"8c73b53a.a66198","type":"debug","z":"2709204f.b61f8","name":"SQL Query","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"payload","targetType":"jsonata","x":690,"y":200,"wires":[]},{"id":"c2ee527.c62d7b","type":"mqtt-broker","z":"","name":"","broker":"192.168.178.28","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":"60","cleansession":true,"birthTopic":"domoticz/in","birthQos":"0","birthRetain":"false","birthPayload":"I'm there","closeTopic":"domoticz/in","closeQos":"0","closeRetain":"false","closePayload":"I will go","willTopic":"domoticz/in","willQos":"0","willRetain":"false","willPayload":"I'm lost"},{"id":"2a831904.05f906","type":"MySQLdatabase","z":"","host":"192.168.178.27","port":"8457","db":"logdata","tz":""}]

```

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [20 August 2019 09:07 UTC](https://discourse.nodered.org/t/read-sql-value-and-convert-to-domoticz-mqtt-topic-payload-issue/14504/4 "2019-08-20T09:07:09Z")

</div>

Read @zenofmud's comment, export it again and retry using that valuable information:

```  
your code  
```

---

<div class="post-metadata">

**Author:** ![domotic](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/domotic/32/11084_2.png) [@domotic](https://discourse.nodered.org/u/domotic)\
**Post date:** [20 August 2019 17:37 UTC](https://discourse.nodered.org/t/read-sql-value-and-convert-to-domoticz-mqtt-topic-payload-issue/14504/5 "2019-08-20T17:37:58Z")

</div>

Oh I see, every day a new learning day! Thank you.  
I' ve updated my post.

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [20 August 2019 23:29 UTC](https://discourse.nodered.org/t/read-sql-value-and-convert-to-domoticz-mqtt-topic-payload-issue/14504/6 "2019-08-20T23:29:10Z")

</div>

Try changing your function code from

```auto
msg = {};
payload = {};
msg.payload=payload;
//msg.topic = "SELECT SUM(1000*kWh_periode) AS count_e FROM data_e WHERE Datum = {msg.payload}";
msg.topic = "SELECT SUM(1000*kWh_periode) AS count_e FROM data_e WHERE Datum = '01-08-2019'";
return msg;

```

to

```auto
msg.topic = "SELECT SUM(1000*kWh_periode) AS count_e FROM data_e WHERE Datum = "+ msg.payload;
//msg.topic = "SELECT SUM(1000*kWh_periode) AS count_e FROM data_e WHERE Datum = '01-08-2019'";
return msg;

```

and see you you do.

---

<div class="post-metadata">

**Author:** ![domotic](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/domotic/32/11084_2.png) [@domotic](https://discourse.nodered.org/u/domotic)\
**Post date:** [21 August 2019 05:06 UTC](https://discourse.nodered.org/t/read-sql-value-and-convert-to-domoticz-mqtt-topic-payload-issue/14504/7 "2019-08-21T05:06:22Z")

</div>

> [@zenofmud](#):
>
> msg.topic = "SELECT SUM(1000\*kWh\_periode) AS count\_e FROM data\_e WHERE Datum = "+ msg.payload;

This gives an error:  
"Error: ER\_PARSE\_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[object Object]' at line 1"

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [21 August 2019 08:29 UTC](https://discourse.nodered.org/t/read-sql-value-and-convert-to-domoticz-mqtt-topic-payload-issue/14504/8 "2019-08-21T08:29:42Z")

</div>

Sorry, I forgot the date was a string, it needs the quotes. use this instead:

```auto
msg.topic = "SELECT SUM(1000*kWh_periode) AS count_e FROM data_e WHERE Datum = '"+ msg.payload+"'";

```

You might find it easier to create the SQL using a `template` node

 ![41%20AM](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/6/65627259c3e9af3b6229197d3b02bfe78a364268.png)

---

<div class="post-metadata">

**Author:** ![domotic](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/domotic/32/11084_2.png) [@domotic](https://discourse.nodered.org/u/domotic)\
**Post date:** [21 August 2019 17:41 UTC](https://discourse.nodered.org/t/read-sql-value-and-convert-to-domoticz-mqtt-topic-payload-issue/14504/9 "2019-08-21T17:41:44Z")

</div>

Thank you Paul, the template node did the trick for me!. The other example in the function node was no success. This motivates me to solve issue 2 myself before I ask the next question. Stay tuned!

---

<div class="post-metadata">

**Author:** ![domotic](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/domotic/32/11084_2.png) [@domotic](https://discourse.nodered.org/u/domotic)\
**Post date:** [21 August 2019 21:31 UTC](https://discourse.nodered.org/t/read-sql-value-and-convert-to-domoticz-mqtt-topic-payload-issue/14504/10 "2019-08-21T21:31:09Z")

</div>

Spent hours again for the 2nd issue, but not the result I want. The payload as debug output from the MySQL node is shown as:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/8/837968804230e4cb489c3e5dd86757c0f4e0a7f4.png)

Now I need to get the numeric value 6000 as a variable for a string. The composition of a static string to Domoticz is not the problem. Like the first issue I need the "6000" as the dynamic part of the string.

As a simple test I've used a template node with only the {{payload}} code and in the debug node I see the "6000" part but how do I isolate this as a variable for my string?

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/0/094d8fde3c9f1b1b7276774f294c92518a57a5be.png)

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [21 August 2019 21:59 UTC](https://discourse.nodered.org/t/read-sql-value-and-convert-to-domoticz-mqtt-topic-payload-issue/14504/11 "2019-08-21T21:59:12Z")

</div>

> [@domotic](#):
>
> I see the "6000" part but how do I isolate this as a variable for my string?

When you see the 6000 value in debug window, hover mouse over it and 3 icons appear at the right hand side. One of them will copy the path.

It will be something like `array[1].count_0`

Use what is copied in the template node  
E.g...  
`select top {{array[1].count_0}} from xxx `

---

<div class="post-metadata">

**Author:** ![ukmoose](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ukmoose/32/13_2.png) [@ukmoose](https://discourse.nodered.org/u/ukmoose)\
**Post date:** [22 August 2019 07:27 UTC](https://discourse.nodered.org/t/read-sql-value-and-convert-to-domoticz-mqtt-topic-payload-issue/14504/12 "2019-08-22T07:27:53Z")

</div>

There’s a page in the docs called working with messages that is worth reading

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [22 August 2019 07:36 UTC](https://discourse.nodered.org/t/read-sql-value-and-convert-to-domoticz-mqtt-topic-payload-issue/14504/13 "2019-08-22T07:36:45Z")

</div>

Wish I had some kind of automatic macro reply for the amount of times this comes up. Same with "I have 2 values from 2 nodes I need to add together..."

---

<div class="post-metadata">

**Author:** ![domotic](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/domotic/32/11084_2.png) [@domotic](https://discourse.nodered.org/u/domotic)\
**Post date:** [22 August 2019 18:21 UTC](https://discourse.nodered.org/t/read-sql-value-and-convert-to-domoticz-mqtt-topic-payload-issue/14504/14 "2019-08-22T18:21:26Z")

</div>

Hi Steve, thanks for your answer. What is the added value of it in relation to my question?

---

<div class="post-metadata">

**Author:** ![domotic](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/domotic/32/11084_2.png) [@domotic](https://discourse.nodered.org/u/domotic)\
**Post date:** [22 August 2019 18:21 UTC](https://discourse.nodered.org/t/read-sql-value-and-convert-to-domoticz-mqtt-topic-payload-issue/14504/15 "2019-08-22T18:21:55Z")

</div>

Thank you, good suggestion!

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [22 August 2019 18:33 UTC](https://discourse.nodered.org/t/read-sql-value-and-convert-to-domoticz-mqtt-topic-payload-issue/14504/16 "2019-08-22T18:33:30Z")

</div>

> [@domotic](#):
>
> thanks for your answer. What is the added value of it in relation to my question?

To which post are you referring?

My 1st where I provided helpful instructions on how to "isolate a variable" so that you could figure out how to get the path to variables in a msg so you could get access to the 6000 and use it in a string?

or

my second (unhelpful) post where I responded to @ukmoose?

---

<div class="post-metadata">

**Author:** ![domotic](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/domotic/32/11084_2.png) [@domotic](https://discourse.nodered.org/u/domotic)\
**Post date:** [22 August 2019 19:03 UTC](https://discourse.nodered.org/t/read-sql-value-and-convert-to-domoticz-mqtt-topic-payload-issue/14504/17 "2019-08-22T19:03:22Z")

</div>

The first one was absolutely helpful, the second one I believe not. Your contribution confirms that more users are struggling with this issue. I've read the instructions " Working with messages" for 4 times now and it's still not clear what to do exactly.

At the very first beginning before I had posted this issue on the forum I've copied the path from the debug node which shows me the perfect clean value I want to use as variable.

At the moment of writing now the actual value is **4000**. The path to this value is **payload[0].count\_e**. So I paste the path in this function:

msg = {};  
payload = {};  
msg.payload=payload;  
msg.payload.idx=41;  
msg.payload.nvalue=0;  
[//msg.payload.svalue=](https://msg.payload.svalue=)'8888';  
msg.payload.svalue=**msg.payload[0].count\_e**;  
msg.topic="";  
return msg;

In this case it returns an error _"TypeError: Cannot read property 'count\_e' of undefined"_. But I've used all combinations with quotes, mustaches etcetera in the meantime.

When I uncomment the line **[//msg.payload.svalue=](https://msg.payload.svalue=)'8888';** it generates the perfect string I need, but this is a static value.

So if you or anyone else can help me with the solution to use this path as a variabe instead of the static 8888 I will be very thankful. For me it's just a matter of doing something with trial and error.

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [22 August 2019 19:07 UTC](https://discourse.nodered.org/t/read-sql-value-and-convert-to-domoticz-mqtt-topic-payload-issue/14504/18 "2019-08-22T19:07:33Z")

</div>

> [@domotic](#):
>
> msg = {};  
> payload = {};  
> msg.payload=payload;  
> msg.payload.idx=41;  
> msg.payload.nvalue=0;  
> [//msg.payload.svalue=](https://msg.payload.svalue=)'8888';  
> msg.payload.svalue= **msg.payload[0].count\_e** ;  
> msg.topic="";  
> return msg;

See what you did?

Read your code through...

Here it is with comments to help...

```auto
msg = {}; // make a new empty object
payload = {}; // make a new empty object
msg.payload=payload; // add empty payload to empty msg
msg.payload.idx=41; //set a property
msg.payload.nvalue=0;//set a property
//msg.payload.svalue='8888';//set a property
msg.payload.svalue=msg.payload[0].count_e; // << try to access array payload that you overwrote above with an empty object
msg.topic="";
return msg;

```

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [22 August 2019 19:11 UTC](https://discourse.nodered.org/t/read-sql-value-and-convert-to-domoticz-mqtt-topic-payload-issue/14504/19 "2019-08-22T19:11:44Z")

</div>

Incase you're struggling to see the solution...

```auto
let result ={}
result.idx=41; //set a property
result.nvalue=0;//set a property
result.svalue=msg.payload[0].count_e; // update existing msg.payload
msg.payload = result;
return msg;

```

Compare this with yours.

---

<div class="post-metadata">

**Author:** ![domotic](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/domotic/32/11084_2.png) [@domotic](https://discourse.nodered.org/u/domotic)\
**Post date:** [22 August 2019 19:16 UTC](https://discourse.nodered.org/t/read-sql-value-and-convert-to-domoticz-mqtt-topic-payload-issue/14504/20 "2019-08-22T19:16:09Z")

</div>

So what I do in my example is creating an empty object and define empty payload and block the information from payload from the previous node?

[Next page](https://discourse.nodered.org/t/read-sql-value-and-convert-to-domoticz-mqtt-topic-payload-issue/14504.md?page=2)