Tried to store a image into sqlite and when I try to retrieve it it's one byte instead of 2 million bytes

Question

When I try to store an image into sqlite, I have converted that image into NSData and there are more than 2,000,000 bytes in the imageData.

When I try to retrieve it from the sqlite database, it became ONE byte.

Here's my code:

Insert image into sqlite:

    -(void)INSERTCLAIMATTACHMENT:(NSString *)tablename
                    PARENTID:(long long)parentid
           ATTACHMENTCONTENT:(UIImage *)attachmentimage
              ATTACHMENTNAME:(NSString *)attachmentname
{
    sqlite3_stmt *statement;
    const char *dbPath = [_databasePath UTF8String];

    Global * g = [[Global alloc]init];
    NSData *imageData = [[NSData alloc]init];
    imageData = UIImagePNGRepresentation(attachmentimage);

    if(sqlite3_open(dbPath, &_DB) == SQLITE_OK)
    {
        NSString *insertSQL = [NSString stringWithFormat:@"INSERT INTO CLAIM_ATTACHMENT_ITEM ('PARENT_ID', 'CLAIM_ATTACHMENT_CONTENT', 'CLAIM_ATTACHMENT_NAME', 'CLAIM_ATTACHMENT_SIZE') VALUES ('%lld', '?', '%@', '%lu')", parentid,attachmentname, [imageData length]];
        const char *insert_statement = [insertSQL UTF8String];

        if(sqlite3_prepare_v2(_DB,insert_statement, -1, &statement, NULL) == SQLITE_OK)
           {
               sqlite3_bind_blob(statement, 2, [imageData bytes], [imageData length], SQLITE_TRANSIENT);
               if(sqlite3_step(statement) == SQLITE_DONE)
               {
                   [g showCustomAlertView:@"Successfull" message:@"Attachment has been added into database."];
               }
               else{
                   [g showCustomAlertView:@"Failure" message:[NSString stringWithFormat:@"%s", sqlite3_errmsg(_DB)]];
               }

           }
        else{
            [g showCustomAlertView:@"Failure" message:[NSString stringWithFormat:@"%s", sqlite3_errmsg(_DB)]];
        }

    }
    else{
        [g showCustomAlertView:@"Failure" message:[NSString stringWithFormat:@"%s", sqlite3_errmsg(_DB)]];
    }
    sqlite3_close(_DB);
}

Retrieve image from sqlite

-(NSMutableArray *)SELECTATTACHMENTFROMCLAIMATTACHMENTITEM:(long long)rowID
{
    sqlite3_stmt *statement;
    const char *dbPath = [_databasePath UTF8String];

    Global * g = [[Global alloc]init];

    NSMutableArray *attachmentArray = [[NSMutableArray alloc]init];

    if(sqlite3_open(dbPath, &_DB) == SQLITE_OK)
    {
        NSString *querySQL = [NSString stringWithFormat:@"SELECT * FROM CLAIM_ATTACHMENT_ITEM WHERE PARENT_ID = '%lld'", rowID];
        const char *query_Statement = [querySQL UTF8String];

        if(sqlite3_prepare_v2(_DB, query_Statement, -1, &statement, NULL) == SQLITE_OK)
        {
            while(sqlite3_step(statement) == SQLITE_ROW)
            {
                AttachmentItem * attachment = [[AttachmentItem alloc]init];
                NSData *attachmentImageData = [[NSData alloc]initWithBytes:sqlite3_column_blob(statement, 2) length:sqlite3_column_bytes(statement, 2)];

                UIImage *attachmentImage = [UIImage imageWithData:attachmentImageData];

                NSString *attachmentName = [[NSString alloc]initWithUTF8String:sqlite3_column_text(statement, 3)];
                attachment.attachmentImage = attachmentImage;
                attachment.attachmentName = attachmentName;
                [attachmentArray addObject:attachment];
            }
        }
        else
        {
            [g showCustomAlertView:@"Failure" message:[NSString stringWithFormat:@"%s", sqlite3_errmsg(_DB)]];
        }
    }
    else
    {
        [g showCustomAlertView:@"Failure" message:[NSString stringWithFormat: @"%s",sqlite3_errmsg(_DB)]];
    }


    sqlite3_close(_DB);

    return attachmentArray;

}

What happen? Why the image data shrinked into one single byte for me :( I'm guessing that the blob cannot store that size of data maybe?

FYI - It's probably a bad idea to store images in the database. You should write them to the file system and just store the filename in the database. See http://stackoverflow.com/questions/12346879/store-images-in-sqlite-or-just-a-reference-to-it — rmaddy, Mar 23 '16 at 04:15
One other thing. Avoid `SELECT * FROM ...` usage. Explicitly list out the column names instead of using `*`. It will make your code much easier to read and it avoid strange bugs if you later modify the schema and the column order isn't what you expected. — rmaddy, Mar 23 '16 at 04:22

rmaddy · Accepted Answer · 2016-03-23T15:43:42.627

2

Your call to sqlite_bind_blob is passing the wrong field index. You only have one ? in the INSERT statement so the index should be 1, not 2.

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

Additionally, you need to remove the quotes from the ? in the INSERT statement:

NSString *insertSQL = [NSString stringWithFormat:@"INSERT INTO CLAIM_ATTACHMENT_ITEM ('PARENT_ID', 'CLAIM_ATTACHMENT_CONTENT', 'CLAIM_ATTACHMENT_NAME', 'CLAIM_ATTACHMENT_SIZE') VALUES ('%lld', ?, '%@', '%lu')", parentid,attachmentname, [imageData length]];

The main reason to use a ? combined with the appropriate sqlite3_bind_xxx statement instead of building a query using stringWithFormat: is that values are properly quoted and escaped for you.

edited Mar 23 '16 at 15:43

answered Mar 23 '16 at 04:14

rmaddy

314,917
42
532
579

I see... so the parameter detects the index of '?' instead of string format specifier? – Johnny Cheuk Mar 23 '16 at 04:16
The `sqlite3_bind_xxx` functions know nothing of Objective-C and certainly nothing of `NSString` format specifiers. – rmaddy Mar 23 '16 at 04:17
Alright sir you deserve my upvote and acception, thank you – Johnny Cheuk Mar 23 '16 at 04:18
I've tried to fix that but after that it still 1 byte instead of original 2m bytes – Johnny Cheuk Mar 23 '16 at 06:21

CL. · Answer 2 · 2016-03-23T08:25:26.377

1

VALUES ('%lld', '?', '%@', '%lu')

The % replacements are textual, but ? is handled by the SQL engine and works only when the parameter marker appears where it is an expression.

With the quotes, '?' is just a string containing the single character ?.

You have to write the parameter without quotes:

[NSString stringWithFormat:@"INSERT ... VALUES ('%lld', ?, '%@', '%lu')", ...];

And this statement has only a single SQL parameter, so its index must be 1, not 2.

edited Mar 23 '16 at 08:25

answered Mar 23 '16 at 08:23

CL.

173,858
17
217
259

Oh my god that's exactly what I was wrong. Thank you so much! – Johnny Cheuk Mar 23 '16 at 08:25

Tried to store a image into sqlite and when I try to retrieve it it's one byte instead of 2 million bytes

2 Answers2