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

Question

我有一个应用程序，它存储一些关于帐户的信息，包括图像。一切都很好:创建了表，可以保存数据，但没有保存图像(我无法理解图像是否未保存或无法从数据库中检索)。我的代码:

数据库表:

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

我已经在模拟器上测试过了，图像数据是(NSDATA，根据 NSLog):数据是:<3f>

请帮帮我!!!

Answer 1

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。