Adding data to SQLite3 DB from within Xcode

Question

I am trying to add 3 pieces of data to a SQLite3 DB. With help from a tutorial, I'm almost there. Below is my code.

I am receiving what I would expect from the NSLogs. I find the DB without problem, I compile the statement to send to the DB without problem, but when the app gets to sqlite3_prepare_v2(database, sqlstatement, -1, &compliedstatement, NULL)==SQLITE_OK I am expecting the data to be added to the DB, but it isn't, and I'm also expecting to display a UIAlertView to tell the user ERROR os SUCCESS. I hope you can see what I do not.

- (IBAction)addData:(id)sender {
    NSString *n = [[NSString alloc] initWithString:[name text]];
    NSString *a = [[NSString alloc] initWithString:[age text]];
    NSString *c = [[NSString alloc] initWithString:[color text]];
    NSLog(@"%@ - %@ - %@",n,a,c);
    [self insertDataName:n Age:a Color:c];
}

This NSLog returns the text properties as expected.

- (IBAction)hide:(id)sender {
    [name resignFirstResponder];
    [age resignFirstResponder];
    [color resignFirstResponder];

}



- (void)viewDidLoad
{
    [super viewDidLoad];
    DBName = @"mydatabase.sqlite";
    NSArray *documentsPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDir = [documentsPaths objectAtIndex:0];
    DBPath = [documentsDir stringByAppendingPathComponent:DBName];

}


-(void)checkAndCreateDB {
    BOOL Success;
    NSFileManager *fileManager = [NSFileManager defaultManager];
    Success = [fileManager fileExistsAtPath:DBPath];

    if(Success) {
        NSLog(@"DB Found");    
        return;
    }

    NSLog(@"DB Not Found");
    NSString *databasePathFromApp = [[[NSBundle mainBundle]resourcePath]stringByAppendingPathComponent:DBName];
    [fileManager copyItemAtPath:databasePathFromApp toPath:DBPath error:nil];

}

This NSLog returns DB Found

-(void)insertData Name:(NSString*)n Age:(NSString*)a Color:(NSString*)c {
    [self checkAndCreateDB];
    sqlite3 *database;
    if (sqlite3_open([DBPath UTF8String],&database)==SQLITE_OK) {
        NSString *statement;
        sqlite3_stmt *compliedstatement;
        statement = [[NSString alloc] initWithFormat:@"insert into table1 values ('%@', '%@', '%@')",n,a,c];
        const char *sqlstatement = [statement UTF8String];
        NSLog(@"%@",statement);

        if (sqlite3_prepare_v2(database, sqlstatement, -1, &compliedstatement, NULL)==SQLITE_OK) {

        if (SQLITE_DONE!=sqlite3_step(compliedstatement)) {
            NSAssert1(0, @"Error by inserting '%s'", sqlite3_errmsg(database));
            UIAlertView *AlertOK = [[UIAlertView alloc] initWithTitle:@"Error!" message:@"Error by inserting" delegate:self cancelButtonTitle:@"OK" otherButtonTitles:nil];     

            [AlertOK show];

        }
        else {
            UIAlertView *AlertOK = [[UIAlertView alloc] initWithTitle:@"Success!" message:@"Data successfully inserted" delegate:self cancelButtonTitle:@"OK" otherButtonTitles: nil];             
            [AlertOK show];
            }
        }
        sqlite3_finalize(compliedstatement);

    }
    sqlite3_close(database);

}

And the last NSLog above displays insert into table1 values ('n', 'a', 'c') as I would expect.

Something is going wrong upon insertion and I can't figure it out.

FYI the DB was created using SQLiteManager. SS below:

Answer 1

Ok, Are you working on a simulator or an iDevice? In iDevice, you cannot create an external SQL database (at least to my knowledge). You have to create it dynamically because of the sandboxing. "FYI the DB was created using SQLiteManager." Check on that.

If that was not the problem try changing your code from "prepare ..." to "execute"

From...

if (sqlite3_prepare_v2(database, sqlstatement, -1, &compliedstatement, NULL)==SQLITE_OK) {

    if (SQLITE_DONE!=sqlite3_step(compliedstatement)) {
        NSAssert1(0, @"Error by inserting '%s'", sqlite3_errmsg(database));
        UIAlertView *AlertOK = [[UIAlertView alloc] initWithTitle:@"Error!" message:@"Error by inserting" delegate:self cancelButtonTitle:@"OK" otherButtonTitles:nil];     

        [AlertOK show];

    }
    else {
        UIAlertView *AlertOK = [[UIAlertView alloc] initWithTitle:@"Success!" message:@"Data successfully inserted" delegate:self cancelButtonTitle:@"OK" otherButtonTitles: nil];             
        [AlertOK show];
        }
    }
    sqlite3_finalize(compliedstatement);

To...

    sqlite3_exec(database, [sqlstatement UTF8String], NULL, NULL, &error);

Do a log on "error" because that is very essential.

if you wanted to pop a UIAlert do this

 if (error !=nil)
    //show alert for error
 else
    //show alert for success