No rows being returned in sqlite select statement

Question

I am having trouble selecting data from my database. Maybe its just to early here, but it looks as if I never get anything returned but I put break points in to stop inside the while statement and it never reaches that part of the code. Did I mess something up in this? I did omit the create statement because it is rather long and I dont think that it is relevant because it does contain IF NOT EXISTS. I also verified that I do have all my data in the database.

sqlite3 *database;
if(sqlite3_open([[self dataFilePath] UTF8String], &database)
   != SQLITE_OK) {
    sqlite3_close(database);
    NSAssert(0,@"Failed to open database");
}

NSString *createSQL = @"--CREATE STATEMENT HERE --";

char *errorMsg;

if(sqlite3_exec (database, [createSQL UTF8String], NULL, NULL, &errorMsg) != SQLITE_OK) {
    sqlite3_close(database);
    NSAssert(0,@"Error creating table: %s", errorMsg);
}

NSString *query = @"SELECT name, number FROM DATA";
sqlite3_stmt *statement;
if (sqlite3_prepare_v2(database, [query UTF8String], -1, &statement, nil) == SQLITE_OK) {
    while (sqlite3_step(statement) == SQLITE_ROW) {
        int myNumber = sqlite3_column_int(statement, 1);
        char *name = (char *)sqlite3_column_text(statement, 0);
        NSString *message = [NSString stringWithFormat:@"Number: %d, Name: %@", myNumber,name];
        //DO MORE STUFF HERE.
    }
    sqlite3_finalize(statement);
}
sqlite3_close(database);

dateFilePath Method:

-(NSString*)dataFilePath {
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    return [documentsDirectory stringByAppendingPathComponent:@"Spaces.sqlite"];
}

Have you executed your query in external `sqlite manager` and the results from there?? — Sumanth, Jul 30 '12 at 13:39
Yep, returns all the rows I need. Thats why I am stuck, I dont get why its not running the code, everything looks ok to me. As I said in my other comment I thought that maybe i made a mistake in the SQL statement so I made it very simple get to columns, and that still doesnt work. — atrljoe, Jul 30 '12 at 13:40

score 0 · Answer 1 · answered Jul 30 '12 at 13:19

0

you're selecting name and number.. but from what? you need to finish you're sql query statement..

answered Jul 30 '12 at 13:19

DJPlayer

3,244
2
33
40

Sorry I took off most of the statement because it was very long also. But I have tried using my long statement, and using the correct short one and I still get nothing. – atrljoe Jul 30 '12 at 13:22

score 0 · Accepted Answer · edited May 23 '17 at 12:29

You say that you checked to see that the data was there, did you look at the copy in your simulator's Documents folder, or did you check the copy in your Xcode project? (You really need to check the contents of the database the app's Documents folder on the simulator, if you can.) The reason I ask is that a very common mistake is to assume that a successful call to sqlite3_open means that it found your database. It doesn't. If it doesn't find your database, it will create a blank one for you.

If you have a database prepared prior to compilation, I'd really encourage you to use sqlite3_open_v2 instead of sqlite_open as discussed here: SQLite will not prepare query when accessing database in Xcode

So, if this is the problem, I'd suggest:

Reset your simulator (via "Reset Content and Settings" on the Simulator menu, or just delete the app and reinstall) to get rid of any blank databases there.
Make sure the database is included in your bundle as described in the above link.
Make sure to programmatically copy the database from bundle to documents as described here: Unable to connect SQLite Database in iOS
And use sqlite3_open_v2 as described in either of the two above links.

No rows being returned in sqlite select statement

2 Answers2