如何在 Objective-C 中使用 sqlite3_exec 将 UIImage 作为 blob 插入

Question

我正在尝试将 sqlite 中的一些图像缓存为 nsdata，当我尝试使用 sqlite3_exec 和原始 SQL 字符串（如 NSString）插入字节数组时遇到问题

NSData *imgData = UIImagePNGRepresentation(img);
NSString* sql = [NSString stringWithFormat:@"INSERT INTO persistedimg (imgx,idvalx) VALUES (%@,'%@')", imgData, idValue];
rc = sqlite3_exec(db, [sql UTF8String], callbackFunction, (void*)contextObject, &zErrMsg);

但上述问题是我添加 NSData直接到 sql 字符串而不是字节。

我想做这样的事情

... [imgData bytes], [imgData length]

但是因为我没有使用典型的“_bind_blob”之类的方法，所以我不确定如何使用原始字符串

更新

来做到这一点，我正在使用一个包装器我想坚持使用并简单地编写一个新方法来支持图像插入/查询命令，

下面是到目前为止我的整个包装类

**

#import "SQLiteAccess.h"
#import <sqlite3.h>

@implementation SQLiteAccess

+ (NSString *)pathToDB {
    NSString *dbName = @"test123";
  NSString *originalDBPath = [[NSBundle mainBundle] pathForResource:dbName ofType:@"db"];
  NSString *path = nil;
  NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
  NSString *appSupportDir = [paths objectAtIndex:0];
  NSString *dbNameDir = [NSString stringWithFormat:@"%@/test123", appSupportDir];
  NSFileManager *fileManager = [NSFileManager defaultManager];
  BOOL isDir = NO;
  BOOL dirExists = [fileManager fileExistsAtPath:dbNameDir isDirectory:&isDir];
  NSString *dbPath = [NSString stringWithFormat:@"%@/%@.db", dbNameDir, dbName];
  if(dirExists && isDir) {
    BOOL dbExists = [fileManager fileExistsAtPath:dbPath];
    if(!dbExists) {
      NSError *error = nil;
      BOOL success = [fileManager copyItemAtPath:originalDBPath toPath:dbPath error:&error];
      if(!success) {
        NSLog(@"error = %@", error);
      } else {
        path = dbPath;
      }
    } else {
      path = dbPath;
    }
  } else if(!dirExists) {
    NSError *error = nil;
    BOOL success =[fileManager createDirectoryAtPath:dbNameDir attributes:nil];
    if(!success) {
      NSLog(@"failed to create dir");
    }
    success = [fileManager copyItemAtPath:originalDBPath toPath:dbPath error:&error];
    if(!success) {
      NSLog(@"error = %@", error);
    } else {
      path = dbPath;
    }
  }
  return path;
}

+ (NSNumber *)executeSQL:(NSString *)sql withCallback:(void *)callbackFunction context:(id)contextObject {
  NSString *path = [self pathToDB];
  sqlite3 *db = NULL;
  int rc = SQLITE_OK;
  NSInteger lastRowId = 0;
  rc = sqlite3_open([path UTF8String], &db);
  if(SQLITE_OK != rc) {
    NSLog(@"Can't open database: %s\n", sqlite3_errmsg(db));
    sqlite3_close(db);
    return nil;
  } else {
    char *zErrMsg = NULL;
    NSAutoreleasePool *pool = [[NSAutoreleasePool alloc] init];
    rc = sqlite3_exec(db, [sql UTF8String], callbackFunction, (void*)contextObject, &zErrMsg);
    if(SQLITE_OK != rc) {
      NSLog(@"Can't run query '%@' error message: %s\n", sql, sqlite3_errmsg(db));
      sqlite3_free(zErrMsg);
    }
    lastRowId = sqlite3_last_insert_rowid(db);
    sqlite3_close(db);
    [pool release];
  }
  NSNumber *lastInsertRowId = nil;
  if(0 != lastRowId) {
    lastInsertRowId = [NSNumber numberWithInteger:lastRowId];
  }
  return lastInsertRowId;
}

static int singleRowCallback(void *queryValuesVP, int columnCount, char **values, char **columnNames) {
  NSMutableDictionary *queryValues = (NSMutableDictionary *)queryValuesVP;
  int i;
  for(i=0; i<columnCount; i++) {
    [queryValues setObject:values[i] ? [NSString stringWithFormat:@"%s",values[i]] : [NSNull null] 
                    forKey:[NSString stringWithFormat:@"%s", columnNames[i]]];
  }
  return 0;
}

+ (NSString *)selectOneValueSQL:(NSString *)sql {
    NSMutableDictionary *queryValues = [NSMutableDictionary dictionary];
    [self executeSQL:sql withCallback:singleRowCallback context:queryValues];
    NSString *value = nil;
    if([queryValues count] == 1) {
        value = [[queryValues objectEnumerator] nextObject];
    }
    return value;
}

+ (NSNumber *)insertWithSQL:(NSString *)sql {
    sql = [NSString stringWithFormat:@"BEGIN TRANSACTION; %@; COMMIT TRANSACTION;", sql];
    return [self executeSQL:sql withCallback:NULL context:NULL];
}

+ (void)updateWithSQL:(NSString *)sql {
  sql = [NSString stringWithFormat:@"BEGIN TRANSACTION; %@; COMMIT TRANSACTION;", sql];
  [self executeSQL:sql withCallback:NULL context:nil];
}

@end

**

对此解决方案的任何帮助都将是巨大的！

Answer 1

我认为您在这里遇到的问题很大一部分是您试图过于简化 SQLite3 API。 API 不仅仅用于执行文本 SQL 查询；还用于执行文本 SQL 查询。准备好的语句和绑定参数的存在是有原因的。您不应该尝试在字符串中插入二进制数据。这只是自找麻烦，尤其是当您的二进制数据中有空值时。

要插入 blob，您确实需要将 sqlite3_bind_blob 与 sqlite3_prepare_v2 结合使用。绑定 blob 时，您还需要使用 [imgData bytes] 作为 blob 数据。

您是否正在寻求重建 API 的帮助，以使此类特定图像缓存用例变得更容易？

编辑

这是一个使用bind插入二进制数据的简单示例。假设有一个名为 my_table 的表，有 2 列：VARCHAR 类型的 name 和 类型的 data BLOB。请注意，我尚未测试或什至尝试编译此内容，因此可能存在拼写错误或错误。

sqlite3 *database;

// Open a connection to the database given its file path.
if (sqlite3_open("/path/to/sqlite/database.sqlite3", &database) != SQLITE_OK) {
    // error handling...
}

// Construct the query and empty prepared statement.
const char *sql = "INSERT INTO `my_table` (`name`, `data`) VALUES (?, ?)";
sqlite3_stmt *statement;

// Prepare the data to bind.
NSData *imageData = UIImagePNGRepresentation([UIImage imageNamed:@"something"]);
NSString *nameParam = @"Some name";

// Prepare the statement.
if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
    // Bind the parameters (note that these use a 1-based index, not 0).
    sqlite3_bind_text(statement, 1, nameParam);
    sqlite3_bind_blob(statement, 2, [imageData bytes], [imageData length], SQLITE_STATIC);
    // SQLITE_STATIC tells SQLite that it doesn't have to worry about freeing the binary data.
}

// Execute the statement.
if (sqlite3_step(statement) != SQLITE_DONE) {
    // error handling...
}

// Clean up and delete the resources used by the prepared statement.
sqlite3_finalize(statement);

// Now let's try to query! Just select the data column.
const char *selectSql = "SELECT `data` FROM `my_table` WHERE `name` = ?";
sqlite3_stmt *selectStatement;

if (sqlite3_prepare_v2(database, selectSql, -1, &selectStatement, NULL) == SQLITE_OK) {
    // Bind the name parameter.
    sqlite3_bind_text(selectStatement, 1, nameParam);
}

// Execute the statement and iterate over all the resulting rows.
while (sqlite3_step(selectStatement) == SQLITE_ROW) {
    // We got a row back. Let's extract that BLOB.
    // Notice the columns have 0-based indices here.
    const void *blobBytes = sqlite3_column_blob(selectStatement, 0);
    int blobBytesLength = sqlite3_column_bytes(selectStatement, 0); // Count the number of bytes in the BLOB.
    NSData *blobData = [NSData dataWithBytes:blobBytes length:blobBytesLength];
    NSLog("Here's that data!\n%@", blobData);
}

// Clean up the select statement
sqlite3_finalize(selectStatement);

// Close the connection to the database.
sqlite3_close(database);