Database query ends up in an infinite loop

[ramy1989]

I have a sqlite3 database with a table "student":

create table student (name varchar(20) not null,
surname varchar(20) not null, id integer primary key);

If I try the query from terminal I get:
"Henrique Gonzales 1981021"

I am trying to do this with the sqlite3 framework available in xcode for iOS.This code runs on a background thread:

- (void) query : (id) sender
{
    NSMutableArray* rows=[NSMutableArray new];
    NSBundle* bundle=[NSBundle mainBundle];
    NSString* path=[bundle pathForResource: @"test" ofType: @"db"];
    sqlite3* database;
    sqlite3_stmt* statement;
    NSAssert(path, @"Invalid path for resource");
    NSAssert( sqlite3_open([path UTF8String], &database)==SQLITE_OK, @"Failed to open the database");
    NSAssert(sqlite3_prepare_v2(database, "select * from student", -1, &statement, NULL) == SQLITE_OK, @"Failed to prepare");
    while(sqlite3_step(statement))
    {
        NSMutableArray* row= [NSMutableArray arrayWithCapacity: 3];
        for (int i=0; i<sqlite3_column_count(statement); i++)
        {
            if(i==2)
            {
                const int studentId=sqlite3_column_int(statement, i);
                [row addObject: @(studentId)];
            }
            else
            {
                const unsigned char * const column = sqlite3_column_text(statement, i);
                [row addObject: [NSString stringWithFormat: @"%s",column]];
            }
        }
        [rows addObject: row];
    }
    NSAssert(sqlite3_close(database)==SQLITE_OK, @"Database not closed correctly");
    for(NSArray* row in rows)
    {
        NSLog(@"Name: %@ Surname: %@ Id: %@ ",row[0],row[1],row[2]);
    }
}

None of the assertions fails, but this ends up in an infinite loop (in the first loop).If I try printing the result the first time they're correct, but it seems like it doesn't exit from the loop.Other values are invalid values like an id of zero and all null strings.

[ramy1989]

Update: I wasn't checking that the result of sqlite3_step was SQLITE_ROW. But There is still a problem:

NSAssert(sqlite3_close(database)==SQLITE_OK, @"Database not closed correctly");

This assertion fails.
If I remove this line all goes fine and the query results are right.
But I don't understand why I don't have to close the database.

[chown33]

Originally Posted by ramy1989

NSAssert(sqlite3_close(database)==SQLITE_OK, @"Database not closed correctly");

This assertion fails.
If I remove this line all goes fine and the query results are right.
But I don't understand why I don't have to close the database.

Obvious question: What value does sqlite3_close() return? The answer should at least offer a clue.

From the reference doc:

Applications should finalize all prepared statements, close all BLOB handles, and finish all sqlite3_backup objects associated with the sqlite3 object prior to attempting to close the object. ...

You may also want to read about sqlite3_close_v2().

-----

I advise against using assertions for basic error-checking. I've seen it done before, by several different people over the years. It never turns out well, primarily for reasons like you have here: no access to the result-code.

You can make error-checking macros or methods that give such access, but that implies you're taking the time to design an error-handling strategy in the first place, rather than repurposing assertions for that task. A well-designed error-handling strategy is invariably better than repurposed assertions, even when the error-handling strategy uses assertions in its implementation.

Another reason for avoiding assertions as primary error-handlers is they can make the debugger harder to use. If you'd been using the debugger, and stepping through, I presume you would have seen the unexpected looping sooner, and even seen the returned value that caused the looping.

[ramy1989]

Thank you for the answer.

So I am not using sqlite3_finalize() before closing the database and my code is working fine.
But now I'm facing another problem:
I need to make a query to delete all the entries of the database and write them from scratch.I've read about resetting/finalizing the statements.
So I do the following:

1)Use sqlite3_open() to open the database;
2)Use sqlite3_prepare() to make a query that deletes all the entries;
3)In a loop, use sqlite3_reset() on the same statement used before, then sqlite3_prepare() to make another query that will insert another element;
4)Outside the loop, I use sqlite3_finalize() on the statement;
5)Then I call sqlite3_close() to close the database.

But sqlite3_close() sometimes returns SQLITE_OK, and sometimes not.Also, it doesn't make any update to the database, it doesn't delete and neither insert new values.Am I closing the database properly?

As for the assertions, I realize that I have low control.So what do you suggest. using a switch-case or functions like sqlite3_errmsg()?