Inserting NSData into SQLite on the iPhone

Question

Hi all,

So far I've managed to create this method for inserting into a SQLite database on the iPhone:

- (void) insertToDB :(NSString *)Identifier :(NSString *)Name
{
    sqlite3 *database;

    if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK)
    {
        char *sql1 = "INSERT INTO table VALUES ('";
        const char *sql2 = [Identifier cStringUsingEncoding:[NSString defaultCStringEncoding]];
        char *sql3 = "', '";
        const char *sql4 = [Name cStringUsingEncoding:[NSString defaultCStringEncoding]];
        char *sql5 = "')";

        char *sqlStatement[255];
        strcpy(sqlStatement, sql1);
        strcat(sqlStatement, sql2);
        strcat(sqlStatement, sql3);
        strcat(sqlStatement, sql4);
        strcat(sqlStatement, sql5);

        sqlite3_stmt *compiledStatement;

        if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)
        {
            sqlite3_last_insert_rowid(database);
            sqlite3_reset(compiledStatement);
        }

        sqlite3_finalize(compiledStatment);
    }
    sqlite3_close(database);
}

Now I'm looking at storing an image in the database. So far I've found this:

UIImage *cachedImage = [UIImage imageNamed:@"Icon.png"];
NSData *dataForImage = UIImagePNGRepresentation(cachedImage);

But i'm having trouble trying to insert this NSData into the char array which makes the sqlStatement. Anyone got an idea how to do this?

(I have a field in the database of type blob for this).

Thanks

Answer 1

I would use sqlite3_stmt instead of a string. Then, you could use sqlite3_bind_blob to bind the blob to the prepared statement.

sqlite3_stmt *insert_statement;     
char *sql = "INSERT INTO table (blobcolumn, column2, column3) VALUES (? , ?, ?)" ;
if(sqlite3_prepare_v2(database, sql, -1, &insert_statement, NULL) != SQLITE_OK)
          {
              //handle error
          } 

sqlite3_bind_blob(insert_statement, 1, [dataForImage bytes], [dataForImage length], NULL);

But, really, it would be best for performance to store the image on disk and the path in the database.

Another way to do it, one that I use to send image data in XML, is to base 64 encode the data. I have it here as a category on NSString:

static char base64EncodingTable[64] = {
'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P',
'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'a', 'b', 'c', 'd', 'e', 'f',
'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v',
'w', 'x', 'y', 'z', '0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '+', '/'
};

@implementation NSString (NSStringCategories)

+ (NSString *) base64StringFromData: (NSData *)data length: (int)length {
    unsigned long ixtext, lentext;
    long ctremaining;
    unsigned char input[3], output[4];
    short i, charsonline = 0, ctcopy;
    const unsigned char *raw;
    NSMutableString *result;

    lentext = [data length]; 
    if (lentext < 1)
        return @"";
    result = [NSMutableString stringWithCapacity: lentext];
    raw = [data bytes];
    ixtext = 0; 

    while (true) {
        ctremaining = lentext - ixtext;
        if (ctremaining <= 0) 
            break;        
        for (i = 0; i < 3; i++) { 
            unsigned long ix = ixtext + i;
            if (ix < lentext)
                input[i] = raw[ix];
            else
                input[i] = 0;
        }
        output[0] = (input[0] & 0xFC) >> 2;
        output[1] = ((input[0] & 0x03) << 4) | ((input[1] & 0xF0) >> 4);
        output[2] = ((input[1] & 0x0F) << 2) | ((input[2] & 0xC0) >> 6);
        output[3] = input[2] & 0x3F;
        ctcopy = 4;
        switch (ctremaining) {
            case 1: 
                ctcopy = 2; 
                break;
            case 2: 
                ctcopy = 3; 
                break;
    }

    for (i = 0; i < ctcopy; i++)
        [result appendString: [NSString stringWithFormat: @"%c",             base64EncodingTable[output[i]]]];

    for (i = ctcopy; i < 4; i++)
        [result appendString: @"="];

    ixtext += 3;
    charsonline += 4;

    if ((length > 0) && (charsonline >= length))
        charsonline = 0;
    }
    return result;
}