How to get last inserted record in table from code - sqlite

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": ""
        });



}

Is that your full function? I don't see where you have defined output.

Sorry, last line missing and this is :
return [output];

In this moment I'm on mobile phone.

But you still haven’t shown the full function.
(And I’m on my mobile right now too :sweat_smile:)

When I will open laptop I send you flow complete.
Regards

Here is a function.

let deviceid = "R16NW";

let myssid = context.get("ssid");
if (myssid===undefined) {
    myssid = "";
}

let sql = "";
let d = new Date();
let epoch = d.getTime();
let output =[];
let insert = [];
let i = 0;
let length = 0;
let my_i = 0;
let query = "";
let name = "";
let lastid = 0;
let no_of_loc = msg.payload.Locations.length;
flow.set("no_of_loc",no_of_loc);

//let lt = 121;
//flow.set("aaa", lt);
//node.status(" AAA - 0 ");

//flow.set("lt", flow.get("lastTrackID"));

if (flow.get("lastTrackID") != 0 )
{
    lastid = flow.get("lastTrackID");
    
}
else if (flow.get("lastTrackID") == 0 ){
    
}
else  {
    //node.status(" nothing ");
}

lastid = lastid + 1;
flow.set("lastid",lastid);
let first = true;

// ------------- TRACK START --------------------------------------------------------
// Only for wifi connections
//
for (i = 0; i < msg.payload.NetworkLogs.length; i++) {
    my_i = (msg.payload.NetworkLogs[i].Time / 1000);  // Prvo milisec u sec
    //my_i = my_i + 3600;                             // Dodati 3600 sec za povećanje za 1 sat GMT+1
    my_i = my_i * 1000;                            // Vrati u milisec
    //my_i = msg.payload.NetworkLogs[i].Time;           // Uzmi kao vrijeme u nativnom formatu
    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_idt", my_idt);

    //Track No.: 2 - 18.02.2022 20: 47: 48

    //Za normalni gps na brodu
    // org flow.set("name", "Track No.: " + lastid + " - " + my_idt);
    flow.set("name", "Track started:" + my_idt);

        node.status(" TRACK ID & Name = " + flow.get("name"));
        output.push({
            "topic": "INSERT INTO tracks (deviceid,time,DateTime,name) " +
                "VALUES ('" + deviceid + "'," + my_i + ",'" + my_idt + "', '" + flow.get("name") + "')", "payload": ""
        });

    output.push({
        "topic": "INSERT INTO wifi (deviceid,connected,ssid,time) " +
            "VALUES ('" + deviceid + "'," + (msg.payload.NetworkLogs[i].IsConnected ? 1 : 0) + ",'" + msg.payload.NetworkLogs[i].SSID + "'," + msg.payload.NetworkLogs[i].Time + ")", "payload": ""
    });



}

// ------------------------ 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;
// ------------------------ HERE, BELOW IS POINT
        let aa = "100";
        flow.set("aa", parseInt(aa));
        //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": ""
        });
}

//node.status({fill:"blue",shape:"ring",text:"Records: "+output.length });    
return [output];

Here is flow.
Thanks for your effort.
Regards from
Drazen

flow_gps_tracking_out.json (68.2 KB)

You have multiple function nodes in your flow. Which one has the issue?

I do note that the function node called 'SQL' will not return a message if you press the 'Today' inject node.

Hi, problem is in 'Save to DB' function.
For test variable aa1 = 2 but must be 73. (PNG)


flow_gps_tracking_out.json (68.6 KB)

Please activate the debug node 'Attachement READ' ard run a test. then copy the complete debug message and paste it to a reply so I can have some test data to test with.

Hi zen, I finally make this to work.
Thanks for you effort and I will prepared next question in short time.
Best regards :+1:

Please explain your solution so others can enefit from your knowledge.

Update,
I'm hurry up with conclusion. Problems are identified inside SQL queries with comas an apostrophe, etc...
In this moment first problem is not solved and that is return last inserted row in table in variable as ID (number, integer).
So, first off all I must prepared right test data and check all, soI will give info in couple days.
Regards

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.