Saving image from UIImagePicker to Sqlite DB

Question

So what I'm doing is the user is picking an image from the camera roll and I'm setting an imageView with that image. So far so good. Now I need to save this image to my Database.So when the view loads next time I have that chosen image again. Problem is my image view is not being set with the image.Probably because its not being saved properly to my database. From what Ive read NSData is best to use and its what I'm using but no success. Can someone help fix my problem?

-(void)imagePickerController:(UIImagePickerController *)picker didFinishPickingMediaWithInfo:(NSDictionary *)info{

    [self dismissViewControllerAnimated:YES completion:nil];
    fsImageView.image = [info objectForKey:UIImagePickerControllerOriginalImage];
    fsUIImage = [info objectForKey:UIImagePickerControllerOriginalImage];
    imgData = UIImagePNGRepresentation(fsUIImage);
    [self saveImageToDatabase];

}

------Here I save my image to the DB-------

-(void)saveImageToDatabase{

    [self openDB];

    sqlite3_stmt *newstatement;

    int originalTotalCount = sqlite3_total_changes(db);

    NSString *sql = [NSString stringWithFormat:@"UPDATE Annotation SET image = '%@' WHERE title ='%@'", imgData, fsTitleName];
    if (sqlite3_prepare_v2(db, [sql UTF8String], -1, &newstatement, nil) != SQLITE_OK) {
        NSLog(@"%s: prepare failed: %s", __FUNCTION__, sqlite3_errmsg(db));
    } else {
        if (sqlite3_step(newstatement) != SQLITE_DONE) {
            NSLog(@"%s: step failed: %s", __FUNCTION__, sqlite3_errmsg(db));
        } else {
            int rowsUpdated = sqlite3_total_changes(db) - originalTotalCount;

            NSString *message;

            if (rowsUpdated == 1)
                message = @"Updated one row";
            else if (rowsUpdated == 0)
                message = @"No rows updated";
            else
                message = [NSString stringWithFormat:@"Updated %d rows", rowsUpdated]; 

            NSLog(@"%@", message);

            sqlite3_step(newstatement);
        }

        sqlite3_finalize(newstatement);
    }
    sqlite3_close(db);

}

------Here I pull my image from the DB------- Gets called when viewLoads..

-(void)setImage{

    [self openDB];

    NSString *sql = [NSString stringWithFormat:@"SELECT image FROM Annotation WHERE title = '%@'",fsTitleName];

    sqlite3_stmt *statement;

    if(sqlite3_prepare_v2(db, [sql UTF8String], -1, &statement, nil) == SQLITE_OK){

        int rc;
        if ((rc = sqlite3_step(statement)) == SQLITE_ROW) {
            char *field1 = (char *) sqlite3_column_text(statement, 0);
            NSString *field1Str = [[NSString alloc]initWithUTF8String:field1];
            imgData = [field1Str dataUsingEncoding:NSUTF8StringEncoding];
            [fsImageView setImage:[UIImage imageWithData: imgData]];

        } else if (rc != SQLITE_DONE) {
            NSLog(@"%s: sqlite3_step failed: %s", __FUNCTION__, sqlite3_errmsg(db));
        }
    } else {
        NSLog(@"%s: sqlite3_prepare_v2 failed: %s", __FUNCTION__, sqlite3_errmsg(db));
    }

    sqlite3_finalize(statement);
    sqlite3_close(db);


}

Answer 1

In your case your getting image from UIImagePicker successfully and converting it into NSData to be stored in sqlite, the only problem your facing is storing the NSData into sqlite.

Saving NSData into sqlite database requires it to be saved as bytes in sqlite, so either you store NSData in bytes or convert it into Base64 string and then strore the Base64 string in sqlite.

To store NSData in sqlite as bytes you can refer below links:

1) How to retrieve string from NSData stored in sqlite?

2) Retrieve nsdata from sqlite stored in Blob data type for iPhone App

To store NSData in sqlite as Base64 you can use below Base64 library which is very easy to use.

https://github.com/nicklockwood/Base64

Now to convert a NSData to Base64 just use below code:

NSString *aStrData = [theNSDataObject base64EncodedString];

And then after you fetch it from database convert back the Base64 string to NSData like below:

NSData *theNSDataObject = [aStrData base64DecodedData];

I prefer using Base64 string is a good way, so if in future if you want to send the image to any web service you can send it in base64 format also.

Answer 2

// Use this Code for insertion

  NSString *insertSQL = [NSString stringWithFormat:@"INSERT INTO <Table Name> (Image) VALUES (?)"];

        const char *insert_stmt = [insertSQL UTF8String];

        if(sqlite3_prepare_v2(contactDB, insert_stmt, -1, &statement, NULL)== SQLITE_OK)
        {

            sqlite3_bind_blob( statement, 1, [imageData bytes],(unsigned int) [imageData length], SQLITE_TRANSIENT);
        }

// Use this Code for retrieving

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

        NSString *querySQL = [NSString stringWithFormat:
                              @"SELECT * FROM <Table Name>" ];
        const char *query_stmt = [querySQL UTF8String];

        if (sqlite3_prepare_v2(contactDB,
                               query_stmt, -1, &statement, NULL) == SQLITE_OK)
        {
            while (sqlite3_step(statement) == SQLITE_ROW)
            {


                int blobBytesLength = sqlite3_column_bytes(statement, 1);
                NSData *blobData = [[NSData alloc]initWithBytes:sqlite3_column_blob(statement, 1)length:blobBytesLength];

                UIImage *imagetest = [[UIImage alloc] initWithData:blobData];//this is your Image
          }
       }