iOS - using sqlite database update data not working

Question

I would like to update some data in Xcode sqlite db. The db is successfully connected, but seems like there's something wrong in the sql statement, it keeps returning "Failed to add contact", thanks for helping.

- (void) saveData:(id)sender
{

    NSLog(@"The code runs through here!");
    sqlite3_stmt    *statement;

    NSString *documents = [self applicationDocumentsDirectory];
    NSString *dbPath = [documents stringByAppendingPathComponent:@"monitorDB.sqlite"];
    const char *dbpath = [dbPath cStringUsingEncoding:NSASCIIStringEncoding];

    if (sqlite3_open(dbpath, & contactDB) == SQLITE_OK)
    {

        NSString *insertSQL = [NSString stringWithFormat:
                               @"UPDATE profile SET username = \"%@\" WHERE id = 1" ,
                               self.username.text];

        const char *insert_stmt = [insertSQL UTF8String];
        sqlite3_prepare_v2(contactDB, insert_stmt,
                           -1, &statement, NULL);
        if (sqlite3_step(statement) == SQLITE_DONE)
        {
            self.settingStatus.text = @"Contact added";
        } else {
            self.settingStatus.text = @"Failed to add contact";
        }
        sqlite3_finalize(statement);
        sqlite3_close(contactDB);
    }   else {
        self.settingStatus.text = @"DB Not Connect";
    }



}

Yet another programmer who doesn't use the error reporting mechanism provided by the API they are using; hint sqlite3_errmsg(). — trojanfoe
– trojanfoe, Commented Sep 10, 2013 at 8:05
NSAssert1(0, @"Error while creating update statement. '%s'", sqlite3_errmsg(contactDB)); — JennyProGram
– JennyProGram, Commented Sep 10, 2013 at 8:14
What's wrong with NSLog()? Why are you using NSAssert()? Asserts are testing developer assertions, not reporting runtime errors. — trojanfoe
– trojanfoe, Commented Sep 10, 2013 at 8:15
And did you copy the database from the app bundle into the documents folder before trying to use it (testing that it doesn't already exist, of course) ? — trojanfoe
– trojanfoe, Commented Sep 10, 2013 at 8:27

kalyani puvvada · Accepted Answer · 2013-09-10 09:29:41Z

Try like this..

In viewdidload we need to check wether table exist or not. If not we need to create db.

NSString *docsdir;
NSArray *dirpaths;

dirpaths=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
docsdir=[dirpaths objectAtIndex:0];
dabasePath=[NSString stringWithFormat:[docsdir stringByAppendingPathComponent:@"contact.db"]];

NSFileManager *filemgr= [NSFileManager defaultManager];
if ([filemgr fileExistsAtPath:dabasePath]==NO ) {
    const char *dbpath=[dabasePath UTF8String];
    if (sqlite3_open(dbpath, &contactDB)== SQLITE_OK) {
        char *error;
        const char *sql_stmt="CREATE TABLE IF NOT EXISTS CONTACTS (ID INTEGER PRIMARY KEY AUTOINCREMENT, ADDRESS TEXT, NAME TEXT, PHONE TEXT, IMAGE BLOB)";
        if (sqlite3_exec(contactDB, sql_stmt, NULL, NULL, &error)!= SQLITE_OK) {
            status.text=@"failed to create";
        }
        sqlite3_close(contactDB);
    }
}

To save data try to use the following code.

-(IBAction)saveData:(id)sender{

    sqlite3_stmt *statement;
    const char *dbpath = [dabasePath UTF8String];
    NSData *imagedata=UIImagePNGRepresentation(imageview.image);
    if (sqlite3_open(dbpath, &contactDB) == SQLITE_OK) {
        NSString *insertSql =[NSString stringWithFormat:@"INSERT INTO CONTACTS (name, address, phone, image) VALUES (\"%@\", \"%@\", \"%@\", ?) ", name.text, address.text, phone.text ];
        // NSString *nam=name.text;
        const char *insert_stmt = [insertSql UTF8String];
        sqlite3_prepare_v2(contactDB, insert_stmt, -1, &statement, NULL);
        sqlite3_bind_blob(statement, 1, [imagedata bytes], [imagedata length], NULL);

        if (sqlite3_step(statement) == SQLITE_DONE) {
          status.text=@"contact added";
          [self clearClick:nil];
        }else{
          status.text=@"failed to added";
        }
        sqlite3_finalize(statement);
        sqlite3_close(contactDB);
    }
}

To update data try to use the following code.

-(IBAction)updateClick:(id)sender{

sqlite3_stmt *updateStmt;
 const char *dbpath = [dabasePath UTF8String];
if(sqlite3_open(dbpath, &contactDB) == SQLITE_OK)
{
    const char *sql = "update contacts Set address = ?, phone = ?, image = ? Where name=?";
    if(sqlite3_prepare_v2(contactDB, sql, -1, &updateStmt, NULL)==SQLITE_OK){
        sqlite3_bind_text(updateStmt, 4, [name.text UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(updateStmt, 1, [address.text UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(updateStmt, 2, [phone.text UTF8String], -1, SQLITE_TRANSIENT);
        NSData *imagedata=UIImagePNGRepresentation(imageview.image);
        sqlite3_bind_blob(updateStmt, 3, [imagedata bytes], [imagedata length], NULL);
    }
}
char* errmsg;
sqlite3_exec(contactDB, "COMMIT", NULL, NULL, &errmsg);

if(SQLITE_DONE != sqlite3_step(updateStmt)){
    NSLog(@"Error while updating. %s", sqlite3_errmsg(contactDB));
}
else{
    [self clearClick:nil];
}
sqlite3_finalize(updateStmt);
sqlite3_close(contactDB);
}

Before doing any transaction with the db,please make sure that you're copying the db from bundle to documents directory(in case it's not present in the directory)
Thank you so much kalyani puvvada.I struggeld with update query for past one month.Even i saw your answer i did not use the code fully.Just now i implemented your update code fully.It works now for me.I am happy now.Thank you.

Rajneesh071 · Accepted Answer · 2013-09-10 10:56:13Z

Check your sql query and change it like this.

NSString *insertSQL = [NSString stringWithFormat:
                           @"UPDATE profile SET username = '%@' WHERE id = 1" ,
                           self.username.text];

Or if you want to do using bind text.

if (sqlite3_open(dbpath, & contactDB) == SQLITE_OK)
{    
    const char *insert_stmt = "UPDATE profile SET username = ? WHERE id = 1";
    if(sqlite3_prepare_v2(contactDB, insert_stmt,
                          -1, &statement, NULL)== SQLITE_OK)
    {
        sqlite3_bind_text(statement, 1, [self.username.text UTF8String], -1, SQLITE_TRANSIENT);
    }

    if (sqlite3_step(statement) == SQLITE_DONE)
    {
        self.settingStatus.text = @"Contact added";
    } else {
        self.settingStatus.text = @"Failed to add contact";
    }
    sqlite3_finalize(statement);
    sqlite3_close(contactDB);
}   else {
    self.settingStatus.text = @"DB Not Connect";
}

iAppDeveloper · Accepted Answer · 2013-09-10 08:44:17Z

0

Just check

   in .h file NSString *databaseName;
            NSString *databasePath;

        and in .m file specify  databaseName = @"Db name";
    NSArray *documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDir = [documentPaths objectAtIndex:0];
    databasePath = [documentsDir stringByAppendingPathComponent:databaseName];

            -(void)save:(id)sender
                {
                    [self checkAndCreateDatabase];
                    sqlite3 *contactDB;
                    sqlite3_stmt *updateStmt;

                    if(sqlite3_open([databasePath UTF8String], &contactDB) == SQLITE_OK)
                    {
                        NSString *querySql=[NSString stringWithFormat:
                                           @"UPDATE profile SET username = \"%@\" WHERE id = 1" ,
                                           self.username.text];
                        const char*sql=[querySql UTF8String];
                        if(sqlite3_prepare_v2(contactDB,sql, -1, &updateStmt, NULL) == SQLITE_OK)
                        {
                            if(SQLITE_DONE != sqlite3_step(updateStmt))
                            {
                                NSLog(@"Error while updating. '%s'", sqlite3_errmsg(contactDB));
                            }
                            else{
                                sqlite3_reset(updateStmt);
                                NSLog(@"Update done successfully!");
                            }
                        }

                        sqlite3_finalize(updateStmt);
                    }
                    sqlite3_close(contactDB);
                }


                -(void) checkAndCreateDatabase{

                    BOOL success;
                    NSFileManager *fileManager = [NSFileManager defaultManager];
                    success = [fileManager fileExistsAtPath:databasePath];
                    if(success) return;

                    NSString *databasePathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:databaseName];
                    [fileManager copyItemAtPath:databasePathFromApp toPath:databasePath error:nil];

                }

edited Sep 10, 2013 at 8:44

answered Sep 10, 2013 at 8:31

iAppDeveloper

1,0881 gold badge8 silver badges16 bronze badges

3 Comments

trojanfoe Over a year ago

Show the code for checkAndCreateDatabase. Stop using NSAssert1 in favour of NSLog.

iAppDeveloper Over a year ago

@jennyProGram just check update statement written is correct, then print the NSLog(@"%@",insertSQL); then run same query in db

trojanfoe Over a year ago

OK looking good. One final thing; use bindings to get the username into the prepared statement, not [NSString stringWithFormat:].

Geekoder · Accepted Answer · 2013-09-10 08:56:47Z

0

There are many possibilities check all of the below:

1) Initialize statement with nil

sqlite3_stmt    *statement = nil;

2) Try below

if (sqlite3_prepare_v2(contactDB, insert_stmt,-1, &statement, NULL) == SQLITE_OK)
{
 while (sqlite3_step(statement) == SQLITE_ROW)
 {
   //Updated
 }
}
else
{
    NSLog(@"error is %s",sqlite3_errmsg(database));
}

answered Sep 10, 2013 at 8:56

Geekoder

1,54110 silver badges21 bronze badges

Comments

Lundin · Accepted Answer · 2013-09-10 08:24:17Z

-1

Are you sure the dbPath is not in app bundle? We can't update db in the bundle.

sqlite3_prepare_v2() and sqlite3_step() will return an int.

You can find something you want in sqlite3.h.

Like #define SQLITE_BUSY         5   /* The database file is locked */ ...

And, why not use FMDB? You can find it easy on Github. (Link https://github.com/ccgus/fmdb )

edited Sep 10, 2013 at 8:24

Lundin

220k47 gold badges281 silver badges446 bronze badges

answered Sep 10, 2013 at 8:17

Jason

595 bronze badges

3 Comments

trojanfoe Over a year ago

This looks more like a comment than an answer. There cannot be answer at the moment as the OP has no idea why the UPDATE fails, until they introduce error reporting in their code.

JennyProGram Over a year ago

there;s a error message: 'no such table: profile'. Actually what i did is, create sqlite at Firefox, then i copy and add file to the xcode project. I reopened the sqlite file, and there's a 'profile' table

Jason Over a year ago

I think you need check the dbpath. If you want to update your sqlite db, you must copy it form bundle to local.

Collectives™ on Stack Overflow

iOS - using sqlite database update data not working

5 Answers 5

2 Comments

Comments

3 Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

3 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related