Saving image data to sqlite database in iphone

Question

I want to save image data to my sqlite database, but I cannot do this ...

- (void) SaveData: (Article*) article :(NSMutableArray*) rssList { sqlite3_stmt *statement; if (sqlite3_open([self.dataBasePath UTF8String], &articlesDB) == SQLITE_OK) { NSString* sqlFormat = @"INSERT INTO ARTICLES (guid, title, summary, mainLink, pubDate, author, imageLink, body, favorites, smallImage) VALUES ('%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', ?)"; // Correct summary. NSString* summary = [NSString stringWithString:article.summary]; summary = [summary stringByReplacingOccurrencesOfString:@"'" withString:@"''"]; // Correct title NSString* title = [NSString stringWithString:article.title]; title = [title stringByReplacingOccurrencesOfString:@"'" withString:@"''"]; // Correct Body NSString* body = [NSString stringWithString:article.body]; //body = [body stringByReplacingOccurrencesOfString:@"'" withString:@"''"]; // Create Insert SQL query. NSString* insertSQL = [NSString stringWithFormat:sqlFormat, article.guid, title, summary, article.mainLink, article.pubDate, article.author, article.imageLink, body, article.favorite ? @"YES" : @"NO"]; // Add to database. sqlite3_prepare_v2(articlesDB, [insertSQL UTF8String], -1, &statement, NULL); if (sqlite3_step(statement) == SQLITE_DONE) { // Add to list. [rssList addObject:article]; // Write Log. NSLog( @"Article added ... " ); } else { NSLog( @"Failed to add article. Error is: %s", sqlite3_errmsg(articlesDB) ); } sqlite3_finalize(statement); sqlite3_close(articlesDB); } } 

You can see that I have a request, and in the end I have smallimage and value (?)

 @"INSERT INTO ARTICLES (guid, title, summary, mainLink, pubDate, author, imageLink, body, favorites, smallImage) VALUES ('%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', ?)"; 

I have a function that should update the image value somewhere in my program: in this function I select a line with the URL of the image link when I try to write data to the last column "SMALLIMAGE", but there is no result ... When I open my database file with an editor, which I see in the last line, only "NULL", as my data is not saved ...

 - (void) SaveSmallImage: (NSData*) imgData :(NSString*) mainUrl { if (sqlite3_open([self.dataBasePath UTF8String], &articlesDB) == SQLITE_OK) { NSString* sqliteQuery = [NSString stringWithFormat:@"SELECT * FROM ARTICLES WHERE IMAGELINK = '%@'", mainUrl]; sqlite3_stmt* statement; if( sqlite3_prepare_v2(articlesDB, [sqliteQuery UTF8String], -1, &statement, NULL) == SQLITE_OK ) { sqlite3_bind_blob(statement, 9, [imgData bytes], [imgData length], SQLITE_TRANSIENT); sqlite3_step(statement); } else { NSLog( @"SaveBody: Failed from sqlite3_prepare_v2. Error is: %s", sqlite3_errmsg(articlesDB) ); } // Finalize and close database. sqlite3_finalize(statement); sqlite3_close(articlesDB); } else { NSLog(@"SaveSmallImage: Failed from sqlite3_open. Error is: %s", sqlite3_errmsg(articlesDB) ); } } 

Who can tell me what I'm doing wrong ...

Answer 1

Here's how you can save an image in SQLITE :

 // Create Images Table. sql_stmp = "CREATE TABLE IF NOT EXISTS IMAGES (URL TEXT UNIQUE, IMAGE BLOB)"; if( sqlite3_exec(articlesDB, sql_stmp, NULL, NULL, &errMsg) != SQLITE_OK ) NSLog( @"Fail to create \"IMAGES\" table. Error is: %@", errMsg ); 

And here is the function to save:

 // Save Small Image Data by given main url - (void) SaveImagesToSql: (NSData*) imgData :(NSString*) mainUrl { NSLog( @"\n*****Save image to SQLite*****\n" ); const char* sqliteQuery = "INSERT INTO IMAGES (URL, IMAGE) VALUES (?, ?)"; sqlite3_stmt* statement; if( sqlite3_prepare_v2(articlesDB, sqliteQuery, -1, &statement, NULL) == SQLITE_OK ) { sqlite3_bind_text(statement, 1, [mainUrl UTF8String], -1, SQLITE_TRANSIENT); sqlite3_bind_blob(statement, 2, [imgData bytes], [imgData length], SQLITE_TRANSIENT); sqlite3_step(statement); } else NSLog( @"SaveBody: Failed from sqlite3_prepare_v2. Error is: %s", sqlite3_errmsg(articlesDB) ); // Finalize and close database. sqlite3_finalize(statement); } 

And here is the image upload function:

 // Load images from data base with given image url - (NSData*) LoadImagesFromSql: (NSString*) imageLink { NSData* data = nil; NSString* sqliteQuery = [NSString stringWithFormat:@"SELECT IMAGE FROM IMAGES WHERE URL = '%@'", imageLink]; sqlite3_stmt* statement; if( sqlite3_prepare_v2(articlesDB, [sqliteQuery UTF8String], -1, &statement, NULL) == SQLITE_OK ) { if( sqlite3_step(statement) == SQLITE_ROW ) { int length = sqlite3_column_bytes(statement, 0); data = [NSData dataWithBytes:sqlite3_column_blob(statement, 0) length:length]; } } // Finalize and close database. sqlite3_finalize(statement); return data; } 

Answer 2

Or how can I update blob data?

 NSString* sqliteQuery = [NSString stringWithFormat:@"UPDATE ARTICLES SET SMALLIMAGE = '?' WHERE IMAGELINK = '@%'", mainUrl]; 

Function

:

 if (sqlite3_open([self.dataBasePath UTF8String], &articlesDB) == SQLITE_OK) { NSString* sqliteQuery = [NSString stringWithFormat:@"UPDATE ARTICLES SET SMALLIMAGE = '?' WHERE IMAGELINK = '@%'", mainUrl]; sqlite3_stmt* statement; if( sqlite3_prepare_v2(articlesDB, [sqliteQuery UTF8String], -1, &statement, NULL) == SQLITE_OK ) { int result = sqlite3_bind_blob(statement, 9, [imgData bytes], [imgData length], NULL); sqlite3_step(statement); } else { NSLog( @"SaveBody: Failed from sqlite3_prepare_v2. Error is: %s", sqlite3_errmsg(articlesDB) ); } // Finalize and close database. sqlite3_finalize(statement); sqlite3_close(articlesDB); } else { NSLog(@"SaveSmallImage: Failed from sqlite3_open. Error is: %s", sqlite3_errmsg(articlesDB) ); } 

this part of code return error 25

 int result = sqlite3_bind_blob(statement, 9, [imgData bytes], [imgData length], NULL); 

Answer 3

To update the BLOB image: (edited patch for several typos from the update response below)

 -(void) UpdateImagesToSql: (NSData *)imgData forUrl: (NSString *) mainUrl { NSLog( @"\n*****Updating image to SQLite*****\n",); const char* sqliteQuery = NSString* sqliteQuery = [NSString stringWithFormat:@"UPDATE ARTICLES SET SMALLIMAGE = ? WHERE IMAGELINK = '@%'", mainUrl]; sqlite3_stmt* statement; if( sqlite3_prepare_v2(articlesDB, sqliteQuery, -1, &statement, NULL) == SQLITE_OK ) { sqlite3_bind_blob(statement, 1, [imgData bytes], (int)[imgData length], SQLITE_TRANSIENT); sqlite3_step(statement); } else { NSLog( @"SaveBody: Failed from sqlite3_prepare_v2. Error is: %s", sqlite3_errmsg(articlesDB) ); } // Finalize and close database. sqlite3_finalize(statement); }