Reading and writing images to an SQLite DB for iPhone use

Hi All, I'm still new to iPhone Dev so be gentle.

I've set up a SQLite DB that currently reads and writes NSStrings perfectly. The only problem I have now is that I also want to store an image in the database and recall it later. I've read up a bit on using NSData and encoding the image but I'm not entirely sure what the syntax is for what I want to do. Any code snippets or examples would be greatly appreciated.

My current process goes like this: UIImagePickerController -> User Chooses Image from Photos -> chosenImage is set to instance of UIImageView -> Now I want to take this image and store it in the DB

Thanks in advance!

Edit* I should mention this call will eventually be replaced with a call to a remote server. Not sure if this makes a difference as far as performance goes.

From stackoverflow

• One option (and generally preferred when working in SQL) is to write the image to a file on the system and store the path (or some other kind of identifier) in the database.

  John Fricker : I'm really at a loss as to why anyone would store the binary in the DB. Seems nuts to me. So I think the metadata in the DB is the best case.

  Jeff : Can you show me a code example for something like this? I can see how saving out to a file could be beneficial.

  Jeff : after a few days of googling. I've realized and executed this idea. I'll post some code if anyone needs it.

  diana : Can u please post it ?

  ing0 : This is not always ideal.

• You'll need to convert the UIImage hosted within your UIImageView into a binary BLOB for storage in SQLite. To do that, you can use the following:

  NSData *dataForImage = UIImagePNGRepresentation(cachedImage);
  sqlite3_bind_blob(yourSavingSQLStatement, 2, [dataForImage bytes], [dataForImage length], SQLITE_TRANSIENT);
  

  This will generate a PNG representation of your image, store it in an NSData instance, and then bind the bytes from the NSData as a BLOB for the second argument in your SQL query. Use UIImageJPEGRepresentation in the above to store in that format, if you like. You will need to have a BLOB column added to the appropriate table in your SQLite database.

  To retrieve this image, you can use the following:

  NSData *dataForCachedImage = [[NSData alloc] initWithBytes:sqlite3_column_blob(yourLoadingSQLStatement, 2) length: sqlite3_column_bytes(yourLoadingSQLStatement, 2)];    
  self.cachedImage = [UIImage imageWithData:dataForCachedImage];
  [dataForCachedImage release];
  

  Jeff : Thanks Brad! I'll give this a try later this afternoon. Looks like it may do the trick though.

  Jeff : I realized this isn't want I wanted to do. Saving the file to a URL on the server is a better way to go.

  Brad Larson : If that works best for application, then that's the way to go. I've stored images in the database when I wanted a single file that I could bundle in an application which would hold some starter data. It made the filesystem a little cleaner.

• Brad's solution works perfectly =D

  Thanks Brad

• Writing Image to SQLite DB

  if(myImage != nil){ NSData *imgData = UIImagePNGRepresentation(myImage); sqlite3_bind_blob(update_stmtement, 6, [imgData bytes], [imgData length], NULL);
  } else { sqlite3_bind_blob(update_stmtement, 6, nil, -1, NULL); }

  Reading From SQLite DB

  NSData *data = [[NSData alloc] initWithBytes:sqlite3_column_blob(init_statement, 6) length:sqlite3_column_bytes(init_statement, 6)];
          if(data == nil)
              NSLog(@"No image found.");
          else
              self.pictureImage = [UIImage imageWithData:data];