Geek asks and answers

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

I am trying to cache some images in sqlite as nsdata and I am having a problem when I try to insert an array of bytes using sqlite3_exec and an raw SQL string (like 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 that I am adding NSData to the sql string directly instead of bytes.

I wanted to do something like this

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

But since I do not use the usual "_bind_blob" approach, I am not sure how to do this with the original string

Update

I am using a wrapper that I would like to use and just wrote a new method for supporting image insert / request commands

below is my whole wrapper class

**

#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!

+3
1

, , , , API- SQLite3. API- SQL-; . . , .

, sqlite3_bind_blob sqlite3_prepare_v2. blob, [imgData bytes] blob.

, API, ?

Edit

, bind . , my_table : name VARCHAR 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 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 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 that data!\n%@", blobData);
}

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

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

More articles:


All Articles