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 …

[image] Steve-Mcl: let result ={} result.idx=41; //set a property result.nvalue=0;//set a property restult.svalue=msg.payload[0].count_e; // update existing msg.payload msg.payload = result; return msg; Now I understand. Great explanation and education to me. Thank you Steve. The value is no…

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…

[image] 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 …

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]

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!

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] 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 …

[image] 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 co…

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

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

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

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

General

drmibell 22 August 2019 20:50 24

Not long ago there was some discussion of a plugin for Discourse that can be used to create templates for replies like "how to share code", "what versions are you using?", and now these topics. I know the developers have better things to do than tinker with forum mechanics, but this feature, if used tactfully, could remove a minor irritant and help get new users more complete answers quickly.

Topic		Replies	Views
Read from mysql db Dashboard	54	17650	23 December 2018
MQTT Topic split before output to MSSQL General split	18	2014	2 September 2021
Generate SQL statement from 2 mqtt messages General	39	1113	6 July 2020
PLC machine data sent Via MQTT to Node Red Then to SQL Database General	25	802	1 October 2024
MQTT data send from BSB-LAN arduino program General	20	1563	2 January 2021

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

Related topics