Save image data to sqlite database in iphone
I want to save image data to my sqlite database but I can't do it ...
- (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 query and at 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 must update image value somewhere in my program: In this function i select the row with image link url when i try to write a data to the last column "SMALLIMAGE" but no result ... When I open my database file with editor i see in the last row only "NULL" like 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 do wrong ...开发者_开发问答
Here is how you can Save image to 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 function for 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 function to load images:
// 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;
}
Or how can i update blob data ??
NSString* sqliteQuery = [NSString stringWithFormat:@"UPDATE ARTICLES SET SMALLIMAGE = '?' WHERE IMAGELINK = '@%'", mainUrl];
function is:
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);
To update the BLOB image: ( edited correcting few typos from the update answer 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);
}
精彩评论