CODEWITHSUNDEEP
iosiphonesqlite
iOSDevp
iOSDevp

Reputation: 195

getting error in sqlite Update in ios

i am getting error in the sqlite updation,here the query is correctly given, but the value is not updating.here i need to update the description value Where the given place, Below i add my code please help me 

-(void) update:(NSString *)filePath withName:(NSString *)place description:(NSString*)description
{
    sqlite3* db = NULL;
    int rc=0;
     sqlite3_stmt* stmt =NULL;

rc = sqlite3_open_v2([filePath cStringUsingEncoding:NSUTF8StringEncoding], &db, SQLITE_OPEN_READWRITE , NULL);
if (SQLITE_OK != rc)
{
    sqlite3_close(db);
    NSLog(@"Failed to open db connection");
}
else
{
    NSString * query  = [NSString stringWithFormat:@"UPDATE  places SET description=%@ WHERE place=%@",description,place];

    sqlite3_bind_text(stmt, 1, [place UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 2, [description UTF8String], -1, SQLITE_TRANSIENT);

    NSLog(@"query %@",query);
    char * errMsg;
    rc = sqlite3_exec(db, [query UTF8String] ,NULL,&stmt,&errMsg);
    if(SQLITE_OK != rc)
    {
        NSLog(@"Failed to insert record  rc:%d, msg=%s",rc,errMsg);
    }
    else{
    NSLog(@"Sucessfully updated");
    }
    sqlite3_finalize(stmt);
    sqlite3_close(db);
 }
}

Table Creation

-(int) createTable:(NSString*) filePath
{
    sqlite3* db = NULL;
    int rc=0;
NSLog(@"create");
rc = sqlite3_open_v2([filePath cStringUsingEncoding:NSUTF8StringEncoding], &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
if (SQLITE_OK != rc)
{
    sqlite3_close(db);
    NSLog(@"Failed to open db connection");
}
else
{
    char * query ="CREATE TABLE IF NOT EXISTS places ( id INTEGER PRIMARY KEY AUTOINCREMENT,place TEXT ,locationname TEXT,time TEXT,description TEXT,notifyTime TEXT,radius TEXT,lat DOUBLE,lon DOUBLE ,notify TEXT,selection INTEGER)";
    char * errMsg;
    rc = sqlite3_exec(db, query,NULL,NULL,&errMsg);

    if(SQLITE_OK != rc)
    {
        NSLog(@"Failed to create table rc:%d, msg=%s",rc,errMsg);
    }
    else{
        NSLog(@"Sucessfully Created ");
    }

    sqlite3_close(db);
}
return rc;

}

Upvotes: 0

Views: 761

Answers (3)

Stephen Darlington
Stephen Darlington

Reputation: 52565

You're using prepared statements incorrectly. You hard-code the values in a string (a bad idea) and then setting the placeholder values -- which makes no sense as there are no placeholders in your SQL. Try something like:

NSString * query  = @"UPDATE  places SET description= ? WHERE place= ?";

sqlite3_bind_text(stmt, 1, [place UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 2, [description UTF8String], -1, SQLITE_TRANSIENT);

The reason you don't want to include the values directly in the query string: imagine someone passing in a value for place like "London'; drop table places;". (Not sure if that'll work but it should give you some idea of the kind of mischief that a user could get up to.)

Upvotes: 0

karthikeyan
karthikeyan

Reputation: 3888

try this ...check db is whether it is opened or not and change this code...

 -(void) update:(NSString *)filePath withName:(NSString *)place description:(NSString*)description
    {
        sqlite3* db = NULL;
        int rc=0;
        sqlite3_stmt* stmt =NULL;

        rc = sqlite3_open_v2([filePath cStringUsingEncoding:NSUTF8StringEncoding], &db, SQLITE_OPEN_READWRITE , NULL);
        if (SQLITE_OK != rc)
        {
            sqlite3_close(db);
            NSLog(@"Failed to open db connection");
        }
        else
        {
            /*
             NSString  *strMQueryupdate=[NSString stringWithFormat:@"update br set TOTAL='%@',QUTY='%@' WHERE PID='%@'",[@(total )stringValue],[@(lblQtn )stringValue],produdId];
             */

          NSString * query  = [NSString stringWithFormat:@"UPDATE  places SET description='%@' WHERE place='%@'",description,place];
            const char *sql = [query UTF8String];

            if (sqlite3_prepare_v2(db, sql, -1, & stmt, NULL) != SQLITE_OK)
            {
                NSLog(@"update fails");
            }
            else
            {
                sqlite3_bind_text(stmt, 1, [place UTF8String], -1, SQLITE_TRANSIENT);
                sqlite3_bind_text(stmt, 2, [description UTF8String], -1, SQLITE_TRANSIENT);

                int success = sqlite3_step(stmt);
                sqlite3_reset(stmt);
                if (success == SQLITE_ERROR)
                {

                }
                else
                {
                    NSLog(@"update success");

                }
                sqlite3_finalize(update_statement);
                sqlite3_close(db);
            }
    }

Upvotes: 0

Pradip Vanparia
Pradip Vanparia

Reputation: 1742

First open the DB connection. Then write below code.

               NSString * query  = [NSString stringWithFormat:@"UPDATE  places SET description=%@ WHERE place=%@",description,place];
               SQL=[NSString stringWithString:query];
                sqlite3_stmt *insert_statement=nil;

                if (sqlite3_prepare_v2(database, [SQL UTF8String], -1, &insert_statement, NULL) != SQLITE_OK) {
                    //NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
                    NSLog(@"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
                }

                int result=sqlite3_step(insert_statement);
                sqlite3_finalize(insert_statement);

and then close the DB connection

Upvotes: 1

Related Questions