FMDB lastinsertRowID always 0

Question

Hya.

I have implemented FMDB in my app. I am trying to get the last row id from one of my databases with this

FMDatabase *bdb=[FMDatabase databaseWithPath:databasePath];
    NSString *str;
    if([bdb open]){
        int lastId=[bdb lastInsertRowId];
        FMResultSet *s = [bdb executeQuery:[NSString stringWithFormat:@"SELECT budget FROM data WHERE ROWID='%d'",lastId]];
        if([s next])
        {
            str= [s stringForColumnIndex:0];
        }
    }

the problem i have is that lastId is always 0 , although there are 3 entries currently in the database. Any1 have any idea why is this happening?

EDIT: the database is created like this:

NSFileManager *filemgr = [NSFileManager defaultManager];
    if ([filemgr fileExistsAtPath: databasePath ] == NO)
    {
        const char *dbpath = [databasePath UTF8String];

        if (sqlite3_open(dbpath, &basicDB) == SQLITE_OK)
        {
            char *errMsg;
            const char *sql_stmt = "CREATE TABLE IF NOT EXISTS DATA (ROWID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT, BUDGET INTEGER)";

            if (sqlite3_exec(basicDB, sql_stmt, NULL, NULL, &errMsg) != SQLITE_OK)
            {
               DLog(@"Failed to create table");
            }
           sqlite3_close(basicDB);

        } else {
           DLog(@"Failed to open/create database");
        }
    }

Clotildecloture

Answer 1

lastInsertRowId works on a specific connection. The connection that you're using has just been opened, so there is no inserted row ID.

(The purpose of lastInsertRowId is to allow your app to know the ID of a record that has just been INSERTed.)

To read data from the last record, use something like this:

SELECT budget FROM data WHERE rowid = (SELECT max(rowid) FROM data)

Niigata

Aha! Thank you for your explanation. But supposing i would not use FMDB but SQLITE API , would select_last_row_id() behave in a similar fashion? – Clotildecloture

Yes; that's how lastInsertRowId is implemented. – Niigata

so how does an INSERT query know to insert a row at a corresponding rowid? i mean sure, the rowid is set to autoincrement, but in a supposed exampple like this: the database contains 3 items, whith rowids 1,2 and 3. you insert a fourth item; sql puts it at rowid 4; then you delete the new row; after that you do another INSERT; the new row will have rowid 5, even though at the time of the last INSERT ,the max_rowid was 3. – Clotildecloture

Thank you very much. The document you linked was revealing as to how things work. Now this sqlite_sequence - how can it be accessed in fmdb? smth like SELECT seq FROM sqlite_sequence WHERE name='table_name' ? – Clotildecloture

Haven't tried it yet; irght now i used the max(rowid) But i'll try it and post the result here for future reference. Thanks again for all your info - really really good! – Clotildecloture

@landonandrey To ask a question, use the "Ask Question" button. – Niigata