Get rowID of record being INSERTED into SQLite database

[Columbo]

When I INSERT a new record into an SQLite database, is there a way to get the rowID of that record?

[NicTheQuick]

Code: Select all

select last_insert_rowid();

Source: https://alvinalexander.com/android/sqli ... imary-key/

[Columbo]

Thank you but, could you please give me an idea as to where in my database query I would put that?

My query is:

Code: Select all

result = DatabaseUpdate(#dbaseID, "INSERT INTO command " +
                                    "(category, cmd, syntax) " +
                                    "VALUES (?, ?, ?);")

Thanks.

[NicTheQuick]

Just call it after your update. I never made anything with databases in Purebasic I guess it should look something like this:

Code: Select all

DatabaseQuery(#dbaseID, "select last_insert_rowid();"
If NextDatabaseRow(#dbaseID)
	Debug GetDatabaseLong(#dbaseID, 0)
EndIf

[Columbo]

That works. Thank you so much.

[kpeters58]

Alternatively you can get it first and then use it in your insert statement [either way, you have two DB round trips ]:

Code: Select all

  Procedure.l GetNextAutoIncIDForTable(DatabaseHandle, TableName.s)
    Protected expr.s = "select seq + 1 as NextID from sqlite_sequence where lower(name) = lower(" + QuotedStr(TableName.s) + ")", result = -1
    
    If DatabaseQuery(DatabaseHandle, expr) 
      If NextDatabaseRow(DatabaseHandle) 
        Result = GetDatabaseLong(DatabaseHandle, 0)
      Else ; no entry in sequence table yet
        Result = 1
      EndIf
      FinishDatabaseQuery(DatabaseHandle)
    Else
        ; handle query error here
    EndIf  
    ProcedureReturn result
  EndProcedure

[loulou2522]

When I INSERT a new record into an SQLite database, is there a way to get the rowID of that record?

Here is my answer

The rowid of the last inserted row can be selected with the last_insert_rowid() function:
Here is an example in sql

Code: Select all

UseSQLiteDatabase()
If FileSize(GetTemporaryDirectory()+"toto.db") <> -1  And  FileSize(GetTemporaryDirectory()+"toto.db") <> -2  
  DeleteFile(GetTemporaryDirectory()+"toto.db")
EndIf 
If FileSize(GetTemporaryDirectory()+"toto.db") = -1  
  CreateFile(0, GetTemporaryDirectory()+"toto.db", #PB_UTF8   )
  CloseFile(0)
EndIf 

Global    ouverture.l =OpenDatabase(#PB_Any, GetTemporaryDirectory()+"toto.db" , "", "", #PB_Database_SQLite )

Requete.s = "CREATE TABLE Cats( CatId INTEGER PRIMARY KEY, CatName);INSERT INTO Cats VALUES ( NULL, 'Brush' ),( NULL, 'Scarcat' ), ( NULL, 'Flutter' )"
DatabaseUpdate(ouverture,requete) 
;==================================== 
; This instruction after an insert give the result you search 
;====================================    
DatabaseQuery(ouverture,"Select last_insert_rowid();")
NextDatabaseRow(ouverture)
Debug GetDatabaseLong(ouverture,0)
FinishDatabaseQuery(ouverture)
CloseDatabase(ouverture)