When to do finalize statement in sqlite in iOS?

Question

When using sqlite in iOS, when to do finalize statement ?

Do I need to finalize the statement after every query ?

What is the difference between reset and finalize ?

If I do reset, do I need to do finalize then ?

Thanks.

Answer 1

You use the sqlite3_finalize() function when you are completely finished with a statement, either because you did a one-off query like the following:

const char *sql = "SELECT COUNT(*) FROM bonds WHERE molecule=? AND structure=?";
sqlite3_stmt *bondCountingStatement;

unsigned int totalBondCount = 0;

if (sqlite3_prepare_v2(database, sql, -1, &bondCountingStatement, NULL) == SQLITE_OK) 
{
    sqlite3_bind_int(bondCountingStatement, 1, databaseKey);
    sqlite3_bind_int(bondCountingStatement, 2, numberOfStructureBeingDisplayed);

    if (sqlite3_step(bondCountingStatement) == SQLITE_ROW)
    {
        totalBondCount =  sqlite3_column_int(bondCountingStatement, 0);
    }
    else
    {
    }
}
sqlite3_finalize(bondCountingStatement);

or if you are done with a prepared statement that you'll never need again (possibly because you're cleaning up on exiting the application).

If you've created a statement that you'll want to use over and over again (for performance reasons, because it is reasonably expensive to prepare a statement), you use sqlite3_reset() to reset the query so that it is ready to use again. You don't want to finalize in that case until you have decided that you'll never want to reuse that statement (again, usually this is during the teardown of your application or a particular object).

An example of a query that only resets the statement at the end is as follows:

sqlite3_bind_text(insertMoleculeSQLStatement, 1, [filename UTF8String], -1, SQLITE_TRANSIENT);
int success = sqlite3_step(insertMoleculeSQLStatement);

// Because we want to reuse the statement, we reset it instead of finalizing it.
sqlite3_reset(insertMoleculeSQLStatement);