SQL Issue inserting a record in a database

General
1

Hi All,

Completly new to Node Red (latest version, just installed) but understand the working. It's a nice development environment but one can get vague error messages Like this one:
"Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DHT_C_Sensor' at line 1"

I've looked over end over and cannot find a clue. The function is quite simple

var dataArray = msg.payload.split(";");
var itype = dataArray[1];
var ihum = dataArray[2];
var itemp = dataArray[3];
var ihic = dataArray[4];
var irsrp = dataArray[5];
msg.payload = dataArray;

var msgDB = {};
msgDB.topic = "insert into DHT(Type,Hum,Temp,Hic,rsrp) values(?,?,?,?,?);"
msgDB.payload = [itype, ihum, itemp, ihic, irsrp];

I manually created the SQL string and executed it and there was no problem.
The DataBase exist, the table exist, the connections are OK

image
image733Ă—319 10.6 KB

Anyone could give me a hint where to look?

2

Start by setting debug 4 to Output Complete Message then look at what it contains. If you can't see the problem then show us what is there.

I suspect that node-red is just passing on an error from the database driver, the text is probably not generated by node-red.

3

Judging by your flow diagram, msg.topic is "DHT_C_Sensor", and that's the string the database is complaining about.

You construct your query as msgDB but you are possibly passing msg to the MySQL node.

1 Like
4

Oh yes, good catch.

@BartZ, possibly at the end of the function you have
return msg
instead of
return msgDB

However, it is good practice to modify and pass on the message passed in so you might be better with just

msg.topic = "insert into DHT(Type,Hum,Temp,Hic,rsrp) values(?,?,?,?,?);"
msg.payload = [itype, ihum, itemp, ihic, irsrp]
return msg
1 Like
5

JBudd, Colin,

Thank you, it solved the problem!!
To be quite honest, I copied (stolen/borrowed) the code. So my task for today: crash course Node Red! Really appriciate the help, thought coud do the quick route

Bart

6

Ps Colin,
The debug 4 - Output Complete Message didn't give me more logical information. But good to know to set this on in case of ...

2/5/2025, 7:17:16 AMnode: debug 4
DHT_C_Sensor : msg : Object
object
topic: "DHT_C_Sensor"
payload: array[6]
0: "DHT01"
1: "C"
2: " 53.50"
3: "16.50"
4: "15.60"
5: " 123"
qos: 1
retain: false
_msgid: "ad43d54196eee62f"

2/5/2025, 7:17:16 AMnode: DHT
msg : error
"Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DHT_C_Sensor' at line 1"
7

Your SQL statement is a "prepared query" which is good form, it protects the database from SQL injection (I've learned that by rote, no idea how it actually works).
I personally avoid the ?, ?, ?, ... syntax, preferring a prepared query with msg.payload as explicit key: value pairs.

So I would have written your function like this

const dataArray = msg.payload.split(";");
msg.payload = {
"itype": dataArray[1], 
"ihum": dataArray[2];
"itemp":  dataArray[3],
"ihic": dataArray[4],
"irsrp": dataArray[5]
}

msg.topic = "insert into DHT (Type,Hum,Temp,Hic,rsrp) values (:itype, :ihum, :itemp, :ihic, :irsrp)"
return msg

Have fun with Node-red! :grinning:

8

Did it not show you more clearly that msg.topic is incorrect?