从sqlite检索BLOB映像 [英] To retrieve BLOB image from sqlite

问题描述

保存图片的代码：

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);

用于检索图像：

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); 

在UITableView CellForIndexPath中：

In UITableView CellForIndexPath:

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

当我调试代码时：

我得到NSData = 226381字节和dataImage 0x0的字节。

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

请帮帮我。

推荐答案

您需要将值正确绑定到查询才能生效。在第一行设置查询时，将插入数据描述而不是正确的数据。如果您要记录查询，您可能会看到类似< 0FFAEC32 ... 23F0E8D1> 的内容。
然后您尝试稍后正确绑定blob，但由于您的查询未正确编写，因此无效。

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);

使用绑定值有助于防止插入可能是SQL注入攻击的无效数据并提高查询的查询性能经常运行。

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.

注意*：如果您打算做很多SQL，请考虑使用 FMDB 甚至核心数据。

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

这篇关于从sqlite检索BLOB映像的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！