sqlite3 blob problem

Question

Possible Duplicate:
sqlite3 insert and read BLOB data in database

I try to read-write BLOB data to database. But when I try to read blob data - the size of it is 0. And all my fields data moved. here is the code:

Please, assist me, what I'm doing wrong?? Thanx!

-(int) addUser: (MyData *) user table: (NSString *) tableName

{

sqlite3_stmt *updStmt =nil; 

const char *sql = "INSERT INTO Users (Name, Surname, Email, Phone, Image) VALUES (?, ?, ?, ?, ?);";
int res = SQLITE_ERROR;
res = sqlite3_prepare_v2(database, sql, -1, &updStmt, NULL);

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

res = sqlite3_bind_text(updStmt, 1, [user.name UTF8String], -1, SQLITE_TRANSIENT);
res = sqlite3_bind_text(updStmt, 2, [user.surname UTF8String], -1, SQLITE_TRANSIENT);  
res = sqlite3_bind_text(updStmt, 3, [user.email UTF8String], -1, SQLITE_TRANSIENT);
res = sqlite3_bind_text(updStmt, 4, [user.telephone UTF8String], -1, SQLITE_TRANSIENT); 

NSData *imageData = [NSData dataWithData: UIImagePNGRepresentation(user.image)];
res = sqlite3_bind_blob(updStmt, 5, imageData, -1, SQLITE_BLOB);

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

sqlite3_reset(updStmt);

return res;

}

and here how can I read this fields:

-(MyData *) createUserWithDetailsByID: (int) uID
{
    NSString *query = [NSString stringWithFormat: @SELECT_USER_ALLDATA_BY_ID, uID];
    MyData *user = nil;

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

    if (res == SQLITE_OK)
    {
        if (sqlite3_step(statement) == SQLITE_ROW)
        {
            //if there are results in row
            char *buf = NULL;
            user = [[MyData alloc] init];

            int num = sqlite3_column_int(statement, 0);
            user.uID = num;

            buf = (char *)sqlite3_column_text(statement, 1);
            if (buf)
                user.name = [NSString stringWithFormat: @"%s", buf];
            else
                user.name = @"";    

            buf = (char *)sqlite3_column_text(statement, 2);
            if (buf)
                user.surname = [NSString stringWithFormat: @"%s", buf];
            else
                user.surname = @"";

            buf = (char *)sqlite3_column_text(statement, 3);
            if (buf)
                user.email = [NSString stringWithFormat: @"%s", buf];
            else
                user.email = @"";

            buf = (char *)sqlite3_column_text(statement, 4);
            if (buf)
                user.telephone = [NSString stringWithFormat: @"%s", buf];
            else
                user.telephone = @"";

            int len = 0;
            len = sqlite3_column_bytes(statement, 5);

            NSData *imgData = [[NSData alloc] initWithBytes: sqlite3_column_blob(statement, 5) length: len];           

            UIImage *i;
            [i initWithData:imgData];

        }
    }
    sqlite3_finalize(statement);

    return user; 
}

AechoLiu · Accepted Answer · 2012-12-07T05:39:26.487

5

NSData *imageData = [NSData dataWithData: UIImagePNGRepresentation(user.image)];
res = sqlite3_bind_blob(updStmt, 5, [imageData bytes], [imageData length], SQLITE_BLOB);

I think when you writing, you should use [imageData bytes] and [imageData length]. NSData is an obj-C class, it's not an array of unsigned char.

edited Dec 07 '12 at 05:39

answered Nov 18 '10 at 21:38

AechoLiu

17,522
9
100
118

score 4 · Answer 2 · edited Aug 30 '12 at 10:07

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

-(void) addToDB
{
 NSLog(@"\nCreating 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:%@", 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] , NULL);

 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(@"\nReading 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);
}

sqlite3 blob problem

2 Answers2