Unable to retrieve and view results from a SQLite database

I am trying to build my skills as an iPhone developer, and presently I am working on using the SQLite database. I have created a SQLite table as follows in my GroceryListAppDelegate.m class:

- (void)applicationDidFinishLaunching:(UIApplication *)application {    

    self.database = [[[ISDatabase alloc] initWithFileName:@"TestDB.sqlite"] autorelease];

if(![[database tableNames] containsObject:@"GroceryItem"]) {

    [database executeSql:@"create table GroceryItem(primaryKey integer primary key autoincrement, name text NOT NULL, number integer NOT NULL)"];
    [database executeSql:@"insert into GroceryItem (name, number) values ('apples', 5)"];
    [database executeSql:@"insert into GroceryItem (name, number) values ('oranges', 3)"];

}

[window addSubview:navigationController.view];
[window makeKeyAndVisible];

}

I make a sql call in my RootViewController.m class:

- (void)viewDidLoad {
[super viewDidLoad];

GroceryList1AppDelegate *appDelegate = (GroceryList1AppDelegate *)[[UIApplication sharedApplication] delegate];

self.results = [appDelegate.database executeSqlWithParameters:@"SELECT * from GroceryItem where number < ?", [NSNumber numberWithInt:6], nil];

}

My executeSqlWithParameters() method looks like this:

- (NSArray *) executeSql:(NSString *)sql withParameters: (NSArray *) parameters {

NSMutableDictionary *queryInfo = [NSMutableDictionary dictionary];
[queryInfo setObject:sql forKey:@"sql"];

if (parameters == nil) {

    parameters = [NSArray array];

}

//we now add the parameters to queryInfo

[queryInfo setObject:parameters forKey:@"parameters"];

NSMutableArray *rows = [NSMutableArray array];

//log the parameters

if (logging) {

    NSLog(@"SQL: %@ \n parameters: %@", sql, parameters);

}

sqlite3_stmt *statement = nil;

if (sqlite3_prepare_v2(database, [sql UTF8String], -1, &statement, NULL) == SQLITE_OK) {

    [self bindArguments: parameters toStatement: statement queryInfo: queryInfo];

    BOOL needsToFetchColumnTypesAndNames = YES;
    NSArray *columnTypes = nil;
    NSArray *columnNames = nil;

    while (sqlite3_step(statement) == SQLITE_ROW) {

        if (needsToFetchColumnTypesAndNames) {

            columnTypes = [self columnTypesForStatement:statement];
            columnNames = [self columnNamesForStatement:statement];
            needsToFetchColumnTypesAndNames = NO;

        }

        id row = [[NSMutableDictionary alloc] init];
        [self copyValuesFromStatement:statement toRow:row queryInfo:queryInfo columnTypes:columnTypes columnNames:columnNames];
        [rows addObject:row];
        [row release];

    }       

}

else {

    sqlite3_finalize(statement);
    [self raiseSqliteException:[[NSString stringWithFormat:@"failed to execute sta开发者_如何学JAVAtement: '%@', parameters: '%@' with message: ", sql, parameters] stringByAppendingString:@"%S"]];

}

sqlite3_finalize(statement);
return rows;

}

When I build and run my code, I get no results, when in fact, given my SQL call, I should get apples, and oranges in my list. However, when I modify my sql code to this:

self.results = [appDelegate.database executeSql:@"SELECT * from GroceryItem"];

Which calls the different method: executeSql():

- (NSArray *) executeSql: (NSString *)sql {

return [self executeSql:sql withParameters: nil];

}

In this case, I end up getting the results: apples, and oranges. Why is this? What am I doing wrong? Why is it that I am getting results from one SQL call, and not from another?

If you extract the .sqlite file from the Simulator or device and open it in on the command line using sqlite3, does the query work? This will enable you to separate testing your database creation code and query code.

Thanks very much for all of your help, but I found the solution. The problem was in my bindArguments() function:

- (void) bindArguments: (NSArray *) arguments toStatement: (sqlite3_stmt *) statement queryInfo: (NSDictionary *) queryInfo {

right where I had the bind statement for numbers:

else if ([argument isKindOfClass:[NSNumber class]])
        {
            sqlite3_bind_double(statement, -1, [argument doubleValue]);
        }

I needed to change the -1 to an i. This variables tells SQL which variable I am going to susbstitute in the statement. So in this case we only have one variable represented by ? and it should be substituted by a 5.