iPhone - Using sql database - insert statement failing

I'm using sqlite database in my iphone app. I've a table which has 3 integer columns. I'm using following code to write to that database table.

-(BOOL)insertTestResult {
NSArray* paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString* documentsDirectory = [paths objectAtIndex:0];
NSString* dataBasePath = [documentsDirectory stringByAppendingPathComponent:@"test21.sqlite3"];     

BOOL success = NO;
sqlite3* database = 0;
if(sqlite3_open([dataBasePath UTF8String], &database) == SQLITE_OK) 
{
    BOOL res = (insertResultStatement == nil) ? createStatement(insertResult, &insertResultStatement, database) : YES;
    if(res)
    {
        int i = 1;
        sqlite3_bind_int(insertResultStatement, 0, i);
        sqlite3_bind_int(insertResultStatement, 1, i);
        sqlite3_bind_int(insertResultStatement, 2, i);

        int err = sqlite3_step(insertResultStatement);
        if(SQLITE_ERROR == err) 
        {
            NSAssert1(0, @"Error while inserting Result. '%s'", sqlite3_errmsg(database));
            success = NO;
        }
        else 
        {
            success = YES;
        }
        sqlite3_finalize(insertResultStatement);
        insertResultStatement = nil;            
    }
}
sqlite3_close(database);    
return success;}

The command sqlite3_step is always giving err as 19. I'm not able to understand where's the issue.

Tables are created using following queries:

CREATE TABLE [Patient] (PID integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,PFirstName text NOT NULL,PLastName text,PSex text NOT NULL,PDOB text NOT NULL,PEducation text NOT NULL,PHandedness text,PType text)

CREATE TABLE PatientResult(PID INTEGER,PFreeScore INTEGER NOT NULL,PForcedScore INTEGER NOT NULL,FOREIGN KEY (PID) REFERENCES Patient(PID))

I've only one entry in Patient table with PID = 1

BOOL createStatement(const char* query, sqlite3_stmt** stmt, sqlite3* database)开发者_开发技巧{
BOOL res = (sqlite3_prepare_v2(database, query, -1, stmt, NULL) == SQLITE_OK);
if(!res) 
    NSLog( @"Error while creating %s => '%s'", query, sqlite3_errmsg(database));
return res;}

---

I have updated your code. Do it this way:

-(BOOL)insertTestResult {
    NSArray* paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString* documentsDirectory = [paths objectAtIndex:0];
    NSString* dataBasePath = [documentsDirectory stringByAppendingPathComponent:@"test21.sqlite3"];     

    BOOL success = NO;
    sqlite3* database = 0;
    if(sqlite3_open([dataBasePath UTF8String], &database) == SQLITE_OK) 
    {
        BOOL res = (insertResultStatement == nil) ? createStatement(insertResult, &insertResultStatement, database) : YES;
        if(res)
        {
            int i = 1;
            sqlite3_bind_int(insertResultStatement, 1, i);  //updated
            sqlite3_bind_int(insertResultStatement, 2, i);  //updated
            sqlite3_bind_int(insertResultStatement, 3, i);  //updated

            int err = sqlite3_step(insertResultStatement);
            if(SQLITE_ERROR == err) 
            {
                NSAssert1(0, @"Error while inserting Result. '%s'", sqlite3_errmsg(database));
                success = NO;
            }
            else 
            {
                success = YES;
            }
            sqlite3_finalize(insertResultStatement);
            insertResultStatement = nil;            
        }
    }
    sqlite3_close(database);    
    return success;
}

The reason is that you have started the index from 0, that is wrong. When you insert in the table of database, you need to start inserting from the index 1. The index 0 start when you try to fetch the data from the database.

---

Google found this, which told me this:

#define SQLITE_CONSTRAINT  19   /* Abort due to constraint violation */

You're violating a constraint somewhere. Either your PID isn't unique or the foreign key to patient isn't referring to a valid patient ID would be my guesses.

---

I made a little change (trial and error basis) and its working. I don't know the reason, why its working........ Here's my change in code:

sqlite3_bind_int(insertResultStatement, 1, i);
sqlite3_bind_int(insertResultStatement, 2, i);
sqlite3_bind_int(insertResultStatement, 3, i);

The change is that, previously my first sqlite3_bind_int second param was started with "0" and not its starting with "1".

If some one find the reason, please update this chain......

---

It'd help if you also posted your createStatement() code. Without that, I'm going to guess that you are failing to specify a value in the INSERT for one of your NOT NULL columns.