To retrieve BLOB image from sqlite

问题:

The code for saving image:

NSData *imageData=UIImagePNGRepresentation(animalImage);
        NSString *insertSQL=[NSString stringWithFormat:@"insert into AnimalsTable (name,propertyID,animalID,breed,mainBreed,dateofbirth,sex,notes,imageData) values(\"%@\",\"%@\",\"%@\",\"%@\",\"%@\",\"%@\",\"%@\",\"%@\",\"%@\")",nameString,propertyString,animalidString,breedString,mainBreedString,dateString,sexString,notesString,imageData];
    sqlite3_stmt *addStatement;
        NSLog(@"%@",appDelegate.sqlFile);
    const char *insert_stmt=[insertSQL UTF8String];
    if (sqlite3_open([appDelegate.sqlFile UTF8String],&database)==SQLITE_OK) {
        sqlite3_prepare_v2(database,insert_stmt,-1,&addStatement,NULL);

        if (sqlite3_step(addStatement)==SQLITE_DONE) {

            sqlite3_bind_blob(addStatement, 1, [imageData bytes], [imageData length], SQLITE_TRANSIENT);
            NSLog(@"Data saved");

        }
        else{
            //NSAssert1(0, @"Error while updating. '%s'", sqlite3_errmsg(database));

            NSLog(@"Some Error occured");
    }

    sqlite3_close(database);

For retrieving image:

NSString *select_sql=[NSString stringWithFormat:@"select name,animalID,imageData from AnimalsTable where mainBreed='%@' AND breed='%@'",mainString,subString];
        const char *sql = [select_sql UTF8String];
        sqlite3_stmt *selectstmt;
        if(sqlite3_prepare_v2(database, sql, -1, &selectstmt, NULL) == SQLITE_OK) {

            while(sqlite3_step(selectstmt) == SQLITE_ROW) {

            NSString *animalName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 0)];
            NSString *animalid=[NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 1)];
            NSData *dataForCachedImage = [[NSData alloc] initWithBytes:sqlite3_column_blob(selectstmt, 2) length: sqlite3_column_bytes(selectstmt, 2)];           
            [animalNamesArray addObject:animalName];
            [animalIDArray addObject:animalid];
            [imageDataArray addObject:dataForCachedImage];

        }
    }
}
else
    sqlite3_close(database); 

In UITableView CellForIndexPath:

NSData *dataForCachedImage=[imageDataArray objectAtIndex:indexPath.row];
    UIImage *dataImage=[[UIImage alloc] init];
    dataImage=[UIImage imageWithData:dataForCachedImage];
    cell.imageView.image=dataImage;

When I debug the code:

I get bytes for NSData=226381 bytes And for dataImage 0x0.

Please help me.

回答1:

You need to properly bind your values to the query for it to work. When you set the query on the first line, the description of the data would be inserted and not the correct data. If you were to log the query you would likely see something like <0FFAEC32 ... 23F0E8D1>. You then attempted to bind the blob properly later but since your query was not written correctly it had no effect.

//Although you try to bind a blob here it does not get bound
//to anything since you do not include a parameter template.
sqlite3_bind_blob(addStatement, 1, [imageData bytes], [imageData length], SQLITE_TRANSIENT);

To fix this here is a minimal example:

//query does not need to be an NSString* but just a const char *
//replace all %@ with ?
const char *sql = "INSERT INTO AnimalsTable(name, ..., imageData) values(?, ..., ?)";
if (sqlite3_open([appDelegate.sqlFile UTF8String],&database)==SQLITE_OK) {
        sqlite3_prepare_v2(database,insert_stmt,-1,&addStatement,NULL);

        //Bind all of the values here before you execute the statement
        sqlite3_bind_text(addStatement, 1, [nameString UTF8String], -1, NULL);
        ...
        sqlite3_bind_blob(addStatement, 9, [imageData bytes], [imageData length], SQLITE_TRANSIENT);

        if (sqlite3_step(addStatement)==SQLITE_DONE) {
            NSLog(@"Data saved");
        }
        ...

        //You must finalize your statement before closing the database
        sqlite3_finalize(addStatement);
        sqlite3_close(database);

Using binded values helps prevent inserting invalid data which could be a SQL injection attack and improves query performance for queries that are run often.

Note*: If you are going to be doing a lot of SQL consider using FMDB or even Core Data.