Android Question SQLite: last_insert_rowid() [solved]

[Didier9]

I am trying to use this tip http://www.sliqtools.co.uk/blog/tec...ecial SQL,executing the last_row_id() command.
to return the rowid of a newly inserted record in an SQLite database.
Not sure how to make that work.
I am trying this command:

Cursor1 = SQL1.ExecQuery( "SELECT last_insert_rowid()" )

The command does execute (at least does not crash) but SQL1.ExecQuery() returns a cursor. How do I get an integer out of it?

 

[Mahares]

The command does execute (at least does not crash) but SQL1.ExecQuery() returns a cursor. How do I get an integer out of it?

Dim MaxRec As Int  'or long
MaxRec=SQL1.ExecQuerySingleResult("SELECT last_insert_rowid() FROM mytable" )
Log(MaxRec)

 

[RichardN]

Or alternatively...

SELECT Max(rowid) FROM MyTable

...As it was the last one added

 

[Albert Kallal]

I often have to get the last ID, so I use say a code snip like this:

       Dim InvoiceIDA As Long = 0

        ' create a new invoice
        Dim MyParms As List
        MyParms.Initialize
        MyParms.Add(gID)
        MyParms.Add(BillRate)
        sqlite.ExecNonQuery2("INSERT INTO Invoices (CustomerIDA, BillRate) VALUES(?,?)",MyParms)
        InvoiceIDA = MyCode.GetLastID

So, I was a bit tired of writing the same code over and over to get that last ID from a insert.

The code for "GetLastID" in my global "general" code module is thus this:

Sub GetLastID As Long

    ' get laste database insert row ID
    Dim PK As Long = 0
    
    Dim rset As ResultSet = Main.sqlite.ExecQuery("SELECT last_insert_rowid() AS ID")
    If rset.NextRow Then
        PK = rset.GetLong("ID")
    End If
    rset.Close
    
    Return PK
    
End Sub

Now, I adopt a standard in my main module that the database object is "sqlite". If you don't have that global database object/handle, then you could I suppose modify the above to include/pass the database object sqlite in above. So in my first "main" routine/activity - I have a global database object "sqlite". I also alias the column name last_insert_rowid() to "ID", and that was done since you can't get columns by their index (say 0), but have to use name.

So, I have general routine - write it once, and then use it for all cases in which I need/require the last inserted auto number PK id. You can use max() as some suggest - but I tend to avoid that approach, since I might be use some custom incrementing code for the last insert (and below the max value).

As a result, such a number not always going to be the max most high value - so I suggest last_insert_rowid() over that of using max() function. And of course I also suggest to write this code one time and use it over and over for the whole application.

Regards,
Albert D. Kallal
Edmonton, Alberta Canada

 

[Didier9]

Thank you all for the quick responses. Mahares' was first and works just fine so I have not tried the others.
It also works when modifying a record, so that's a plus.

I have been using/improving this app for several years but I did not know this particular function was available. It will make my app much more convenient.

Thanks a lot!

 

[Didier9]

Now, I adopt a standard in my main module that the database object is "sqlite". If you don't have that global database object/handle, then you could I suppose modify the above to include/pass the database object sqlite in above.

I do something similar, I have SQL1 actually declared in the Starter module so I can access it from all activities.

 

[Mahares]

Or alternatively...
SELECT Max(rowid) FROM MyTable

It is not always the case that Max(rowid) is equal to last_insert_rowid(). Case in point, Suppose you have set ID INTEGER PRIMARY KEY in the structure syntax.
The last record has ID=500, If I insert a new record with explicitly putting ID=200 because the number 200 was available, the Max(rowid) would show 500, but the last_insert_rowid().would show 200. But, if I leave the ID as Null in the INSERT statement, the two would be the same.

I also alias the column name last_insert_rowid() to "ID", and that was done since you can't get columns by their index (say 0)

In your example, you can get the column by its index. Your code would look like this:

Dim rset As ResultSet = sql.ExecQuery("SELECT last_insert_rowid() AS ID ") 
    If rset.NextRow Then
        Log($"Albert:  ${rset.GetLong2(0)} "$)  'works same as rset.GetLong("ID")
    End If
    rset.Close

 

[Albert Kallal]

rset.GetLong2(0)

Very nice -

That use of "alias" to ID certainly is ("was") a way to get around not realizing that column index by number refs do exist.

Not the end of the world. But somewhere along the way I must have missed this ability - so my bad here.

However, to be 100% clear as pointed out above?

You CAN reference the columns by index, and I think using 0 as per above is a better choice and double so since no alias column is required either.

Regards,
Albert D. Kallal
Edmonton, Alberta Canada