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?

Answer 1

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.