iphone app SQLite Database populate failure

Question

I just started learning SQLite interfacing with the iPhone app and I am stuck not with an error, but I am unable to insert values into a DB using the following code.

I couldnt figure out where the error creeped in here, To see an insert I have NSlogged a string to show up in debugger but the code isnt reaching till there, could you kindly help me out with this one.

I tried looking at other posts but I couldnt get much out of them, I see that my syntax and statements are correct and after seeing a particular post on SO, I realized I did a mistake at somepart and then corrected it.

But still its not working. I am working on a university owned system and I am not able to access the mac disk files whether to see my db is getting populated or even created..is there something with setting up the privileges for creating db. (i really suspect that), if so then how can i create a db elsewhere in the file system for access or is there anything wrong in the below code, if you need any other files kindly do comment so that I can put them up here...

vc.m

#import "ViewController.h"

@interface ViewController ()
{
NSMutableArray *arrayOfPerson;
sqlite3 *personDB;
NSString *dbPathString;

}

@end

@implementation ViewController

- (void)viewDidLoad
{
[super viewDidLoad];
arrayOfPerson = [[NSMutableArray alloc]init];
[[self myTableView]setDelegate:self];
[[self myTableView]setDataSource:self];
[self createOrOpenDB];
}

-(void)createOrOpenDB{

NSArray *path=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *docPath=[path objectAtIndex:0];

dbPathString = [docPath stringByAppendingPathComponent:@"persons.db"];

char *error;
NSFileManager *fileManager = [NSFileManager defaultManager];
if (![fileManager fileExistsAtPath:dbPathString]) {

    const char *dbPath=[dbPathString UTF8String];

    //creating DB
    if (sqlite3_open(dbPath, &personDB)==SQLITE_OK) {
        const char *sql_stmt="CREATE TABLE IF NOT EXISTS PERSONS (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT,AGE INTEGER";
        sqlite3_exec(personDB, sql_stmt, NULL, NULL, &error);
        NSLog(@"Created !");
        sqlite3_close(personDB);
    }

}


- (void)didReceiveMemoryWarning
{
[super didReceiveMemoryWarning];

}

- (IBAction)addPersonButton:(id)sender {
char *error;
if (sqlite3_open([dbPathString UTF8String], &personDB)==SQLITE_OK) {
    NSString *insertStmt = [NSString stringWithFormat:@"INSERT INTO PERSONS(NAME,AGE) VALUES         ('%s','%d')",[self.nameField.text UTF8String],[self.ageField.text intValue]];
    const char *insert_stmt = [insertStmt UTF8String];

    if (sqlite3_exec(personDB, insert_stmt, NULL
                     , NULL, &error)==SQLITE_OK) {
        NSLog(@"Person Added");

        Person *person = [[Person alloc]init];

        [person setName:self.nameField.text];
        [person setAge:[self.ageField.text intValue]];

        [arrayOfPerson addObject:person];


    }

    sqlite3_close(personDB);
}

}

- (IBAction)displayPersonButton:(id)sender {
sqlite3_stmt *statement;
if(sqlite3_open([dbPathString UTF8String], &personDB)==SQLITE_OK){
    [arrayOfPerson removeAllObjects];
    NSString *querySql=[NSString stringWithFormat:@"SELECT *FROM PERSONS"];
    const char *query_sql=[querySql UTF8String];
    if(sqlite3_prepare(personDB, query_sql, -1, &statement, NULL)==SQLITE_OK){
        while(sqlite3_step(statement)==SQLITE_ROW){
            NSString *name=[[NSString alloc]initWithUTF8String:(const char *)sqlite3_column_text(statement,1)];
            NSString *ageString=[[NSString alloc]initWithUTF8String:(const char*)sqlite3_column_text(statement, 2)];//used sqlite3_column_int here before ! Mistake
            Person *person = [[Person alloc] init];

            [person setName:name];

            [person setAge:[ageString intValue]];
            [arrayOfPerson addObject:person];

        }
    }
}
[[self myTableView] reloadData];
}

-(void)touchesBegan:(NSSet *)touches withEvent:(UIEvent *)event{
[super touchesBegan:touches withEvent:event];
[[self ageField]resignFirstResponder ];
[[self nameField]resignFirstResponder];}
- (IBAction)deletePersonButton:(id)sender {
}
@end

vc.h

#import <UIKit/UIKit.h>
#import "sqlite3.h"
#import "Person.h"

@interface ViewController : UIViewController<UITableViewDataSource,UITableViewDelegate>
@property (weak, nonatomic) IBOutlet UITextField *nameField;
@property (weak, nonatomic) IBOutlet UITextField *ageField;
@property (weak, nonatomic) IBOutlet UITableView *myTableView;
- (IBAction)addPersonButton:(id)sender;
- (IBAction)displayPersonButton:(id)sender;
- (IBAction)deletePersonButton:(id)sender;
@end

Did any error creep in between, I checked it to my best, but couldnt find any syntactically

Answer 1

You are missing a closing bracket on your CREATE TABLE.

const char *sql_stmt="CREATE TABLE IF NOT EXISTS PERSONS (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT,AGE INTEGER";

You also don't need to wrap your integer parameter in single quotes either on this statement:

NSString *insertStmt = [NSString stringWithFormat:@"INSERT INTO PERSONS(NAME,AGE) VALUES         ('%s','%d')",[self.nameField.text UTF8String],[self.ageField.text intValue]];

Try adding this code after the body of your insertion attempt to see if sqlite will give more information as to the possible error.

if (sqlite3_exec(personDB, insert_stmt, NULL
                 , NULL, &error)==SQLITE_OK) {
    NSLog(@"Person Added");

    Person *person = [[Person alloc]init];

    [person setName:self.nameField.text];
    [person setAge:[self.ageField.text intValue]];

    [arrayOfPerson addObject:person];


}
else     // Add this in the hope it will shed more light on the problem.
{
    NSLog(@"Error: %s", sqlite3_errmsg(personDB));
}

Just noticed too that you have a couple of issues in your - (IBAction)displayPersonButton: method.

First, you have no space between *FROM. And ideally you should name the columns you want to select rather than using *.

Second, you have no sqlite3_finalize(statement).

Third, you open the database on entry to the method but don't close it again before exit.