how to update the image in sqlite using ios

Question

i want to store data with an image using this query,everything update correctly but the image column is always null.i don't know what is wrong in this query.please anybody help me fix it out.i'm new to iOS and sqlite too.

    UIImage *uiimg = img.image;
    NSData *data = UIImagePNGRepresentation(uiimg);

    NSString *cmd = [NSString stringWithFormat:@"update APPUSERS set name = '%@', u_name =           '%@', contact_no = '%@', address = '%@', dob = '%@', pswrd = '%@', confirm_pswrd = '%@', img = '%d' where u_name ='%@'",name.text, uname.text, pnoneno.text, address.text, dob.text, password.text, confirmpassword.text, data, tempstore] ;

    const char * sql = [cmd UTF8String];


    if (sqlite3_step(update_statement) == SQLITE_DONE)
     {
            NSLog(@"success");
     }
       else
     {
            NSLog(@"failed");

    }

while doing the sqlite3_bind_text for all fields the data not get update.that why i using this method to save data and image too. — Anand3777, Aug 29 '13 at 06:56
@user2315316 It doesn't matter. You should always do `sqlite3_bind_xxx`, whether, `INSERT`, `UPDATE`, or even just a `WHERE` clause in a `SELECT` statement. If it wasn't working with `sqlite3_bind_xxx` before, you probably didn't look at `sqlite3_errmsg` to identify the source of the problem and remedy it. Note, if any of these values could be `nil`, you'd also want to check for that condition, and use `sqlite3_bind_null` in that case. — Rob, Aug 29 '13 at 07:08

score 4 · Accepted Answer · edited May 23 '17 at 12:21

See https://stackoverflow.com/a/17994313/1271826 for an example of how to use sqlite3_bind_blob to store your NSData in the database.

Furthermore, you absolutely should avoid using stringWithFormat for building SQL statements in general. What if the address was 123 O'Brian Way or Martha's Vineyard? That apostrophe would prematurely terminate the string you're inserting. Double quotes are no better, when you go to insert Jimmy "The Greek" Snyder or Dwayne "The Rock" Johnson. Worse, a malevolent user could wreak havoc with SQL injection.

You should use ? placeholders and sqlite3_bind_blob function for NSData. And for your strings, use sqlite3_bind_text.

Thus:

UIImage *uiimg = img.image;
NSData *data = UIImagePNGRepresentation(uiimg);

const char *sql = "update APPUSERS set name = ?, u_name = ?, contact_no = ?, address = ?, dob = ?, pswrd = ?, confirm_pswrd = ?, img = ? where u_name =?";

if (sqlite3_prepare_v2(database, sql, -1, &update_statement, NULL) != SQLITE_OK)
    NSLog(@"prepare failed: %s", sqlite3_errmsg(database));

if (sqlite3_bind_text(update_statement, 1, [name.text UTF8String], -1, NULL) != SQLITE_OK)
    NSLog(@"bind 1 failed: %s", sqlite3_errmsg(database));

if (sqlite3_bind_text(update_statement, 2, [uname.text UTF8String], -1, NULL) != SQLITE_OK)
    NSLog(@"bind 2 failed: %s", sqlite3_errmsg(database));

if (sqlite3_bind_text(update_statement, 3, [pnoneno.text UTF8String], -1, NULL) != SQLITE_OK)
    NSLog(@"bind 3 failed: %s", sqlite3_errmsg(database));

if (sqlite3_bind_text(update_statement, 4, [address.text UTF8String], -1, NULL) != SQLITE_OK)
    NSLog(@"bind 4 failed: %s", sqlite3_errmsg(database));

if (sqlite3_bind_text(update_statement, 5, [dob.text UTF8String], -1, NULL) != SQLITE_OK)
    NSLog(@"bind 5 failed: %s", sqlite3_errmsg(database));

if (sqlite3_bind_text(update_statement, 6, [password.text UTF8String], -1, NULL) != SQLITE_OK)
    NSLog(@"bind 6 failed: %s", sqlite3_errmsg(database));

if (sqlite3_bind_text(update_statement, 7, [confirmpassword.text UTF8String], -1, NULL) != SQLITE_OK)
    NSLog(@"bind 7 failed: %s", sqlite3_errmsg(database));

// note, use blob here

if (sqlite3_bind_blob(update_statement, 8, [data bytes], [data length], SQLITE_TRANSIENT) != SQLITE_OK)
    NSLog(@"bind 8 failed: %s", sqlite3_errmsg(database));

if (sqlite3_bind_text(update_statement, 9, [tempstore UTF8String], -1, NULL) != SQLITE_OK)
    NSLog(@"bind 9 failed: %s", sqlite3_errmsg(database));

if (sqlite3_step(update_statement) == SQLITE_DONE)
{
    NSLog(@"success");
}
else
{
    NSLog(@"failed: %s", sqlite3_errmsg(database));
}

sqlite3_finalize(update_statement);

Note, in addition to doing sqlite3_bind_xxx, I'd also suggest (a) don't forget to do sqlite3_prepare_v2 first; (b) on any error, look at sqlite3_errmsg; and (c) remember to do sqlite3_finalize at the end to free up your memory.

Note, though, SQLite is notably inefficient in storing large blobs. If the images are small thumbnails it's not an issue, but for large images, you should be saving the image to your Documents folder and then only saving the file path in your database.

Finally, note that if you had an image that you loaded into a UIImage that you then re-retrieved via UIImagePNGRepresentation, that can result in data loss or the file may get considerably larger. It depends upon the original source of the image. People often assume if they do UIImagePNGRepresentation, they're guaranteed to get the same image, and while it may look virtually identical, it can be altered in the process (not always; it depends). If you have access to the original asset/file/NSData you used to set the UIImage, you should just refer back to that.

As an unrelated aside, you really shouldn't be storing passwords in plaintext in your database, either. You should encrypt them or use a keychain to store the passwords.

score 1 · Answer 2 · edited May 23 '17 at 12:13

1

Try to save Image name in the database and image in Documents folder by creating NSDocument in NSFileManager.These are singleton instances.You can save the image in phone memory folder which is related to documents of the applciation

example you can find in this path Read/write file in Documents directory problem

edited May 23 '17 at 12:13

Community

1
1

answered Aug 29 '13 at 06:39

NHS

409
2
7

how to update the image in sqlite using ios

2 Answers2