wxSQLite3 preparedStatement problem Topic is solved

In this forum you can discuss database related issues which can be wxWidgets related, but also generic in nature.
Post Reply
User avatar
evstevemd
Part Of The Furniture
Part Of The Furniture
Posts: 2408
Joined: Wed Jan 28, 2009 11:57 am
Location: United Republic of Tanzania

wxSQLite3 preparedStatement problem

Post by evstevemd »

Hi,
I don't understand why is the code below doesn't work. I have tried and tried but nothing works. Would you guys help me spot error?

Code: Select all

if(db->IsOpen()) {
		wxSQLite3Statement stmt = db->PrepareStatement("SELECT Book, Chapter FROM Encylopedia   WHERE Book=? AND Chapter=?;");
		stmt.Bind(1, book);
		stmt.Bind(2, chapter);
		wxSQLite3ResultSet rs =stmt.ExecuteQuery();
		stmt.Reset();
		
		return rs;
	}
Strange enough replacing prepared statement with code below works, which make me suspect there is something wrong with my prepared statement code, but cannot just spot it :oops:

Code: Select all

wxSQLite3ResultSet rs = db->ExecuteQuery(wxT("SELECT * FROM Encylopedia  WHERE Book=1 AND Chapter=1;"));
Chief Justice: We have trouble dear citizens!
Citizens: What it is his honor?
Chief Justice:Our president is an atheist, who will he swear to?
utelle
Moderator
Moderator
Posts: 1125
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Re: wxSQLite3 preparedStatement problem

Post by utelle »

evstevemd wrote:I don't understand why is the code below doesn't work. I have tried and tried but nothing works. Would you guys help me spot error?

Code: Select all

if(db->IsOpen()) {
		wxSQLite3Statement stmt = db->PrepareStatement("SELECT Book, Chapter FROM Encylopedia   WHERE Book=? AND Chapter=?;");
		stmt.Bind(1, book);
		stmt.Bind(2, chapter);
		wxSQLite3ResultSet rs =stmt.ExecuteQuery();
		stmt.Reset();
		
		return rs;
	}
Strange enough replacing prepared statement with code below works, which make me suspect there is something wrong with my prepared statement code, but cannot just spot it :oops:

Code: Select all

wxSQLite3ResultSet rs = db->ExecuteQuery(wxT("SELECT * FROM Encylopedia  WHERE Book=1 AND Chapter=1;"));
In the first case the wxSQLite3Statement instance stmt owns the underlying SQLite statement object and the ownership is not tranferred to the result set. Since stmt is a local variable it's destructor is called when it goes out of scope. Thus the underlying SQLite statement object is finalized and the result set returned by ExecuteQuery isn't valid any longer. In the second case the result set owns the underlying SQLite statement object, and your function works as you expect.

Currently the result set returned by a prepared statement needs to be processed within the scope of the prepared statement. Probably I should consider to provide a way to pass forward the ownership of the underlying SQLite statement object to the result set. But this would have it's drawbacks since a prepared statement is often used to process the same query over and over again with different parameters and that wouldn't work reliably anymore if the ownership of the SQLite statement object is given away to a result set.

For the time being as a workaround you would need to pass the wxSQLite3Statement instance holding the prepared statement as a parameter to your function, so that it doesn't go out of scope when the function returns the result set. Certainly not a very elegant solution but currently the only one to make your code work.

Regards,

Ulrich
User avatar
evstevemd
Part Of The Furniture
Part Of The Furniture
Posts: 2408
Joined: Wed Jan 28, 2009 11:57 am
Location: United Republic of Tanzania

Post by evstevemd »

Thanks Urlich.
I will have to go that way :oops:
I think you might think of adding a parameter (of bool type) for user whether he wants to forward or not
Chief Justice: We have trouble dear citizens!
Citizens: What it is his honor?
Chief Justice:Our president is an atheist, who will he swear to?
User avatar
evstevemd
Part Of The Furniture
Part Of The Furniture
Posts: 2408
Joined: Wed Jan 28, 2009 11:57 am
Location: United Republic of Tanzania

Re: wxSQLite3 preparedStatement problem

Post by evstevemd »

utelle wrote: For the time being as a workaround you would need to pass the wxSQLite3Statement instance holding the prepared statement as a parameter to your function, so that it doesn't go out of scope when the function returns the result set. Certainly not a very elegant solution but currently the only one to make your code work.

Regards,

Ulrich
Hi again, I have tried that but have not worked!
Here is a function, modified if I have missed something.

Code: Select all

wxSQLite3ResultSet BibleLoader::getChapter(int book, int chapter, wxSQLite3Statement stmt, wxString dbName) {
	wxSQLite3Database* db = new wxSQLite3Database();
	wxSQLite3ResultSet rs;
	wxString path = wxFileName::GetCwd()+PATH_SEP+wxT("encyclopedias")+PATH_SEP+dbName;
	db->Open(path,DB_PASS);
	if(db->IsOpen()) {
                wxSQLite3Statement stmt = db->PrepareStatement("SELECT Book, Chapter FROM Encylopedia   WHERE Book=? AND Chapter=?;");
                stmt.Bind(1, book);
                stmt.Bind(2, chapter);
                wxSQLite3ResultSet rs =stmt.ExecuteQuery();
                stmt.Reset();
               
                return rs;
        }else {
		return wxSQLite3ResultSet();
	}
}
and here is how I call it

Code: Select all

//Dummy Prepared statement object to overcome owership issue
wxSQLite3Statement stmt;
wxSQLite3ResultSet rs = bl->getChapter(lastConfigChapters.book, lastConfigChapters.chapter, stmt, wxT("Britanica.enc"));
Chief Justice: We have trouble dear citizens!
Citizens: What it is his honor?
Chief Justice:Our president is an atheist, who will he swear to?
utelle
Moderator
Moderator
Posts: 1125
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Re: wxSQLite3 preparedStatement problem

Post by utelle »

evstevemd wrote:I have tried that but have not worked!
Well, it can't since your code contains 2 errors. Please note the comments in the following code snippet:

Code: Select all

// 1. Pass stmt by reference (note the ampersand)
wxSQLite3ResultSet BibleLoader::getChapter(int book, int chapter, wxSQLite3Statement& stmt, wxString dbName) {
	wxSQLite3Database* db = new wxSQLite3Database();
	wxSQLite3ResultSet rs;
	wxString path = wxFileName::GetCwd()+PATH_SEP+wxT("encyclopedias")+PATH_SEP+dbName;
	db->Open(path,DB_PASS);
	if(db->IsOpen()) {
                // 2. Don't hide the function argument
                // Drop variable declaration:
                /*wxSQLite3Statement*/
                stmt = db->PrepareStatement("SELECT Book, Chapter FROM Encylopedia   WHERE Book=? AND Chapter=?;");
                stmt.Bind(1, book);
                stmt.Bind(2, chapter);
                wxSQLite3ResultSet rs =stmt.ExecuteQuery();
                stmt.Reset();
               
                return rs;
        }else {
		return wxSQLite3ResultSet();
	}
}
This should work as long as you process the result set during the life time of the passed stmt variable.

Regards,

Ulrich

P.S.: I'll consider to add a boolean parameter to method ExecuteQuery to let the user decide whether he wants to forward the ownership of the SQLite statement object to the result set or not.
User avatar
evstevemd
Part Of The Furniture
Part Of The Furniture
Posts: 2408
Joined: Wed Jan 28, 2009 11:57 am
Location: United Republic of Tanzania

Post by evstevemd »

Thanks for pointing out errors and for consideration.

i will mark it solved and if I hit a wall, then I will be back to Cologne ;)
Chief Justice: We have trouble dear citizens!
Citizens: What it is his honor?
Chief Justice:Our president is an atheist, who will he swear to?
utelle
Moderator
Moderator
Posts: 1125
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Post by utelle »

evstevemd wrote:Thanks for pointing out errors and for consideration.

i will mark it solved and if I hit a wall, then I will be back to Cologne ;)
You are welcome.

I added a boolean parameter to method ExecuteQuery to let the user decide whether he wants to forward the ownership of the SQLite statement object to the result set or not. Please download the files wxsqlite3.h and wxsqlite3.cpp from the wxCode SVN. Let me know if you experience any problems using this modification.

Regards,

Ulrich
User avatar
evstevemd
Part Of The Furniture
Part Of The Furniture
Posts: 2408
Joined: Wed Jan 28, 2009 11:57 am
Location: United Republic of Tanzania

Post by evstevemd »

I'm downloading
thanks!
Chief Justice: We have trouble dear citizens!
Citizens: What it is his honor?
Chief Justice:Our president is an atheist, who will he swear to?
User avatar
evstevemd
Part Of The Furniture
Part Of The Furniture
Posts: 2408
Joined: Wed Jan 28, 2009 11:57 am
Location: United Republic of Tanzania

Post by evstevemd »

utelle wrote:
evstevemd wrote:Thanks for pointing out errors and for consideration.

i will mark it solved and if I hit a wall, then I will be back to Cologne ;)
You are welcome.

I added a boolean parameter to method ExecuteQuery to let the user decide whether he wants to forward the ownership of the SQLite statement object to the result set or not. Please download the files wxsqlite3.h and wxsqlite3.cpp from the wxCode SVN. Let me know if you experience any problems using this modification.

Regards,

Ulrich
Works flawlessly
Chief Justice: We have trouble dear citizens!
Citizens: What it is his honor?
Chief Justice:Our president is an atheist, who will he swear to?
Post Reply