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

dm2jokes · 22 February 2022 13:26

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



}

zenofmud · 22 February 2022 14:16

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

dm2jokes · 22 February 2022 14:48

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

In this moment I'm on mobile phone.

zenofmud · 22 February 2022 14:57

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

dm2jokes · 22 February 2022 15:12

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

dm2jokes · 22 February 2022 17:45

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];

dm2jokes · 22 February 2022 17:56

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

flow_gps_tracking_out.json (68.2 KB)

zenofmud · 22 February 2022 23:26

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.

dm2jokes · 23 February 2022 04:39

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)

zenofmud · 23 February 2022 09:29

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.

dm2jokes · 23 February 2022 17:41

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

zenofmud · 23 February 2022 18:32

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

dm2jokes · 24 February 2022 11:28

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

system · 25 April 2022 11:29

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

Topic		Replies	Views
Save multiple inputs Sqlite - undefined General database	9	319	23 September 2022
SQLite insert problem General	9	593	22 August 2019
MySQL (Mariadb) INSERT returns msg.payload = "[object Object]" General database	6	334	5 January 2023
Receiving data from multiple nodes and uploading it to sqlite database General	13	1213	1 February 2021
Getting Flow variables into SQLite INSERT General sqlite	14	156	12 June 2024

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

Related topics