c++ sqlite3_exec unable to close due to unfinalised statements

Question

I have the following piece of code using Sqlite3 on Ubuntu (c++):

void test_function(dbHandler)
{
    char *retError = 0;

    std::string sql("INSERT INTO LOG (LAST_CHANGED_DATE_TIME) VALUES ('TEST');");

    int returnStatus = sqlite3_exec(dbHandler, sql.c_str(), 0, 0, &retError);

    std::cout << "RetStatus = " << returnStatus << "; " << retError << s

    if (returnStatus == SQLITE_OK)
        return sqlite3_changes(dbHandler);
    else
    {
        sqlite3_free(retError);
        sqlite3_close(dbHandler);
    }
}

The sqlite3_exec statement sql is made wrong to test its failture behaviour (in that case the field name does not match). I get a correct status from sqlite3_exec as:

 Status = 1; table Log has no column named last_changed_date_time

As it is an error, I need to free the error message (retError) and close the database connection. And here is where I have a problem:

When calling sqlite3_close, I´m getting the following exception message:

unable to close due to unfinalized statements or unfinished backups

I´ve browsed around sqlite3 docs and could not find out what I´m not releasing here...

Based on that I need help to:

a) Fix the above code.

b) Understand the correct way to recover from sqlite3_exec in case of error.

Thanks for helping.

Answer 1

a) The output line that should end with std::endl;. The dbHandler argument must have a type. A void function cannot not return a value. However, with respect to using the sqlite3 api, the posted code is correct.

b) In case of an error sqlite3_exec will do the recovery. You only need to free the memory pointed to by retError, using sqlite3_free, which you're already doing.

Below is a minimal running example with the 3 errors I described fixed. It shows that it is correct (the "library routine called out of sequence" output is because you cannot call sqlite3_errmsg on a closed database handle), as it does not produce the error you describe. So if sqlite3_close fails, that is because of an error elsewhere in your program.

The error you describe can be reproduced by uncommenting the 3 commented lines. The statement that will then be created by sqlite3_prepare is not cleaned up by a call to sqlite3_finalize and therefore sqlite3_close will cause the "unable to close due to unfinalized statements or unfinished backups" error. Your error is likely caused by something similar.

#include <iostream>
#include <sqlite3.h>

void test_function(sqlite3 * dbHandler)
{
    char *retError = 0;

    std::string sql("INSERT INTO LOG (LAST_CHANGED_DATE_TIME) VALUES ('TEST');");

    int returnStatus = sqlite3_exec(dbHandler, sql.c_str(), 0, 0, &retError);

    std::cout << "RetStatus = " << returnStatus << "; " << retError << std::endl;

    if (returnStatus == SQLITE_OK)
        return; // sqlite3_changes(dbHandler);
    else
    {
        sqlite3_free(retError);
        sqlite3_close(dbHandler);
    }
}

int main() 
{
    sqlite3 * dbHandler;
    sqlite3_open("test.sqlite", &dbHandler);
    sqlite3_exec(dbHandler, "CREATE TABLE LOG (DUMMY);", 0, 0, 0);

    // sqlite3_stmt * test;
    // const char * sql = "INSERT INTO LOG (DUMMY) VALUES ('TEST');";
    // sqlite3_prepare(dbHandler, sql, -1, &test, 0);

    test_function(dbHandler);

    std::cout << "Last error: " << sqlite3_errmsg(dbHandler) << std::endl;
    return 0;
}

Answer 2

Since I'm revisiting sqlite3 since upgrading after 10 years I managed to get the above message. I did a sqlite3_open_v2 and then a sqlite3_close and got the same message. sqlite3_close_v2 avoids the message.

Small, idiot mistake, but perhaps useful to someone.