Insert NSData into SQLite on iPhone

Question

So far, I have managed to create this method to insert into the SQLite database on iPhone:

- (void) insertToDB :(NSString *)Identifier :(NSString *)Name { sqlite3 *database; if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK) { char *sql1 = "INSERT INTO table VALUES ('"; const char *sql2 = [Identifier cStringUsingEncoding:[NSString defaultCStringEncoding]]; char *sql3 = "', '"; const char *sql4 = [Name cStringUsingEncoding:[NSString defaultCStringEncoding]]; char *sql5 = "')"; char *sqlStatement[255]; strcpy(sqlStatement, sql1); strcat(sqlStatement, sql2); strcat(sqlStatement, sql3); strcat(sqlStatement, sql4); strcat(sqlStatement, sql5); sqlite3_stmt *compiledStatement; if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) { sqlite3_last_insert_rowid(database); sqlite3_reset(compiledStatement); } sqlite3_finalize(compiledStatment); } sqlite3_close(database); } 

Now I am looking to save an image in a database. So far I have found this :

 UIImage *cachedImage = [UIImage imageNamed:@"Icon.png"]; NSData *dataForImage = UIImagePNGRepresentation(cachedImage); 

But I am having trouble trying to insert this NSData into a char array that sqlStatement does. Has anyone figured out how to do this?

(I have a field in a blob type database for this).

thanks

Answer 1

I would use sqlite3_stmt instead of a string. Then you can use sqlite3_bind_blob to bind blob to prepared statement.

 sqlite3_stmt *insert_statement; char *sql = "INSERT INTO table (blobcolumn, column2, column3) VALUES (? , ?, ?)" ; if(sqlite3_prepare_v2(database, sql, -1, &insert_statement, NULL) != SQLITE_OK) { //handle error } sqlite3_bind_blob(insert_statement, 1, [dataForImage bytes], [dataForImage length], NULL); 

But, indeed, it would be best if performance saves the image to disk and the path in the database.

Another way to do this, which I use to send image data in XML, is to base the encoding of 64 data. I have this here as a category in NSString:

 static char base64EncodingTable[64] = { 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z', '0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '+', '/' }; @implementation NSString (NSStringCategories) + (NSString *) base64StringFromData: (NSData *)data length: (int)length { unsigned long ixtext, lentext; long ctremaining; unsigned char input[3], output[4]; short i, charsonline = 0, ctcopy; const unsigned char *raw; NSMutableString *result; lentext = [data length]; if (lentext < 1) return @""; result = [NSMutableString stringWithCapacity: lentext]; raw = [data bytes]; ixtext = 0; while (true) { ctremaining = lentext - ixtext; if (ctremaining <= 0) break; for (i = 0; i < 3; i++) { unsigned long ix = ixtext + i; if (ix < lentext) input[i] = raw[ix]; else input[i] = 0; } output[0] = (input[0] & 0xFC) >> 2; output[1] = ((input[0] & 0x03) << 4) | ((input[1] & 0xF0) >> 4); output[2] = ((input[1] & 0x0F) << 2) | ((input[2] & 0xC0) >> 6); output[3] = input[2] & 0x3F; ctcopy = 4; switch (ctremaining) { case 1: ctcopy = 2; break; case 2: ctcopy = 3; break; } for (i = 0; i < ctcopy; i++) [result appendString: [NSString stringWithFormat: @"%c", base64EncodingTable[output[i]]]]; for (i = ctcopy; i < 4; i++) [result appendString: @"="]; ixtext += 3; charsonline += 4; if ((length > 0) && (charsonline >= length)) charsonline = 0; } return result; }