IPhone Development - sqlite3_bind_int not working

Question

Hi guys, i'm trying to insert some data on a database using this code:

-(void)insertLocationOnDatabase:(LocationType *)aLocation {
    sqlite3_stmt *stmt;
    int location = [aLocation.locationID intValue];
    NSLog(@"Location ID: %i", location);
    const char *sql = "insert into tbl_location values (?,?,?,?)";
    if (sqlite3_prepare_v2(database, sql, -1, &stmt, NULL) == SQLITE_OK) {
        sqlite3_bind_int(stmt, 0, location);
        sqlite3_bind_text(stmt, 1, [aLocation.Description UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(stmt, 2, [aLocation.isActive UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(stmt, 3, [aLocation.sequenceOrder UTF8String], -1, SQLITE_TRANSIENT);
        if (sqlite3_step(stmt) == SQLITE_DONE) {
            NSLog(@"Location %@ inserted on database",aLocation.Description);
        }
        else {
            NSLog(@"Error on step: %i",sqlite3_errcode(database));
            }
    }
    else {
        NSLog(@"Error on prepare: %i",sqlite3_errcode(database));
    }
}

the problem is on line:

sqlite3_bind_int(stmt, 0, location);

without this line and changing the sql, the code works fine, but when i put this line back, i get this error:

2010-09-17 10:24:01.032 StockControl[944:207] Error on step: 20

From sqlite3.h:

#define SQLITE_MISMATCH    20   /* Data type mismatch */

Somebody knows where is my mistake?

Regards, Claudio

Answer 1

According to the docs for the binding methods in SQLite, the bindings count from one, not zero:

The second argument is the index of the SQL parameter to be set. The leftmost SQL parameter has an index of 1.

That might well lead to a type mismatch.

Answer 2

Why not use following methods?

NSString *sqlCmd = [NSString stringWithFormat:
                   @"insert into tbl_location values (%d,'%@','%@','%@')",
                  location, 
                  aLocation.Description, 
                  aLocation.isActive, 
                  aLocation.sequenceOrder];

const char *sql = [sqlCmd UTF8String];

// ...

I use bind only for large data, for example an image file.