I can not insert data to SQlite database in object

Question

Possible Duplicate:
Assertion failure error in objective c

I want to try insert some data in to sqlite database table but I got an error like this:

***Assertion failure in -[ViewController buttonClick:],/Users/ds/Desktop/SqliteDeneme/SqliteDeneme/ViewController.m:57

My code is here:

- (IBAction)buttonClick:(id)sender {

NSString *str1 =@"1";
NSString *str2 =@"1";
NSString *str3 =@"0.1";
NSString *str4 =@"0.1";
NSString *str5 =@"0.1";
NSString *str6 =@"0.1";
NSString *str7 =@"deneme";
NSString *str8 =@"1";
NSString *str9 =@"1";
NSString *str10=@"deneme";

NSArray *pathsArray=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask,YES);
NSString *doumentDirectoryPath=[pathsArray objectAtIndex:0];
destinationPath=[doumentDirectoryPath stringByAppendingPathComponent:@"SqliteTestDb.sqlite"];
NSLog(@"database path %@",destinationPath);

if (sqlite3_open([destinationPath UTF8String], &cruddb)==SQLITE_OK) 
{
    NSLog(@"dataBaseOpen");
    // leak happens here, do stuff then call sqlite3_close(database), or move it out of the if/else block.
    if(stmt == nil) {
        const char *sql = "INSERT INTO LabUpdate (IsSuccess, ProducerId, Latitude, Longitude, Altitude, Slope, SampleDate, PackageNo, Status, Description) VALUES (?,?,?,?,?,?,?,?,?,?)";            
        if(sqlite3_prepare_v2(cruddb, sql, -1, &stmt, NULL) == SQLITE_OK){
            //sqlite3_prepare_v2(cruddb, sql, 1, &stmt, NULL);
            sqlite3_bind_int(stmt, 1, [str1 integerValue]);
            sqlite3_bind_int(stmt, 2, [str2 integerValue]);
            sqlite3_bind_double(stmt, 3, [str3 floatValue]);
            sqlite3_bind_double(stmt, 4, [str4 floatValue]);
            sqlite3_bind_double(stmt, 5, [str5 floatValue]);
            sqlite3_bind_double(stmt, 6, [str6 floatValue]);
            sqlite3_bind_text(stmt, 7, [str7 UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_int(stmt, 8, [str8 integerValue]);
            sqlite3_bind_int(stmt, 9, [str9 integerValue]);
            sqlite3_bind_text(stmt, 10, [str10 UTF8String], -1, SQLITE_TRANSIENT);
        }
        else 
            NSAssert1(0, @"Error while creating add statement. '%s'", sqlite3_errmsg(cruddb));

    }

    if(SQLITE_DONE != sqlite3_step(stmt))
        NSAssert1(0, @"Error while inserting data. '%s'", sqlite3_errmsg(cruddb));
    else
        //SQLite provides a method to get the last primary key inserted by using sqlite3_last_insert_rowid
        recordID = sqlite3_last_insert_rowid(cruddb);

    //Reset the add statement.
    sqlite3_reset(stmt);

}
else {
    sqlite3_close(cruddb);
    NSLog(@"dataBaseNotOpen");
    NSAssert1(0, @"Error while opening database '%s'", sqlite3_errmsg(cruddb));

}   

}

How can I solve this problem? I put a breakpoint and I saw it is not entered after this line:

if(sqlite3_prepare_v2(cruddb, sql, -1, &stmt, NULL) == SQLITE_OK){

this is my database table and colums:

Answer 1

Can I suggest you use a different technique to binding columns:

NSString* SQL = [NSString stringWithFormat:@"INSERT INTO table1(col1,col2,col3) VALUES(%i,'%@',%i)",number,Surname,age];

where number and age are int's and surname is an NSString* (notice the quotes around strings in the format).

You can execute this command with code like this:

sqlite3_stmt *queryHandle  = [self prepare:SQL];

if (sqlite3_step(queryHandle) != SQLITE_DONE) 
{
     NSLog(@"ExecuteNonQuery has error");
     NSLog( @"Failed from sqlite3_step. Error is:  %s", sqlite3_errmsg(database) );

}
else
{
    int rowsaffected = sqlite3_changes(database);
}

The general prepare function would look like this:

-(sqlite3_stmt*)prepare:(NSString*)query
{
   sqlite3_stmt *queryHandle;


   const char *sqlStatement = (const char *) [query UTF8String];

   if(sqlite3_prepare_v2(database, sqlStatement, -1, &queryHandle, NULL) != SQLITE_OK) 
   {
       int error = sqlite3_prepare_v2(database, sqlStatement, -1, &queryHandle, NULL);

       NSLog( @"Failed from sqlite3_prepare_v2. Error is:  %s", sqlite3_errmsg(database) );

       NSLog(@"Compiled Statement has error code:%i:%@",error,query);
}

return queryHandle;

}

This is the way I use and is quick, can be generalised to sub functions like the prepare.

Hope this helps.

Answer 2

May be it works

if(sqlite3_open([destinationPath UTF8String], &cruddb) ==SQLITE_OK) {

    sqlite3_prepare_v2(cruddb, "BEGIN TRANSACTION", -1, &compiledStmt, NULL);
    sqlite3_step(compiledStmt);
    sqlite3_finalize(compiledStmt);

    const char *sql = "INSERT INTO LabUpdate (IsSuccess, ProducerId, Latitude, Longitude, Altitude, Slope, SampleDate, PackageNo, Status, Description) VALUES (?,?,?,?,?,?,?,?,?,?)"; 
    if(sqlite3_prepare_v2(cruddb, sql, -1, &stmt, NULL) == SQLITE_OK){  

        sqlite3_bind_int(stmt, 1, [str1 integerValue]);
        sqlite3_bind_int(stmt, 2, [str2 integerValue]);
        sqlite3_bind_double(stmt, 3, [str3 floatValue]);
        sqlite3_bind_double(stmt, 4, [str4 floatValue]);
        sqlite3_bind_double(stmt, 5, [str5 floatValue]);
        sqlite3_bind_double(stmt, 6, [str6 floatValue]);
        sqlite3_bind_text(stmt, 7, [str7 UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_int(stmt, 8, [str8 integerValue]);
        sqlite3_bind_int(stmt, 9, [str9 integerValue]);
        sqlite3_bind_text(stmt, 10, [str10 UTF8String], -1, SQLITE_TRANSIENT);

            NSUInteger err = sqlite3_step(compiledStmt);
            if (err != SQLITE_DONE){
                NSLog(@"error while binding %d %s",err, sqlite3_errmsg(database));
            }
            sqlite3_reset(compiledStmt);
        sqlite3_finalize(compiledStmt);     
    } else {
        NSLog(@"Invalid Query");
    }

    sqlite3_prepare_v2(cruddb, "END TRANSACTION", -1, &compiledStmt, NULL);
    sqlite3_step(compiledStmt);
    sqlite3_finalize(compiledStmt); 
    sqlite3_close(cruddb);