iphone - 在sqlite3 iphone中插入和读取图像数据

我有一个应用程序，可以存储一些有关帐户的信息，包括图像。一切都很棒：创建了表，可以保存数据，但是图像却不行（我不明白该图像是否未保存或无法从db中检索）。我的代码：

数据库表：

 static const char *accountsTable = "CREATE TABLE IF NOT EXISTS tbl_accounts (unique_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, provider_id INTEGER, login TEXT, password TEXT, threshold INTEGER, is_need_push INTEGER, comment TEXT, image BLOB)";

我的插入方法：

-(BOOL) createAccountWithAccountData:(AccountsData *) accountData
{
    NSInteger pushNotifications = accountData.isNeedPushNotifications ? 1 : 0;
    const char *dbPath = [dataBasePath UTF8String];
    if (sqlite3_open(dbPath, &database) == SQLITE_OK) {
        NSString *insertSqlStatement = [NSString stringWithFormat: @"INSERT INTO tbl_accounts (provider_id, login, password, threshold, is_need_push, comment, image) values ('%d', '%@', '%@', '%d', '%d', '%@', '?')", accountData.providerId, accountData.logIn, accountData.password, accountData.threshold, pushNotifications, accountData.comment];

        const char *insertStmt = [insertSqlStatement UTF8String];

        if (sqlite3_prepare_v2(database, insertStmt, -1, &sqlStatement, NULL) == SQLITE_OK ) {
            if (accountData.image != nil) {
                NSLog(@"Image not null");
                NSData *imageData = UIImageJPEGRepresentation(accountData.image, 1.0);
                sqlite3_bind_blob(sqlStatement, 7, [imageData bytes], [imageData length], nil);
            } else {
                NSLog(@"image is nil");
            }
            if (sqlite3_step(sqlStatement) == SQLITE_DONE)
            {
                NSLog(@"Successfully created account data");
                sqlite3_reset(sqlStatement);
                sqlite3_close(database);
                return YES;
            } else {
                NSLog(@"Unable to create account data");
                sqlite3_reset(sqlStatement);
                sqlite3_close(database);
                return NO;
            }
        }
    }
    return NO;
}

而我得到所有帐户的方法：

-(NSArray *) getAllAccounts
{
    const char *dbPath = [dataBasePath UTF8String];
    NSMutableArray *allAccounts = [[NSMutableArray alloc] init];
    if (sqlite3_open(dbPath, &database) == SQLITE_OK) {
        NSLog(@"DB Opened");
        NSString *findSqlStatement = [NSString stringWithFormat: @"SELECT * FROM tbl_accounts"];
        const char *findStmt = [findSqlStatement UTF8String];
        if (sqlite3_prepare_v2(database, findStmt, -1, &sqlStatement, NULL) == SQLITE_OK)
        {
            NSLog(@"statement was prepared");
            while (sqlite3_step(sqlStatement) == SQLITE_ROW)
            {
                NSLog(@"Into the while loop");
                NSInteger uniqueId = [[NSString stringWithUTF8String:(const char *) sqlite3_column_text(sqlStatement, 0)] integerValue];
                NSInteger providerId = [[NSString stringWithUTF8String:(const char *) sqlite3_column_text(sqlStatement, 1)] integerValue];
                NSString *logIn = [NSString stringWithUTF8String:
                                  (const char *) sqlite3_column_text(sqlStatement, 2)];
                NSString *password = [NSString stringWithUTF8String:
                                   (const char *) sqlite3_column_text(sqlStatement, 3)];
                NSInteger threshold = [[NSString stringWithUTF8String:(const char *) sqlite3_column_text(sqlStatement, 4)] integerValue];

                NSInteger pushNotif = [[NSString stringWithUTF8String:(const char *) sqlite3_column_text(sqlStatement, 5)] integerValue];

                NSString *comment = [NSString stringWithUTF8String:
                                      (const char *) sqlite3_column_text(sqlStatement, 6)];

                BOOL isNeedNotif = [self convertNSInteger:pushNotif];

                int length = sqlite3_column_bytes(sqlStatement, 7);
                NSData *data = [[NSData alloc] initWithBytes:sqlite3_column_blob(sqlStatement, 7) length:length];

                NSLog(@"itemLogin: %@", logIn);
                NSLog(@"password : %@", password);
                NSLog(@"data is: %@", data);
                NSLog(@"comment is: %@", comment);
                NSLog(@"isNeedNotif: %hhd", isNeedNotif);

                UIImage *imageFromDb = nil;
                if (data != nil)
                    imageFromDb = [[UIImage alloc] initWithData:data];
                else
                    NSLog(@"No image");

                if (imageFromDb) {
                    NSLog(@"Image");
                } else {
                    NSLog(@"NoImage");
                }

                AccountsData *item = [[AccountsData alloc] initWithProviderId:providerId logIn:logIn password:password threshold:threshold isNeedPushNotifications:isNeedNotif comment:comment image:imageFromDb];
                item.unique_id = uniqueId;

                [allAccounts addObject:item];
                NSLog(@"Item added to array");
                NSLog(@"Array count: %d", [allAccounts count]);
            }
        }
        sqlite3_reset(sqlStatement);
        sqlite3_close(database);
    }

    return allAccounts;
}

我已经在模拟器上对其进行了测试，图像数据为（根据NSLog，为NSDATA）：
数据是：

请帮我！！！

最佳答案

INSERT INTO tbl_accounts (..., image) values (..., '?')

您要插入的字符串由单个字符?组成。

参数标记不得加引号：

INSERT INTO tbl_accounts (..., image) values (..., ?)

此外，sqlite3_bind_blob的第二个参数是参数号，并且该语句只有一个参数。它必须是1，而不是7。

此外，仅当您要重用该语句时，才需要sqlite3_reset（否则无害）。
您永远不会忘记的是，在使用完语句之后以及关闭数据库之前，调用sqlite3_finalize。
在此代码中，只需将sqlite3_reset替换为sqlite3_finalize。