Hi there,
running Node-Red on an onlineserver of digitalocean.
I am taking data from a sqlite DB (Select all db entries) , calculating some stats with it
then trying to create a string/ variable which holds this new data;
and than insert this data into an existing (but empty) column "Status" of table "gps" of an sqlite DB "tracker.db.
All this is done in the function "Add Status to DB".
//(length-4 because it needs to go 4 entries ahead to see UNIX time difference
for (var i=0; i<msg.length-4; i++) {
var lat_t= msg.payload[i].Latitude;
var lat_t_1= msg.payload[i][i+1].Latitude;
var dif_lat= Math.abs(lat_t)-Math.abs(lat_t_1)
var lon_t= msg.payload[i].Longitude;
var lon_t_1= msg.payload[i+1].Longitude;
var dif_lon= Math.abs(lon_t)-Math.abs(lon_t_1)
var dif_tot= Math.sqrt((Math.abs(dif_lat)-Math.abs(dif_lon))**2)*90000
if (dif_tot > max_dist) {
stat=1;
}
// declare trip end only after 10 min halt within range of 150m (factor 90'000)
else if (((msg.payload[i+1].UNIX - msg.payload[i].UNIX) > 1000*60*10) ||((msg.payload[i+2].UNIX - msg.payload[i].UNIX) > 1000*60*10)||((msg.payload[i+3].UNIX - msg.payload[i][i].UNIX) > 1000*60*10)||((msg.payload[i+4].UNIX - msg.payload[i].UNIX) > 1000*60*10)){
stat=0.5; }
else
{
stat=0;
}
msg.payload[i].Status=stat;
//msg.topic = "INSERT INTO gps (Status) VALUES (stat)";
}
for (var j=msg.length-4; j<=5; j++) {
stat=2;
msg.payload[j].Status=stat;
}
msg.topic = "INSERT INTO gps (Status)" +
"Values ("+msg.payload.Status+")";
Many thanks for some suggestions and just to say, I am relatively new to node-red but went through a good amount of videos and forum chats but couldn't really solve this,
Jacob
SQL is complaining because it does not know what to do with the word undefined.
This is probably a strong hint that your calculations are incorrect.
If you were to change msg.topic to insert into gps(Status)Values(42); or perhaps insert into gps(Status)Values("DentArthurDent"); it would probably work.
That said, I'm puzzled that you are inserting a record with just one field into gps, a table that has 22 fields and at least 99 records, as opposed to updateing a specific record (where id = 1039)
msg.topic = "INSERT INTO gps (Status)" + "Values ("+msg.payload.Status+")";
Shouldnt this msg.payload.Status above be msg.payload[index].Status with the index relating to which entry in the array you want to update.
I looks like you are looping round all the entries in the array from the select statement, updating their status to 2 in the array and then trying to insert a single row in the "gps" table with a non existent msg.payload.Status entity.
Thanks both, that were some really useful hints!!!!!
I wasn't aware of the existence of "update" so using this now and its doing what I want, I checked for single values and that is working. I however remain with the problem, that I want to update the entire column of the table with values calculated in the function.
var max_dist = 150; //meter
var stat= 0;
for (var i=0; i<msg.length-4; i++) {
var lat_t= msg.payload[i].Latitude;
var lat_t_1= msg.payload[i][i+1].Latitude;
var dif_lat= Math.abs(lat_t)-Math.abs(lat_t_1)
var lon_t= msg.payload[i].Longitude;
var lon_t_1= msg.payload[i+1].Longitude;
var dif_lon= Math.abs(lon_t)-Math.abs(lon_t_1)
var dif_tot= Math.sqrt((Math.abs(dif_lat)-Math.abs(dif_lon))**2)*90000
if (dif_tot > max_dist) {stat=1;}
else if (((msg.payload[i+1].UNIX - msg.payload[i].UNIX) > 1000*60*10) ||((msg.payload[i+2].UNIX - msg.payload[i].UNIX) > 1000*60*10)||((msg.payload[i+3].UNIX - msg.payload[i][i].UNIX) > 1000*60*10)||((msg.payload[i+4].UNIX - msg.payload[i].UNIX) > 1000*60*10)){
stat=0.5; }
else {stat=0;}
msg.payload[i].Status=stat;
id_set=msg.payload[i].id;
} // here the loop ends and the calculated values are irrelevant since id_set and stat is given manually
id_set=1211;
stat=10;
msg.topic="UPDATE gps SET Status= "+stat+" WHERE id = "+id_set+";"
return msg;
So I don't think its possible to loop over "msg.topic" and "return msg" to update all the entries ( msg.payload[i].Status or stat) at the position "id_set"?
This maybe refers to this post: Update More than one Value with SQLITE - #3 by ilvesheimer
Anyone an idea how to solve this (I don't think I am able to go the batch way)? Originally with the code I wanted to create a string or array that holds all the values which than is of course as long as the table and than add/ update the entire column of the table with it.
The dialect of SQL I speak is for MySQL so it may be slightly different from SQLITE.
The single query UPDATE table SET fieldname = value will update every record in the table.
It's an unusual thing to want to do though.
UPDATE table SET fieldname = value WHERE DeviceId = "R15NW" will update all records for that device.
UPDATE table SET fieldname = value WHERE id = 1039 will (if id is unique) update just one record.
I don't really understand what you are asking here. But note that once the function node reaches a return statement it will do no further processing till the next message arrives.
It is possible for a function to output multiple messages using for example
let newmsg = {}
newmsg.payload = [msg.payload.status, msg.payload.id] //NB This is a "Prepared Query" and is more secure than putting the values in msg.topic
newmsg.topic = "UPDATE test SET status = ? WHERE id = ?"
node.send(newmsg)
But it's better to update several records together using a less restrictive WHERE clause if your data permits that
Hi jbudd,
so I have basically this table which just looks like the image of the msg.payload I uploaded, only horizontally and each object is a row. I want to calculate the status of my device (is it moving or standing still) and insert this info into the column "Status". I also ran the code in a javascript editor (using an array as table there) where this is working fine.
With looping over the part msg.topic="UPDATE gps SET Status= "+stat+" WHERE id = "+id_set+";" I try to update each newly calculated value at the corrresponding id under the column "status", the values stat and id_set come directly from the loop
id_set=msg.payload[i].id;
This however doesn't seem to work. So another idea was to create and array containing all the values and hence update the entire column "Status". But it seems thats not how javascript/nodered works, I am wondering if msg.topic... is only executed once msg.return is called? As said, looping through each row and updating the value corresponding to that id is how I would normally program it but this passing of information and commands through nodes is rather new to me.
So it took me 2 days but I found the solutions and learned a lot on the way.
it is possible to loop over msg.topic= update or insert....
Working with large arrays is not joke, it is so easy to make a tiny mistake like forgetting some mark adn the mistake is very hard to spot or figure from the debug message
I learned to appricate the "inject" (also called timestamp) node, when having a complex flow I recommend creating a "test" flow for debugging with the injectnode at the beginning to trigger the payload msg. when clicking on it and see what comes out yb adding debug nodes everywhere
I never found this in tutorials but this way its actually rather easy to load an entire database timeseries table into msg.payload (tried this with sqlite and influxdb V2.6) when the inject node contains like this:
msg.topic = "SELECT * FROM gps WHERE UNIX >= "+1000000000000+" ORDER BY id";
return [msg,{"topic": "Loading data", "payload": "Please wait, updating views..."}];
After the database node (sqlite or influx) a function can be added that generates new entries/ column etc. for the table exactly by looping over msg.topic = update or insert
This way all kinds of operations in databases are possible!
My code to update the values now looks like this:
var max_dist = 150; //meter
var stat= 0;
//(length-4 because it needs to go 4 entries ahead to see UNIX time difference
for (var i=0; i<msg.payload.length-4; i++) {
//var j=i+1;
var lat_t= msg.payload[i].Latitude;
var lat_t_1= msg.payload[i+1].Latitude;
var dif_lat= Math.abs(lat_t)-Math.abs(lat_t_1)
var lon_t= msg.payload[i].Longitude;
var lon_t_1= msg.payload[i+1].Longitude;
var dif_lon= Math.abs(lon_t)-Math.abs(lon_t_1)
var dif_tot= Math.sqrt((Math.abs(dif_lat)-Math.abs(dif_lon))**2)*90000
if (dif_tot > max_dist) {
stat=1;
}
// declare trip end only after 10 min halt within range of 150m (factor 90'000)
else if (((msg.payload[i+1].UNIX - msg.payload[i].UNIX) > 1000*60*10) ||((msg.payload[i+2].UNIX - msg.payload[i].UNIX) > 1000*60*10)||((msg.payload[i+3].UNIX - msg.payload[i].UNIX) > 1000*60*10)||((msg.payload[i+4].UNIX - msg.payload[i].UNIX) > 1000*60*10)){
stat=0.5; }
else
{
stat=0;
}
msg.payload[i].Status=stat;
id_set=msg.payload[i].id;
//add human readable time -> check if really necessary?
var now = new Date();
now.setTime(msg.payload[i].UNIX);
var yyyy = now.getFullYear();
var mm = now.getMonth() < 9 ? "0" + (now.getMonth() + 1) : (now.getMonth() + 1); // getMonth() is zero-based
var dd = now.getDate() < 10 ? "0" + now.getDate() : now.getDate();
var hh = now.getHours() < 10 ? "0" + now.getHours() : now.getHours();
var mmm = now.getMinutes() < 10 ? "0" + now.getMinutes() : now.getMinutes();
var ss = now.getSeconds() < 10 ? "0" + now.getSeconds() : now.getSeconds();
msg.payload[i].formatteddate = dd + "." + mm + "." + yyyy + " " + hh + ":" + mmm + ":" + ss ;
