SQLite library: lastinsertid ?

[Epfel]

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

 

[Erel]

SQLite includes a function named: last_insert_rowid.
Try: SELECT last_insert_rowid();

 

[klaus]

[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.

[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.

 

[Epfel]

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