0

This is my first SQLite based iPhone app and I am trying to get it to read a menu hierarchy from my database.

The database appears to be registered fine as the compiled statement doesnt error (tried putting in valid table name to test) but for some reason sqlite3_step(compiledStmt) doesnt ever equal SQLITE_ROW as if to suggest there is no data in there; which there is.

sqlite3 *database;

menu = [[NSMutableArray alloc] init]; 

if (sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK) {
    const char *sqlStmt = "SELECT * FROM Menu";
    sqlite3_stmt *compiledStmt;

    if (sqlite3_prepare_v2(database, sqlStmt, -1, &compiledStmt, NULL) == SQLITE_OK) {
        while (sqlite3_step(compiledStmt) == SQLITE_ROW) {
            NSString *aTitle = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStmt, 1)];

            MenuItem *menuItem = [[MenuItem alloc] init];
            menuItem.title = aTitle;

            [menu addObject:menuItem];

            [menuItem release];
        }
    }
    else {
        NSLog(@"There is an error with the SQL Statement");
    }

    sqlite3_finalize(compiledStmt);

}

sqlite3_close(database);
Anthony Main
  • 6,039
  • 12
  • 64
  • 89
  • I do not develop iPhone apps, just debugging by what I know. Can you print out the contents of `compiledStmt` before you reach `sqlite3_step()`? I see where its located, but I cannot tell where it gets initialized. What does `sqlite3_prepare_v2` do, prepare the statement? What are its content before you invoke `sqlite3_step()`? – Anthony Forloney Jan 28 '10 at 15:46
  • Are you able to use Core Data? If so, I would recommend that over the "manual" approach you've taken: http://developer.apple.com/macosx/coredata.html – Gavin Miller Jan 28 '10 at 16:01
  • With that said, what is sqlite3_step(compiledStmt) returning if it's not returning SQLITE_ROW? – Gavin Miller Jan 28 '10 at 16:03
  • AHHH Why are you not using a SQLite wrapper?!?!! I'll bet half your problems would go away if you did. – Dave DeLong Jan 28 '10 at 16:11
  • Anthony - the contents is the string above it Gavin - this is for a prototype and so I have a large pile of data I just need to store temporarily in my app, is this still relevant as want this data stored and queryable on load - any tutorials? Gavin - it is returning SQLITE_DONE Dave - a little more help please?! – Anthony Main Jan 28 '10 at 16:27
  • @tigermain - a bunch of people have gone to the work to create an Objective-C wrapper around the SQLite C API, which means that you no longer have to worry about compiling statements, etc. You can pretty much just do: `id results = [myDB executeQuery:@"SELECT * FROM menu"];` Here's the ones I've found: http://cocoaheads.byu.edu/resources/sqlite – Dave DeLong Jan 28 '10 at 16:58
  • Thanks Dave I gave that a go with EXACTLY the same result. My simple select query is still bringing back no results. I'd appreciate it if someone could help with the problem and not just point me in the direction of Core Data, I've read some more about it and I think I'd still be using a SQLite database to persist the data I have, which still wont get my round my problem – Anthony Main Jan 28 '10 at 18:21
  • A couple of really basic things to check: check your database path to make sure you're actually opening the database you think you are. Then use the sqlite3 command line tool to open the database and issue the query and see if it returns any records there. – Dewayne Christensen Jan 28 '10 at 20:11
  • Dewayne - thanks,but as I said the database is being connected correctly as I the queries are compiling and I have tried with teh command line tool and get the results I expect, so am stumped – Anthony Main Jan 28 '10 at 20:45
  • Just because the query compiles without errors doesn't necessarily mean that you're connected to the file you think you're connected to. If you have an old copy of the database file lying around in a different directory, and somehow your databasePath variable is pointing at that file instead, you'd get the exact behavior you're seeing. I realize this is the equivalent of asking "well is it plugged in?" and I don't mean to imply that you're stupid or anything, but I know that oftentimes it's the trivial matters that trip us, because we have to overcome basic assumptions we're working with. – Dewayne Christensen Jan 28 '10 at 21:53
  • Unfortunately it isnt me being stupid (for a change) there is only a single database file, unless the application itself is maintaining an old copy of it?!?! – Anthony Main Jan 29 '10 at 00:09
  • I've got no idea about the iphone sqlite thing but could you just try to insert an element in your db from the iphone app ? and then do the select * from Menu, just to see... – LB40 Jan 29 '10 at 15:05
  • 1
    Ooh, that's an idea, change your SQL statement to "select count(*) from Menu". That should always return a single row, plus once you get that row back, you'll know how many rows Menu actually has in it. If it's zero, then your problem doesn't lie in the piece of code you've posted. – Dewayne Christensen Jan 29 '10 at 19:57
  • @Dewayne +1 I agree with that, that was my other idea – LB40 Jan 29 '10 at 21:24

2 Answers2

0

In response to your comments:

Core data can be used for simple data or complex data. The setup is minimal and it's super easy to use. In terms of tutorials, apple's own documentation is the best. I'm a new iPhone developer myself and I was able to figure it out in a couple of hours. Apple has a great sample app called iPhoneCoreDataRecipes that helped me get up and running.

The other option is to look at plists. They can be used for storing simple data as well; typically configuration data. David Janes has written up a simple tutorial.

Gavin Miller
  • 43,168
  • 21
  • 122
  • 188
  • well Im talking about a data set of about 1000 records is Core Data still the way to go? Is it queryable like SQLite? – Anthony Main Jan 28 '10 at 18:03
  • Sorry I missed that part - Core Data is built on SQLite and 1000 records is not a problem at all. – Gavin Miller Jan 28 '10 at 18:06
  • That recipe demo looks really good, seems to be doing what I need, just develed into it more and it IS using a SQLite database, still think Im going to have the same problems though – Anthony Main Jan 28 '10 at 18:26
0

Despite most suggestions to use Core Data, I have spent the afternoon researching it and it is completely inappropriate to my needs. I need to store/import over 1000 rows of data in the application on load and Core Data has NO way to do this in any easy fashion.

SQLite will still be the ideal solution, but I still dont have a solution to the problem.

EDIT:

Further research led me to this AMAZING example:

http://cocoawithlove.com/2009/11/writing-parser-using-nsscanner-csv.html

With a little jiggary pockery you can import your CSV etc into a SQLite file for embedding in your application

Anthony Main
  • 6,039
  • 12
  • 64
  • 89