How to update an entry in sqlite3?

Question

Im super new to ios (Objective-C) and sql and Im following a tutorial of sqlite (specifically sqlite3) found here: http://www.tutorialspoint.com/ios/ios_sqlite_database.htm and i implemented everything correctly. The problem is that if I try to enter information with the same reg id (The primary key which is used to find elements in the database), it complains about the key not being unique.

- (BOOL) saveData:(NSString*)registerNumber name:(NSString*)name
   department:(NSString*)department year:(NSString*)year;
    {
    const char *dbpath = [databasePath UTF8String];
    if (sqlite3_open(dbpath, &database) == SQLITE_OK)
    {
        NSString *insertSQL =
        [NSString stringWithFormat:@"insert into studentsDetail (regno,name, department, year) values (\"%d\",\"%@\", \"%@\", \"%@\")",[registerNumber integerValue], name, department, year];

        const char *insert_stmt = [insertSQL UTF8String];
        sqlite3_prepare_v2(database, insert_stmt,-1, &statement, NULL);
        int s = sqlite3_step(statement);
        const char *errmsg = sqlite3_errmsg(database);
        if (s == SQLITE_DONE)
        {
            sqlite3_reset(statement);
            return YES;
        }
        else {
            sqlite3_reset(statement);
            return NO;
        }
    }
    return NO;
}

How do I go about making the saveData: action actually update the entry inside the database instead of just being rejected? Thanks!

Numeric values do not go in quotes. And string values must be in single quotes, not double quotes. But more importantly, do not use `stringWithFormat` to build your queries. Properly bind the values using `sqlite3_bind_xxx`. — rmaddy, Oct 10 '13 at 18:33
You also need to close the database since you open it every time. You should also finalize the statement. There is no need to reset the statement because you are not reusing them. You also need to check the return value of `sqlite3_prepare_v2`. Use `sqlite3_errmsg()` to check any failed call. — rmaddy, Oct 10 '13 at 18:34
@rmaddy the code works as it was originally intended to do, to introduce a simple sql database structure but I want to extend it so that if I try to enter an existing reg id, it will overwrite the remaining properties with the new ones. Ill look into using the binding function but in terms of the format I'm fairly certain that for obj-c I'm using the right syntax for string and numbers, but correct me if I am wrong. — CoderNinja, Oct 10 '13 at 18:41
Use FMDatabase framework, is to much easy insert values in a table. — Nekak Kinich, Oct 10 '13 at 18:46

score 0 · Accepted Answer · edited May 23 '17 at 12:10

0

"I want to extend it so that if I try to enter an existing reg id, it will overwrite the remaining properties with the new ones"

If this is what you are trying to accomplish then you should not be using an INSERT statement. The purpose of the primary key is to protect the database from duplicate rows with the same primary key. When you look at it that way, the rejection is appropriate.

What you really want is to use INSERT OR REPLACE

If you're a beginner to both sql and objective c, I'd recommend a sqlite wrapper I created that is aimed at beginners: TankDB

Edit:

Code example:

INSERT OR REPLACE INTO studentsDetail (regno,name, department, year) values (\"%d\",\"%@\", \"%@\", \"%@\")"

Refer to this question to make sure that you don't actually mean to use 'UPSERT'. Beware that INSERT OR REPLACE does not update the existing row with the new information. It will completely delete the existing row and replace it.

Edit2: Open, prepare, step, finalize, close

const char *dbpath = [_databasePath UTF8String];
if(sqlite3_open(dbpath, &_database) == SQLITE_OK) {
    const char *command = [query UTF8String];

    // Perform the query
    if (sqlite3_prepare_v2(_database, command, -1, &selectstmt, NULL) == SQLITE_OK) {

        // For each row returned
        while (sqlite3_step(selectstmt) == SQLITE_ROW) {
            // Do stuff
        }
    }
    sqlite3_finalize(selectstmt);
}

sqlite3_close (_database);

edited May 23 '17 at 12:10

Community

1
1

answered Oct 11 '13 at 22:09

jnortey

1,625
2
10
17

how do i change my code so that it uses REPLACE? The insert_stmt? – CoderNinja Oct 13 '13 at 01:59
If INSERT OR REPLACE merely deletes and reinserts, the primary key changes but other than that, the functionality is the same? This is exactly what i was looking for, thanks for helping my realize what I was doing wrong – CoderNinja Oct 17 '13 at 06:05
No problem. It would be a good idea to mark the answer as the accepted answer if it helped solve your problem. – jnortey Oct 17 '13 at 13:08
I tried replace my insert with INSERT OR REPLACE and now I get an errmsg of database is locked. Do you know why that would happen? – CoderNinja Oct 17 '13 at 14:12
Yes, as rmaddy mentioned above, you are never finalizing your statement or closing your database. You need to make sure that at the end of every call to saveData, the database is closed. Be careful when you add the close, because you have multiple return statements in your function. You need to make sure that the close statement is called regardless of which branch your code takes. – jnortey Oct 17 '13 at 14:30
How does one finalize the sqlite statement/ what is the exact purpose of finalizing it? – CoderNinja Oct 17 '13 at 15:30
I have edited my answer with the correct way to close and finalize. If you have more questions, it would be best to open a separate question as we are now leaving the scope of the original question. For info on why you must finalize visit here: http://www.sqlite.org/c3ref/finalize.html – jnortey Oct 17 '13 at 15:41

How to update an entry in sqlite3?

1 Answers1