在 iPhone 上将 NSData 插入 SQLite [英] Inserting NSData into SQLite on the iPhone

问题描述

到目前为止，我已经成功地创建了这个方法来插入 iPhone 上的 SQLite 数据库:

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

但是我在尝试将此 NSData 插入到创建 sqlStatement 的 char 数组中时遇到了麻烦.有人知道怎么做吗?

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?

(我在数据库中有一个 blob 类型的字段).

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

谢谢

推荐答案

我会使用 sqlite3_stmt 而不是字符串.然后，您可以使用 sqlite3_bind_blob 将 blob 绑定到准备好的语句.

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.

另一种方法是，我用来以 XML 格式发送图像数据的方法是对数据进行 base 64 编码.我在这里将它作为 NSString 上的一个类别:

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

这篇关于在 iPhone 上将 NSData 插入 SQLite的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！