Unable to retrieve and view results from a SQLite database

Dear fellow iPhone and Objective-C developers:

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 statement: '%@', 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?

Any help would be greatly appreciated.

Answers


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.

Thanks again for all of your help.


Need Your Help

With ExpresJS or Node, Is there an easy way to read an external image into memory and serve it?

node.js express

I'm using an external service to create images. I'd like my users to be able to hit my API and ask for the image. Then my Express server would retrieve it from the external service, then serve it t...

One liner code challenge

c#

I am trying to write a one liner code for the following code:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.