What am I doing wrong, I have wasted hours trying to find out !
I only want the object array with 3 values to go into my database once.
I have an object array with 3 values
I also have another object with 1 value.
Image No1 works but I get the same data 3 times in my database.
Image No2 works I get the data only once in my database ...
BUT i get this error in the debug sidebar... TypeError: Cannot read properties of undefined (reading 'temperature_01')
\
The code in the function node that sends data to my database is
let device = "IP92"; // ENTER HERE ... the IP address.
let location = "PC room"; // ENTER HERE ... Location
let new_date = new Date(); // Does NOT change with time zones across the world.
let iso = new Date().toISOString(); // RFC 3339 format ... for Grafana etc.
let timestamp = Math.round(new_date.getTime() / 1000); // getTime = milliseconds ... this removes last 3 digits to change to seconds ... for Grafana etc.
var temperature = msg.payload.data.temperature_01;
var humidity = msg.payload.data.humidity_01;
var battery = msg.payload.data.battery;
let payload =
{
humidity: humidity, // ENTER HERE ... any new items to add to database, in this section.
iso: iso,
location: location,
temperature: temperature,
timestamp: timestamp,
battery: battery
};
payload = JSON.stringify(payload); // Converts a JavaScript value to a JSON string
const Data_sequence = `( device,device_data) values('${device}','${payload}')` // This is the actual data.
const topic = `INSERT INTO energy ${Data_sequence}` // The insert string to send to database.
msg.topic = topic;
msg.payload = "";
return msg;
This is an object with properties. An array is something else.
You should put them in series switch (check has key) -> switch (has other key) -> switch (has other key) -> link
This is NOT a safe way to insert data into a database. you should use prepared statements. Assuming mysql (you didnt say), the readme has info on how to do prepared queries
No 1 gives an output on ALL three has key selections because they are all valid. Just go with Option 2
No 2 Are you also sending in the battery_voltagemsg, because this does not have a temperature_01 property? If so you can (hopefully) get rid of the error message by adding a check that the data exists
if (!Object.hasOwn(msg.payload, 'data') {
return
}
let temperature = msg.payload.data.temperature_01
let humidity = msg.payload.data.humidity_01
let battery = msg.payload.data.battery
I am assuming that every msg received has all three properties? If not add a check for each property in the function
Thanks for that, when I read that Steve, I thought I was being good for once getting the object correct
No problem, that had been changed on my flow to read Object with properties.
I did try your suggested way and it does work, but say I want 10 values, it gets to be a long flow
This is NOT a safe way to insert data into a database.
The output is going to a SQLite database, I will have to look at using prepared statements.
But it may also go into a PostgreSQL one at some point.
Buckskin I also tried your way and again that worked with no errors.
and it is compact and easily expandable.
I am assuming that every msg received has all three properties?
Yes
But also there is a single object with battery_voltage and I may be wrong, but was that causing the error ?
I must admit I do not understand the
if (!Object.hasOwn(msg.payload, 'data') {
return
}
Can you give a quick idea of what it actually does, as I always learn by my mistakes and then document it for the future
I went to JSON objects thinking it would make my code easier, but it seems to have made it much harder to understand, if like me you don't know the correct terminology, to do a search on the internet.
Thanks so far guys for your help much appreciated, if you or anyone else has any thoughts on improving this please tell me, as I always thought JSON objects were more elegant to use and my code and flows looks a bit of a mess and not easy to understand.
Hi Steve thanks,
Your few words have clarified about the JSON object that I am still using the wrong term.
JSON is the string representation of a JavaScript Object.
Any quick pointers on this as I have looked briefly online and it does look very complicated, or in my case do I not need to as much, as the messages are coming from my own Arduino module and i wrote the code ?
As I have got older I realise i learn much quicker with visual images than with lots of text.
Your answer with added text on my No1 image helped me understand easily.
That is true yes however, if you practice best practice, you will learn best practice. Also, building these queries are often easier while also being secure by default.
Thanks to both of you, you gave me enough info to make changes to my Arduino code and solve the issues I had and this is now how I imagined JSON to work.
I change the battery code sent from the Arduino and added a battery ( object ? ) with a voltage property. Steve I honestly mean this, if I am using any descriptions wrong still please let me know.
So now the DHT switch node just looks for data and then the other battery switch node looks for battery
I now I have no error codes and even have data in my database !
It really is worth learning the correct terms, it helps with searching and explaining issues.
Here are a few more tips I hope you find helpful:
You can use typeof myvar to find the type of a thing
e.g. node.warn({ typeofpayload: typeof msg.payload })
Objects have Properties
You can access them using . i.e: myObject.property1
You can access them using [] i.e: myObject["property1"]
Arrays have Elements
The first element is 0
You can test how many elements it has using myArray.length
You can check it is an array using Array.isArray(myArray)
Objects
// below is a CONSTANT object with 3 properties
const myObj = {
property1: 1,
property2: "two"
property3: [1,1,1]
}
// Constant means you cannot change the type
myObj = "hello" // ERROR!
// But you can change its properties
myObj.property1 = "one" // changed from a number 1 to a string "one"
// And you can add ...
myObj.property4 = "I am number four"
// ... and remove properties
delete myObj.property4
Arrays
// create an empty array
const myArray = []
// create an empty with stuff
const myArrayWithStuff = [1, "two", [1,1,1]]
// you can add things to the end
myArrayWithStuff.push('i am number four')
// or the beginning
myArrayWithStuff.unshift('element 0')
// you can pop things off the end
let lastEl = myArrayWithStuff.pop()
// you can remove & grab the first element
let firstEl = myArrayWithStuff.shift()
// and you can clear it
myArrayWithStuff.length = 0
// you can test a variable to see if it is an array
if(Array.isArray(myArray) {
// yup, its an array
}
It is good practice to check your types
let n = "I am supposed to be a number but a bug in my flows made me a string"
if (typeof n !== 'number') {
n = Number(n)
if (isNaN(n)) {
throw new Error('it is Not a Number')
}
}
One last gotcha - do not rely on typeof without extra checks
For example:
const nObj = null
let prop1
if (typeof nObj === 'object') {
nProp1 = nObj.property1 // ERROR: Uncaught TypeError: Cannot read properties of null (reading 'property1')
}
// leys try again
const nObj = null
let prop1
// if nObj is _something_ AND it is an object...
if (nObj && typeof nObj === 'object') {
nProp1 = nObj.property1
}
and the kicker:
const myArray = []
let element0
if (typeof myArray === 'object') {
element0 = myArray[0] // How did we get here!!!
// myArray is an object?
// YES, arrays are objects.
// You need to use Array.isArray(myArray) to check them
}
// Lets try again
const myArray = []
let element0
// If it IS an array AND it has something in it, then you can access its elements
if (Array.isArray(myArray) && myArray.length > 0) {
element0 = myArray[0] // Only access element if we know there is one
}
All untested and hand written, possible blugs lurk
Thanks Steve a great help and a lot to read there, but I will soon, as I get a bit more time.
I am celebrating ha ha ... a few minutes ago I got Grafana to display Battery and Also DHT temperature in separate graphs from SQLite , still much more to do
But at least I now have, Arduino sending out JSON string ( I nearly typed object, but looked online to check ).
Which via Node-RED goes to SQLite and then to Grafana
Thanks for your help and everyone else that has got me this far.
No doubt I will be back for more advice in the not too distant future
If you didn't get this fixed perhaps you might try moving the task to the stored procedure. MySql offers an "insert or update" and "insert or ignore" option. If you have a potential duplicate entry it will just discard it.
All untested and hand written, possible blugs lurk 
