sqlite3 在数据库中插入和读取 BLOB 数据

2021-12-19 00:00:00 blob select insert sqlite

我需要将 BLOB 数据读取和写入数据库.这是我的结构表

I need to read and write BLOB data to a database. Here is my structure table

    #define CREATE_TABLE_USERS_SQL "CREATE TABLE IF NOT EXISTS %@ ( 
UserID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
Name VARCHAR(50), 
Image BLOB);"

如何将其插入数据库，然后从中检索?

How can I insert it into database, and then retrieve from it?

环境:iPhone SDK 4.1 SQLite3 数据库.

Environment: iPhone SDK 4.1 SQLite3 database.

此代码失败:

        NSData * buf2 = [[NSData alloc] init];
        sqlite3_column_blob(statement, 5);
        buf2 = sqlite3_column_bytes(statement, 5);
        user.image = [UIImage imageWithData: buf2];

推荐答案

谢谢大家！！在您的帮助下，我解决了问题，并希望像我一样为未来的初学者分享结果.)

Thanx all!! With yours help I'v solved problem and want to share results for future beginners like I am.)

-(void) addToDB
{
    NSLog(@"
Creating db");
    NSString *str = @"CREATE TABLE IF NOT EXISTS Images (image1 BLOB);";
    int res = SQLITE_ERROR;


    res = sqlite3_open([@"aa.sql" UTF8String], &database);
    res = sqlite3_exec(database, [str UTF8String], NULL, NULL, NULL);

    sqlite3_stmt *updStmt =nil; 

    const char *sql = "INSERT INTO Images (image1) VALUES (?);";
    res = sqlite3_prepare_v2(database, sql, -1, &updStmt, NULL);

    if(res!= SQLITE_OK)
    {
        NSLog(@"Error while creating update statement:%s", sqlite3_errmsg(database));
    }

    UIImage *img = [UIImage imageNamed: @"flower.png"];
    NSData *imageData = [NSData dataWithData: UIImagePNGRepresentation(img)];

    res = sqlite3_bind_blob(updStmt, 1, [imageData bytes], [imageData length] , SQLITE_TRANSIENT);

    if((res = sqlite3_step(updStmt)) != SQLITE_DONE)
    {
        NSLog(@"Error while updating: %@", sqlite3_errmsg(database));
        sqlite3_reset(updStmt);
    } 

    res = sqlite3_reset(updStmt);
    res = sqlite3_close(database);
}

-(void) readFromDB
{
    NSLog(@"
Reading from db");

    NSString *query = @"SELECT image1 from Images";
    int res = SQLITE_ERROR;
    int len = 0;

    res = sqlite3_open([@"aa.sql" UTF8String], &database);

    sqlite3_stmt *statement;
    res = sqlite3_prepare_v2 (database, [query UTF8String], -1, &statement, nil);

    if (res == SQLITE_OK)
    {
        if (sqlite3_step(statement) == SQLITE_ROW)
        {
            len = sqlite3_column_bytes(statement, 0);
            NSData *imgData = [[NSData alloc] initWithBytes: sqlite3_column_blob(statement, 0) length: len];           


            UIImage *img = [[UIImage alloc] initWithData:imgData];

            self.view1.image = img;

        }
    }
    sqlite3_finalize(statement);

    res = sqlite3_close(database);
}

相关文章