How to insert UIImage as blob using sqlite3_exec in objective-c

Question

I'm trying to cache some images in sqlite as nsdata and I'm having an issue when I attempt to insert the byte array using sqlite3_exec and a raw SQL string (as 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);

But the problem with the above is I'm adding NSData to the sql string directly instead of the bytes.

I wanted to do something like this

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

But because I'm not using the typical "_bind_blob" like approach I'm not sure how to do it w/ a raw string

Update

I'm using a wrapper that I'd like to stick w/ and simply write a new method to support image insert / query commands

the below is my entire wrapper class so far

**

#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

**

Any help with this solution would be huge!

Answer 1

I think a large part of the issue you are running into here is that you are trying to simplify the SQLite3 APIs too much. The APIs are not just for executing textual SQL queries; prepared statements and bind parameters exist for a reason. You shouldn't be trying to insert binary data in a string. That's just asking for problems, especially if your binary data has nulls in it.

To insert blobs, you really do need to use sqlite3_bind_blob with sqlite3_prepare_v2. When you bind the blob, you will need to also use [imgData bytes] as the blob data.

Are you perhaps looking for help reconstructing your API to make this sort of thing easier for this particular image caching use case?

Edit

Here's a simple example using bind to insert binary data. Assume there is a table called my_table with 2 columns: name of type VARCHAR and data of type BLOB. Please note that I have not tested or even tried compiling this, so there may be typos or errors.

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