I am getting an error with sqlite3_prepare_v2

Question

I am creating an app, and doing update with sqlite. Here is my piece of code given below:

    NSFileManager *fileMgr = [NSFileManager defaultManager];
    NSString *dbPath = [[[NSBundle mainBundle] resourcePath ]stringByAppendingPathComponent:@"appforoffice.sqlite"];
    BOOL success = [fileMgr fileExistsAtPath:dbPath];
    if(!success)
    {
        NSLog(@"Cannot locate database file '%@'.", dbPath);
    }
    if(!(sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK))
    {
        NSLog(@"An error has occured.");
    }

    const char *sql = "UPDATE settings SET `value`='Off' WHERE `type`=?";
if(sqlite3_prepare_v2(database, sql, -1, &updateStmt, NULL) == SQLITE_OK)
    {
        if(sqlite3_bind_text(updateStmt, 1, [updateType UTF8String], -1, SQLITE_TRANSIENT) == SQLITE_OK)
        {
            if(sqlite3_step(updateStmt) == SQLITE_DONE) {
                NSLog(@"Update Done successfuly");
            }
            else {
                 NSLog(@"Error while updating. '%s'", sqlite3_errmsg(database));
            }

            sqlite3_finalize(updateStmt);
            sqlite3_close(database);
        }
        else
        {
            NSLog(@"Error while binding variable. '%s'", sqlite3_errmsg(database));
        }
    }
    else
    {
        NSLog(@"Error while creating update statement. '%s'", sqlite3_errmsg(database));
    }

But guys, I am not getting any error. But the problem is that, the database table is not being effected by the query. I am sure the query is perfectly alright.

I am confused about the problem, can't find any way to get out of this.

Solution 1

sqlite3_prepare_v2() doesn't return boolean, so testing its return value with ! is wrong. From the reference:

On success, the sqlite3_prepare() family of routines return SQLITE_OK; otherwise an error code is returned.

So your code should look more like this:

if (sqlite3_prepare_v2(database, sql, -1, &updateStmt, NULL) == SQLITE_OK)
{
    if (sqlite3_bind_text(updateStmt, 1, [updateType UTF8String], -1,
        SQLITE_TRANSIENT) == SQLITE_OK)
    {
        ... call update API ...
    }
    else
    {
        NSLog(@"Error while binding variable. '%s'", sqlite3_errmsg(database));
    }
}
else
{
    NSLog(@"Error while creating update statement. '%s'", sqlite3_errmsg(database));
}

EDIT (after the question was updated with the whole query):

I don't like the look of the ` characters in the query; remove them and it should work.

Solution 2

I prefer to use sql command straight forward without any prepare or bind Methods

sqlite3_exec(database,[sql UTF8String],nil,nil,nil);

where sql is an NSString which has the update statement ,Just make sure your db is open

hope that helps

Comments

[Soumalya Banerjee]

I did exactly, what you've told me to do. Now I am not getting an error Error while binding variable. 'bind or column index out of range'.

[Soumalya Banerjee]

When I am using sqlite3_exec, I am not getting any error, it is not taking any effect, i.e. update query is not running. But the query is perfectly alright.

[trojanfoe]

OK, the bind column indexes start at 0; change 1 in the statement to 0.

[Soumalya Banerjee]

I solved the error, there was a silly mistake in my query. Now I am not getting any error. But the main issue is, update query is not running, i.e. databse table is not effected. But the query is perfectly alright.

[Soumalya Banerjee]

I have edited my question, after editing my code, as you suggested.

[Soumalya Banerjee]

Yes because, using 0, gives same error Error while binding variable. 'bind or column index out of range', when making it 1 is solving the error. And I think 1 refers to the first position.

[Soumalya Banerjee]

I have re-edited the code, and now here is my entire code given with my question.

[trojanfoe]

@SoumalyaBanerjee My comment about column indexes starting at 0 was wrong, as you say, they start at 1.

[M.Othman]

am using it always and it works , make sure you opened the database 'if(sqlite3_open([_dbPath UTF8String],&database)==SQLITE_OK)'

[CoDe]

@trojanfoe I also facing similar issue mention here stackoverflow.com/questions/39916124/… Could you please check.