SQLite library: lastinsertid ?

E

Epfel

New Member
Licensed User
Longtime User
Hello,

is there any possibility to get the value from the last inserted row out of an auto-incremented column?

Something like the lastinsertid()-command in other programming-languages?

Or is it save to take always the highest number? What about if SQLite re-uses deleted rows?

Hoping for answers
Epfel
 

klaus

Expert
Licensed User
Longtime User
B4X: 
[FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]Cursor1 = SQL1.ExecQuery([/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]"SELECT last_insert_rowid()"[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2])[/SIZE][/FONT][/SIZE][/FONT]
[FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]Cursor1.Position = [/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]0[/COLOR][/SIZE][/FONT]
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]row([/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]0[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]) = Cursor1.GetLong2([/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]0[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2])[/SIZE][/FONT][/SIZE][/FONT]
This code does work, but when you start the program it returns 0.
Only after having inserted a new row into the database you will get the right value.

I used in the SQLiteDB program a column 'Code' as INETEGER PRIMARY KEY and the max() function. So I get the highest rowid which is the last inserted row.
B4X: 
[SIZE=2][FONT=Courier New][SIZE=2][FONT=Courier New]txt = [/FONT][/SIZE][/FONT][/SIZE][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]"SELECT max(Code[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]) FROM "[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2] & DBTableName[/SIZE][/FONT]
[SIZE=2][FONT=Courier New]Cursor1 = SQL1.ExecQuery(txt)[/FONT][/SIZE]
[SIZE=2][FONT=Courier New]Cursor1.Position = [/FONT][/SIZE][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]0[/COLOR][/SIZE][/FONT]
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]row([/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]0[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]) = Cursor1.GetLong2([/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]0[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2])[/SIZE][/FONT][/SIZE][/FONT]

Best regards.
 
E

Epfel

New Member
Licensed User
Longtime User
SQLite library: lastinsertid ? - solved

Hello Klaus, hello Erel,

thanks a lot for your replies.

I've already used the method of Klaus as a workaround. But the method of Erel (which works fine) seems to be a little bit saver - enclosing the INSERT and the SELCT in just one transmission. It's just a feeling, it has to be proved yet.

Thanks again from Germany
Epfel
 
You must log in or register to reply here.
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…