27

On iPhone, what's the best way to get the ID of the last inserted row on an SQLite Database using FMDB ?

Is there a better way rather than doing :

SELECT MAX(ID)
BoltClock
  • 630,065
  • 150
  • 1,295
  • 1,284
vdaubry
  • 11,071
  • 7
  • 52
  • 76

4 Answers4

66

If you can guarantee that your ID column is an auto-increment column, MAX(ID) is fine.

But to cover any case, there's a specialized SQLite function called LAST_INSERT_ROWID():

SELECT LAST_INSERT_ROWID();

In FMDB, you use the -lastInsertRowId method (which internally runs the above):

int lastId = [fmdb lastInsertRowId];
BoltClock
  • 630,065
  • 150
  • 1,295
  • 1,284
  • Why [fmdb lastInsertRowId] always returns 0 for me? – landonandrey Mar 12 '16 at 18:06
  • 2
    @landonandrey `lastInsertRowId` works on a specific connection. The connection that you're using has just been opened, so there is no inserted row ID. You can reference this [answer](https://stackoverflow.com/questions/12976670/fmdb-lastinsertrowid-always-0) for more information. – Bernard Dec 29 '19 at 19:09
  • 1
    I found that `LAST_INSERT_ROWID()` is connection specific. So if you're opening/closing your connection it won't work the way you might expect. – Elijah W. Gagne Apr 21 '20 at 01:04
9

The function sqlite3_last_insert_rowid() is what you're looking for. Having just checked out the source code for FMDB, there seems to be a method on FMDatabase called -lastInsertRowId that wraps the function.

Adil Hussain
  • 24,783
  • 20
  • 95
  • 134
JeremyP
  • 80,230
  • 15
  • 117
  • 158
4

For swift 4 use this code 

let lastRowId = sqlite3_last_insert_rowid(db)

for example 

if sqlite3_exec(db, stringSql, nil, nil, nil) != SQLITE_OK {
    let errmsg = String(cString: sqlite3_errmsg(db)!)
    print("error Insert: \(errmsg)")
}

let lastRowId = sqlite3_last_insert_rowid(db);
print(lastRowId) // its gives you last insert id

if sqlite3_finalize(statement) != SQLITE_OK {
    let errmsg = String(cString: sqlite3_errmsg(db)!)
    print("error finalizing prepared statement: \(errmsg)")
}
statement = nil

//*** close data base
if sqlite3_close(db) != SQLITE_OK {
    print("error closing database")
}
db = nil
Bijender Singh Shekhawat
  • 2,707
  • 1
  • 21
  • 31
1

Try the following:

var rowid: Int = Int(contactDB.lastInsertRowId())
shA.t
  • 15,232
  • 5
  • 47
  • 95
Michele Diblasi
  • 67
  • 7