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

General
1

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
SQL2DOMOTICZ.jpg1267Ă—533 127 KB

2

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.

3 
[{"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":""}]
4

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

```
your code
```

5

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

6

Try changing your function code from

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

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.

7

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"

8

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

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
Screen Shot 2019-08-21 at 4.35.41 AM.png1080Ă—311 55.7 KB

9

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!

10

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
image.png1201Ă—374 31.9 KB

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
image.png404Ă—564 9.34 KB

11

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

12

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

13

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

1 Like
14

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

15

Thank you, good suggestion!

16

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?

1 Like
17

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

18

See what you did?

Read your code through...

Here it is with comments to help...

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;
19

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

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.

20

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?