[iOS dev.] Store image into local SQLite.

Part 1. Download image files and store into local database(SQLite).

Preparation:
a. Create a local sqlite file, and insert a table. (e.g.: ACSLog table in database named "db")
b. URL of target image is: urlString.

// Get an image from the URL below

UIImage *image = [[UIImage alloc] initWithData:[NSData dataWithContentsOfURL:[NSURL URLWithString:urlString]]];



if( image != nil )	// download success.

{						

	NSData *imgData = UIImagePNGRepresentation(image);			

	

      // *** the index of question mark start from "1"			

	const char *sql = "INSERT INTO ACSLog VALUES(?,?,?,?,?)";

	sqlite3_stmt *stmt=0;

	sqlite3_prepare_v2(db, sql, strlen(sql)+1, &stmt, NULL);

						

	// this is my special case to process (store file name string into some fields of table)

	if( [_type isEqualToString:@"h"] )

		sqlite3_bind_text(stmt, 1, [_filename UTF8String], -1, SQLITE_TRANSIENT);

	else if( [_type isEqualToString:@"c"] )

		sqlite3_bind_text(stmt, 2, [_filename UTF8String], -1, SQLITE_TRANSIENT);

	else if( [_type isEqualToString:@"f"] )

		sqlite3_bind_text(stmt, 4, [_filename UTF8String], -1, SQLITE_TRANSIENT);

			

	// bind account and store image data with "blob" format

	sqlite3_bind_text(stmt, 3, [AccountAct1_Personal UTF8String], -1, SQLITE_TRANSIENT);

	sqlite3_bind_blob(stmt, 5, [imgData bytes], [imgData length], NULL);



	if( SQLITE_DONE != sqlite3_step(stmt) )

		NSAssert1(0, @"Error while updating. '%s'", sqlite3_errmsg(appDelegate.db));



	sqlite3_reset(stmt);

	sqlite3_finalize(stmt);			

}

[image release];

Part 2. get blob data in SQLite and transform into PNG image for display.

- (NSData*) loadBlobWithFileName:(NSString*)_fileName andType:(NSString*)_type

{

	NSAutoreleasePool* pool = [[NSAutoreleasePool alloc] init];

	sqlite3_stmt *stmt=0;

	char *tmp_sql;

	if( _type == @"h" )

		tmp_sql = "Select PNGImage from ACSLog Where accountid = ? AND Hat = ?";

	else if( _type == @"c" )

		tmp_sql = "Select PNGImage from ACSLog Where accountid = ? AND Cloth = ?";

	else if( _type == @"f" )

		tmp_sql = "Select PNGImage from ACSLog Where accountid = ? AND Face = ?";

	const char *sql = tmp_sql;

	

	if( sqlite3_prepare_v2(db, sql, -1, &stmt, NULL) != SQLITE_OK)

		NSAssert1(0, @"Error while creating detail view statement. '%s'", sqlite3_errmsg(db));

	

	sqlite3_bind_text(stmt, 1, [accountID UTF8String], -1, SQLITE_TRANSIENT);

	sqlite3_bind_text(stmt, 2, [_fileName UTF8String], -1, SQLITE_TRANSIENT);

		

	if(SQLITE_DONE != sqlite3_step(stmt))

	{		

		NSData *data = [[[NSData alloc] initWithBytes:sqlite3_column_blob(stmt, 0) length:sqlite3_column_bytes(stmt, 0)] autorelease];

		

		if(data == nil){

			NSLog(@"No image found.");

			sqlite3_reset(stmt);

			sqlite3_finalize(stmt);

			[pool drain];

			return NULL;

		}

		else {

			sqlite3_reset(stmt);

			sqlite3_finalize(stmt);

			[data retain];

			[pool drain];

			return [data autorelease];

		}				

	}

	else

		NSAssert1(0, @"Error while getting the price of coffee. '%s'", sqlite3_errmsg(appDelegate.db));

	[pool drain];

	return nil;

}

There's a very useful tutorial about how to use Blob data for image.
SQLite Tutorial – Saving images in the database.