Exception on prepared statement reuse in wxSQLite3

Question

I'm writing an application that uses the wxSQLite3 library, which is a wrapper around libsqlite3 for the wxWidgets cross-platform GUI programming framework. When attempting to reuse a prepared statement, a wxSQLite3Exception is thrown.

This example illustrates the problem:

#include <wx/string.h>
#include <wx/wxsqlite3.h>

int main() {
    wxSQLite3Database::InitializeSQLite();

    //create in-memory test database & populate it
    wxSQLite3Database db;
    db.Open(wxT(":memory:"));
    db.ExecuteUpdate(wxT("CREATE TABLE SimpleTable (id INT PRIMARY KEY, val INT);"));
    db.ExecuteUpdate(wxT("INSERT INTO SimpleTable VALUES (1, 10);"));
    db.ExecuteUpdate(wxT("INSERT INTO SimpleTable VALUES (2, 20);"));

    //create a prepared statement we can reuse
    wxSQLite3Statement stmt;
    stmt = db.PrepareStatement(wxT("SELECT * FROM SimpleTable WHERE id = ?;"));

    //first use of statement (works)
    stmt.Bind(1, 1);
    wxSQLite3ResultSet r_set = stmt.ExecuteQuery();
    if (r_set.NextRow()) {
        wxPrintf(wxT("id: %i   value: %i\n"), r_set.GetInt(wxT("id")), r_set.GetInt(wxT("val")));
    }
    r_set.Finalize();

    //reset and reuse statement
    stmt.Reset();
    stmt.Bind(1, 2); //**EXCEPTION THROWN HERE**
    wxSQLite3ResultSet r_set2 = stmt.ExecuteQuery();
    if (r_set2.NextRow()) {
        wxPrintf(wxT("id: %i   value: %i\n"), r_set2.GetInt(wxT("id")), r_set2.GetInt(wxT("val")));
    }
    r_set2.Finalize();

    //cleanup
    stmt.Finalize();
    db.Close();
    wxSQLite3Database::ShutdownSQLite();
    return 0;
}

The exception handling was removed for brevity, but the message from the exception is:

WXSQLITE_ERROR[1000]: Statement not accessible

I wrote roughly equivalent code in plain C using libsqlite3 and it ran without problem. Does anyone know what I'm doing wrong, or if this is a bug of some sort in wxSQLite3? Thank you in advance for your help!

Does removing the `Finalize` calls on the result sets help? (These calls should not have any effect unless you transferred the ownership of the statement to the result set object, which you did not do.) — CL., Dec 16 '14 at 10:36
It does help! Thank you! I don't understand why, though. Don't you need to `Finalize` the result set to prevent memory leaks? — Chris, Dec 16 '14 at 18:31
Does `ExecuteQuery` have a boolean parameter? If not, then you have an older (and apparently buggier) version of wxWidgets than the one I'm looking at. — CL., Dec 16 '14 at 20:04
It does not. However, I'm using wxSQLite3 3.1.1, which is the current release version. I experience this problem with wxWidgets 2.8.12 (previous stable release) and wxWidgets 3.0.2 (current release), and on both Windows (VC++) and Linux (gcc). — Chris, Dec 17 '14 at 21:27
Reference counting was introduced to wxSQLite3 in version 3.0.0, almost 3 years ago. It makes using wxSQLite3 a lot easier, because wxSQLite3 keeps track of the use of the underlying SQLite data structures. It's seldom required to explicitly call method Finalize, but if you do so, a consequence is that the associated prepared statement can't be accessed anymore. — Ulrich Telle, Dec 20 '14 at 23:33

CL. · Accepted Answer · 2014-12-21T07:04:43.457

1

In SQLite itself, a statement and a result set actually are the same object.

wxSQLite3 uses reference counting so that the statement is freed only when the last wxSQLite3Statement or wxSQLite3ResultSet object is freed. This happens automatically in the respective destructors.

However, calling Finalize() explicitly bypasses the reference counting.

While not necessary, if you want to ensure that wxSQLite3ResultSet resources are freed correctly before the next statement execution, just destruct this object:

wxSQLite3Statement stmt = ...;
...
{
    wxSQLite3ResultSet r_set = stmt.ExecuteQuery();
    ... r_set.NextRow() ...
    // r_set destructed here
}
...

edited Dec 21 '14 at 07:04

answered Dec 18 '14 at 07:51

CL.

173,858
17
217
259

1

It is not necessary to force the destruction of the result set object in that way. wxSQLite3's reference counting takes care of cleaning up resources even if you reassign a new value to an existing result set object. The mistake is to call Finalize(), because it destroys the underlying prepared statement. Thereafter the prepared statement is no longer valid, and trying to access it will therefore throw an exception. – Ulrich Telle Dec 20 '14 at 23:20

score 1 · Answer 2 · answered Dec 20 '14 at 23:16

As long as you intend to reuse a prepared SQL statement, that is, to reset the statement and to bind new values to statement variables, you must not call method Finalize - neither on the prepared statement object itself nor on a result set retrieved from that statement.

As the method name, Finalize, suggests, the method finalizes the underlying SQLite statement object by calling sqlite3_finalize (quotation from the SQLite docs: "The sqlite3_finalize() function is called to delete a prepared statement.") After the underlying SQLite statement object has been deleted, it obviously can't be accessed anymore. Therefore you get the exception.

Usually you don't need to call method Finalize explicitly. wxSQLite3 takes care of finalizing statements through reference counting.

Exception on prepared statement reuse in wxSQLite3

2 Answers2