Storing and Retrieving Image in SQLite with Swift

Storing and Retrieving Image in Sqlite with swift

Image itself cannot be stored into a database columns but you can first convert it into a string and then store it. The string is called base64 string. As far as I know, any image can be converted to that and reversely.

To encode to base 64:

let image : UIImage = UIImage(named:"imageNameHere")!
let imageData:NSData = UIImagePNGRepresentation(image)!
let strBase64 = imageData.base64EncodedString(options: .lineLength64Characters)

Now your UIImage object is converted to a String! Save strBase64 to SQLite DB. Remember to use text as column type because this string is very long.

To decode back to UIImage:

let dataDecoded:NSData = NSData(base64EncodedString: strBase64, options: NSDataBase64DecodingOptions(rawValue: 0))!
let decodedimage:UIImage = UIImage(data: dataDecoded)!

Saving and retrieving images to and from SQLite in iOS

- (void)saveImage 
{
  sqlite3_stmt *compiledStmt;
  sqlite3 *db;
  if(sqlite3_open([dbPath UTF8String], &db)==SQLITE_OK){
  NSString *insertSQL=@"insert into Image(image) VALUES(?)";
  if(sqlite3_prepare_v2(db,[insertSQL cStringUsingEncoding:NSUTF8StringEncoding], -1, &compiledStmt, NULL) == SQLITE_OK){
  UIImage *image = [UIImage imageNamed:@"vegextra.png"];
  NSData *imageData=UIImagePNGRepresentation(image);

  sqlite3_bind_blob(compiledStmt, 1, [imageData bytes], [imageData length], SQLITE_TRANSIENT);

  if(sqlite3_step(compiledStmt) != SQLITE_DONE ) {
      NSLog( @"Error: %s", sqlite3_errmsg(db) );
  } else {
      NSLog( @"Insert into row id = %lld", (sqlite3_last_insert_rowid(db)));
  }

sqlite3_finalize(compiledStmt);
  }
 }
sqlite3_close(db);
}

- (void)showImage
 {
   sqlite3_stmt *compiledStmt;
   sqlite3 *db;
   int i = 1;
   if(sqlite3_open([dbPath UTF8String], &db)==SQLITE_OK){
   NSString *insertSQL = [NSString stringWithFormat:@"Select image from Image Where Id = %d",i];
   if(sqlite3_prepare_v2(db,[insertSQL cStringUsingEncoding:NSUTF8StringEncoding], -1, &compiledStmt, NULL) == SQLITE_OK) {
   while(sqlite3_step(compiledStmt) == SQLITE_ROW) {

   int length = sqlite3_column_bytes(compiledStmt, 0);
   NSData *imageData = [NSData dataWithBytes:sqlite3_column_blob(compiledStmt, 0) length:length];

   NSLog(@"Length : %d", [imageData length]);

  if(imageData == nil)
     NSLog(@"No image found.");
  else
     imgView.image = [UIImage imageWithData:imageData];
   }
 }
 sqlite3_finalize(compiledStmt);
}
sqlite3_close(db);
}

Saving image from UIImagePicker to Sqlite DB

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.

How can i show Images from Sqlite to a uicollectionview

I solved the issue by retrieving the blob data like this:

let lenght:Int = Int(sqlite3_column_bytes(queryStatement, 0));
let imgdata : NSData = NSData(bytes: sqlite3_column_blob(queryStatement, 0), length: lenght)
let decoded_img : UIImage = UIImage(data: imgdata as Data)!
imageNames.append(decoded_img)

I declared an array of type UIImage, then used it in uicollectionview view, it is now working fine for me.

---

Related Topics