sqlite3 insert and read BLOB data in database

Question

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?

Environment: iPhone SDK 4.1 SQLite3 database.

This code fails:

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

This question is asking the same thing as yours, and the answers there should address your needs: http://stackoverflow.com/questions/643682/reading-and-writing-images-to-an-sqlite-db-for-iphone-use — Brad Larson, Nov 18 '10 at 22:06

score 12 · Answer 1 · edited Jan 29 '13 at 19:47

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

thanks for posting,this code works well.but when i change the name of the image from "flower.png" to some other like "xyz.png" then it display the previous image in imageview so can you point out the reason....... — , Sep 30 '11 at 10:58
maby you need to go to appropriate record in database first, and only then read image from it, and now it always takes first record. try that, I really don't remember it was a year ago. — yozhik, Sep 30 '11 at 12:20
in NSLog(@"Error while creating update statement:%@", sqlite3_errmsg(database)); %s formatting should be used — Valeriy Van, Jan 29 '13 at 19:05
constant SQLITE_BLOB is wrongly used in sqlite3_bind_blob call. SQLITE_TRANSIENT or SQLITE_STATIC should be used there. http://www.sqlite.org/c3ref/bind_blob.html — Valeriy Van, Jan 29 '13 at 19:37
Be careful with the use of SQLITE_TRANSIENT usage. Depending on your case it may cause a BAD_ACCESS error. This was my situation until I changed it to SQLITE_STATIC. — garthoid, Aug 17 '13 at 03:05

pestaa · Answer 2 · 2010-11-18T14:12:34.547

1

Apart from choosing your client or programming interface, usage does not differ from any other string field.

[Update] I haven't had the luck to develop for the iPhone yet, but I believe it's the same as any other SELECT or INSERT query. Make sure to encode the BLOB and enclose it with single apostrophes (') like strings.

edited Nov 18 '10 at 14:12

answered Nov 18 '10 at 14:03

pestaa

4,749
2
23
32

score 0 · Answer 3 · answered Sep 28 '12 at 06:16

//-----insert IMAGE

  - (void)setImage:(UIImage *)theImage
 {

  if(sqlite3_open([databasePath UTF8String],&myDatabase)==SQLITE_OK)
  {    
    NSString *insertProgrammeSql = @"INSERT INTO imageTable (imageName) VALUES (?)";

sqlite3_stmt *statement;

   if (sqlite3_prepare_v2(myDatabase, [insertProgrammeSql cStringUsingEncoding:NSUTF8StringEncoding], -1, &statement, NULL) == SQLITE_OK) {

    NSData *imageData = UIImagePNGRepresentation(theImage);

         sqlite3_bind_blob(statement, 1, [imageData bytes], [imageData length], SQLITE_TRANSIENT);

    sqlite3_step(statement);
}
  sqlite3_close(myDatabase);
//  return YES;    
}   


  }

//--------get image

   -(UIImage *)getImage

    {

    UIImage *image = nil;


if(sqlite3_open([databasePath UTF8String],&myDatabase)==SQLITE_OK)
{   

NSString *selectSql = @"SELECT image FROM imageTable";

sqlite3_stmt *statement;
if (sqlite3_prepare_v2(myDatabase, [selectSql UTF8String], -1, &statement, NULL) == SQLITE_OK)
{
    int length = sqlite3_column_bytes(statement, 0);
    NSData *imageData = [NSData dataWithBytes:sqlite3_column_blob(statement, 0 ) length:length];

    image = [UIImage imageWithData:imageData];



  sqlite3_finalize(statement);

  }
     sqlite3_close(myDatabase);
  }
 return image;
 }

score 0 · Answer 4 · answered Feb 15 '14 at 18:35

This code fails:

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

You are calling the SQLite functions in the correct order. According to the SQLite docs:

The safest and easiest to remember policy is to invoke these routines in one of the following ways:

sqlite3_column_text() followed by sqlite3_column_bytes()

sqlite3_column_blob() followed by sqlite3_column_bytes()

sqlite3_column_text16() followed by sqlite3_column_bytes16()

In other words, you should call sqlite3_column_text(), sqlite3_column_blob(), or sqlite3_column_text16() first to force the result into the desired format, then invoke sqlite3_column_bytes() or sqlite3_column_bytes16() to find the size of the result. Do not mix calls to sqlite3_column_text() or sqlite3_column_blob() with calls to sqlite3_column_bytes16(), and do not mix calls to sqlite3_column_text16() with calls to sqlite3_column_bytes().

However, buff2 is an integer, not a data pointer. sqlite3_column_bytes tells you how many bytes are in the blob.

score 0 · Answer 5 · answered Nov 18 '10 at 21:34

0

why not use fmdb by Gus Mueller et al, of Flying Meat? http://flyingmeat.com/

https://github.com/ccgus/fmdb

If you find fmdb useful, go and buy one of the excellents apps of Flying Meat. VoodooPad or Acron.

answered Nov 18 '10 at 21:34

Cesar A. Rivas

1,355
1
10
13

sqlite3 insert and read BLOB data in database

5 Answers5

Linked

Related