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

**URL:** <https://discourse.nodered.org/t/how-to-get-last-inserted-record-in-table-from-code-sqlite/58816>\
**Category:** General\
**Tags:** database\
**Created:** [22 February 2022 13:26 UTC](https://discourse.nodered.org/t/how-to-get-last-inserted-record-in-table-from-code-sqlite/58816 "2022-02-22T13:26:45Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![dm2jokes](https://avatars.discourse-cdn.com/v4/letter/d/8e7dd6/32.png) [@dm2jokes](https://discourse.nodered.org/u/dm2jokes)\
**Post date:** [22 February 2022 13:26 UTC](https://discourse.nodered.org/t/how-to-get-last-inserted-record-in-table-from-code-sqlite/58816/1 "2022-02-22T13:26:45Z")

</div>

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

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

}

```

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [22 February 2022 14:16 UTC](https://discourse.nodered.org/t/how-to-get-last-inserted-record-in-table-from-code-sqlite/58816/2 "2022-02-22T14:16:08Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![dm2jokes](https://avatars.discourse-cdn.com/v4/letter/d/8e7dd6/32.png) [@dm2jokes](https://discourse.nodered.org/u/dm2jokes)\
**Post date:** [22 February 2022 14:48 UTC](https://discourse.nodered.org/t/how-to-get-last-inserted-record-in-table-from-code-sqlite/58816/3 "2022-02-22T14:48:59Z")

</div>

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

In this moment I'm on mobile phone.

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [22 February 2022 14:57 UTC](https://discourse.nodered.org/t/how-to-get-last-inserted-record-in-table-from-code-sqlite/58816/4 "2022-02-22T14:57:05Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![dm2jokes](https://avatars.discourse-cdn.com/v4/letter/d/8e7dd6/32.png) [@dm2jokes](https://discourse.nodered.org/u/dm2jokes)\
**Post date:** [22 February 2022 15:12 UTC](https://discourse.nodered.org/t/how-to-get-last-inserted-record-in-table-from-code-sqlite/58816/5 "2022-02-22T15:12:55Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![dm2jokes](https://avatars.discourse-cdn.com/v4/letter/d/8e7dd6/32.png) [@dm2jokes](https://discourse.nodered.org/u/dm2jokes)\
**Post date:** [22 February 2022 17:45 UTC](https://discourse.nodered.org/t/how-to-get-last-inserted-record-in-table-from-code-sqlite/58816/6 "2022-02-22T17:45:26Z")

</div>

Here is a function.

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

```

---

<div class="post-metadata">

**Author:** ![dm2jokes](https://avatars.discourse-cdn.com/v4/letter/d/8e7dd6/32.png) [@dm2jokes](https://discourse.nodered.org/u/dm2jokes)\
**Post date:** [22 February 2022 17:56 UTC](https://discourse.nodered.org/t/how-to-get-last-inserted-record-in-table-from-code-sqlite/58816/7 "2022-02-22T17:56:29Z")

</div>

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

[flow\_gps\_tracking\_out.json](https://discourse.nodered.org/uploads/short-url/ryccDGDuAvXOT4kqTqjC2pfn9SM.json) (68.2 KB)

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [22 February 2022 23:26 UTC](https://discourse.nodered.org/t/how-to-get-last-inserted-record-in-table-from-code-sqlite/58816/8 "2022-02-22T23:26:41Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![dm2jokes](https://avatars.discourse-cdn.com/v4/letter/d/8e7dd6/32.png) [@dm2jokes](https://discourse.nodered.org/u/dm2jokes)\
**Post date:** [23 February 2022 04:39 UTC](https://discourse.nodered.org/t/how-to-get-last-inserted-record-in-table-from-code-sqlite/58816/9 "2022-02-23T04:39:25Z")

</div>

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

 ![Save_to_DB](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/2/92722ca9ee7df47053a05bc28950b50de257c22b.png)  
[flow\_gps\_tracking\_out.json](https://discourse.nodered.org/uploads/short-url/3HnnhEYhIuYTzGyk9Qq9Ucofik3.json) (68.6 KB)

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [23 February 2022 09:29 UTC](https://discourse.nodered.org/t/how-to-get-last-inserted-record-in-table-from-code-sqlite/58816/10 "2022-02-23T09:29:38Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![dm2jokes](https://avatars.discourse-cdn.com/v4/letter/d/8e7dd6/32.png) [@dm2jokes](https://discourse.nodered.org/u/dm2jokes)\
**Post date:** [23 February 2022 17:41 UTC](https://discourse.nodered.org/t/how-to-get-last-inserted-record-in-table-from-code-sqlite/58816/11 "2022-02-23T17:41:15Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [23 February 2022 18:32 UTC](https://discourse.nodered.org/t/how-to-get-last-inserted-record-in-table-from-code-sqlite/58816/12 "2022-02-23T18:32:31Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![dm2jokes](https://avatars.discourse-cdn.com/v4/letter/d/8e7dd6/32.png) [@dm2jokes](https://discourse.nodered.org/u/dm2jokes)\
**Post date:** [24 February 2022 11:28 UTC](https://discourse.nodered.org/t/how-to-get-last-inserted-record-in-table-from-code-sqlite/58816/13 "2022-02-24T11:28:55Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [25 April 2022 11:29 UTC](https://discourse.nodered.org/t/how-to-get-last-inserted-record-in-table-from-code-sqlite/58816/14 "2022-04-25T11:29:50Z")

</div>

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