iOS SQLite Blob data is saving NULL

Question

I am trying to insert a BLOB data of SignatureView using this code but when i actually browse the database there is null instead of data. My signature table schema is given below.

create table sign(id integer primary key AUTOINCREMENT, image blob,invoiceid integer);

-(void)storeImageData:(NSData *)imageData withInvoiceID:(NSInteger)invoiceID{

    NSString *dbPath = [[[NSBundle mainBundle] resourcePath ]stringByAppendingPathComponent:@"database.sqlite3"];
    const char *dbpath = [dbPath UTF8String];
    sqlite3 *contactDB;

    sqlite3_stmt    *statement;

    NSLog(@"%@",dbPath);
    if (sqlite3_open(dbpath, &contactDB) == SQLITE_OK)
    {
        int invoiceIDINT = (int)invoiceID;
    //
        NSString *insertSQL = [NSString stringWithFormat: @"INSERT INTO sign (image,invoiceid) VALUES (?,%d)", invoiceIDINT];

        const char *insert_stmt = [insertSQL UTF8String];

        sqlite3_prepare_v2(contactDB, insert_stmt, -1, &statement, NULL);
        if (sqlite3_step(statement) == SQLITE_DONE)
        {
            sqlite3_bind_blob(statement, 2, [imageData bytes], [imageData length], SQLITE_TRANSIENT);
            sqlite3_step(statement);

        } else {
            const char *Error = sqlite3_errmsg(database);
            NSString *error = [[NSString alloc]initWithUTF8String:Error];
            UIAlertView *view = [[UIAlertView alloc]initWithTitle:@"Error2" message:[NSString stringWithFormat:@"Last inserted ID: %@",error] delegate:nil cancelButtonTitle:@"Cancel" otherButtonTitles:nil, nil];
            [view show];
        }
        sqlite3_finalize(statement);
        sqlite3_close(contactDB);
    }


}

Answer 1

1. Always check the result of sqlite3_prepare_v2. If it fails, log the problem using sqlite3_errmsg.
2. Only call sqlite3_finalize if sqlite3_prepare_v2 succeeds.
3. You get NULL for the blob because your call to sqlite3_bind_blob is passing the wrong column index. It should be 1, not 2 since your want to bind to the first ? in your INSERT statement.
4. Why the inconsistency? Why do you use stringWithFormat to set the value for the invoiceid column and then use sqlite_bind_xxx for the image column? You should bind both. Never use stringWithFormat to build a query.
5. You call sqlite3_step twice. Only call it once and bind your values before you call it.
6. You appear to be writing to a database inside your app's resource bundle. You can't do that on a real device. It works in the simulator but not on real iOS devices. You need to put your database file in the Documents folder.

Given all of the above, your code should be something like this:

-(void)storeImageData:(NSData *)imageData withInvoiceID:(NSInteger)invoiceID{
    // This is wrong - you need to update this to use the Documents folder
    NSString *dbPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"database.sqlite3"];
    const char *dbpath = [dbPath UTF8String];
    NSLog(@"%@",dbPath);

    sqlite3 *contactDB;
    if (sqlite3_open(dbpath, &contactDB) == SQLITE_OK)
    {
        const char *insert_stmt = "INSERT INTO sign (image, invoiceid) VALUES (?, ?)";

        sqlite3_stmt *statement;
        if (sqlite3_prepare_v2(contactDB, insert_stmt, -1, &statement, NULL) == SQLITE_OK) {
            sqlite3_bind_blob(statement, 1, [imageData bytes], [imageData length], SQLITE_TRANSIENT);
            sqlite3_bind_int(statement, 2, (int)invoiceID);

            if (sqlite3_step(statement) == SQLITE_DONE) {
                // Row inserted successfully
            } else {
                const char *Error = sqlite3_errmsg(contactDB);
                NSString *error = [[NSString alloc] initWithUTF8String:Error];
                UIAlertView *view = [[UIAlertView alloc] initWithTitle:@"Error2" message:[NSString stringWithFormat:@"Last inserted ID: %@",error] delegate:nil cancelButtonTitle:@"Cancel" otherButtonTitles:nil, nil];
                [view show];
            }

            sqlite3_finalize(statement);
        } else {
            NSLog(@"Unable to prepare statement %s: %s", insert_stmt, sqlite3_errmsg(contactDB));
        }

        sqlite3_close(contactDB);
    } else {
        NSLog(@"Unable to open database at path %@: %s", dbPath, sqlite3_errmsg(contactDB));
    }
}