Hi!
Well my problem is that cannot get value of inserted ID after insert row in table Tracks. I try to get value in variable last_inserted but nothing happens. ID need for later in last two sql queries.
Thanks for help
Regards
// ------------------------ LOCATIONS ------------------------------------------------------------
for (let i=0;i<msg.payload.Locations.length;i++) {
my_i = msg.payload.Locations[i].Time;
var my_idt = "";
// format the unix timestamp to dd.mm.yyyy hh:mm:ss format
var now = new Date();
now.setTime(my_i);
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();
my_idt = dd + "." + mm + "." + yyyy + " " + hh + ":" + mmm + ":" + ss;
flow.set("my_i", my_i);
flow.set("my_idt", my_idt);
flow.set("name", "Track started:" + my_idt);
if (first){
node.status(" TRACK ID & Name = " + flow.get("name"));
output.push({
"topic": "INSERT INTO tracks (deviceid,time,DateTime,name,latitude_start,longitude_start) " +
"VALUES ('" + deviceid + "'," + my_i + ",'" + my_idt + "', '" +
flow.get("name") + "'," + msg.payload.Locations[i].Latitude + "," +
msg.payload.Locations[i].Longitude + ")", "payload": ""
});
first = false;
let aa = "100";
flow.set("aa", parseInt(aa));
// ------------- HERE
//Get last inserted ID in tracks
output.push({
"topic": "SELECT id AS last_inserted FROM tracks ORDER BY id DESC LIMIT 1", "payload": ""
});
aa = msg.payload.last_inserted;
flow.set("aa",aa);
}
if (i == no_of_loc){
// get last record from Locations: lat,lon and update tracks stop's
output.push({
"topic": "UPDATE tracks SET (latitude_stop = " + msg.payload.Locations[i].Latitude +
", longitude_stop = " + msg.payload.Locations[i].Longitude +
") WHERE id = " + flow.get("aa") , "payload": ""
});
}
output.push({"topic": "INSERT INTO gps (deviceid,longitude,latitude,accuracy,altitude,speed,time,trackid,dtm) " +
"VALUES ('" + deviceid + "'," + msg.payload.Locations[i].Longitude + "," + msg.payload.Locations[i].Latitude +
"," + msg.payload.Locations[i].Accuracy + "," + msg.payload.Locations[i].Altitude + "," +
msg.payload.Locations[i].Speed + "," + my_i + "," + flow.get("aa") + ",'" + my_idt + "')", "payload": ""
});
}