WxSqlite3 - Same action two different results Topic is solved

Talk here about issues with one of the components hosted at wxCode, or suggest features for it.
Post Reply
tomicolon
In need of some credit
In need of some credit
Posts: 8
Joined: Thu Feb 22, 2018 7:40 pm

WxSqlite3 - Same action two different results

Post by tomicolon »

The problem is this, I have a single row loaded in the table. One method returns the data correctly, but the other method returns a wrong row and a good one

For simplicity I made this script, I count the times it enters the loop and I show them on the screen, as I have only one row the correct result is 1, but in the second way it returns a 2
That is, the first time you enter the loop is junk data, and the second time you enter is the correct data

Code: Select all

#include "Probando.h"
#include <wx/string.h>
#include "include/wx/wxsqlite3.h"
#include <iostream>
#include <wx/filefn.h>
using namespace std;
void Probando::crear_tabla ( ) {
	wxString testDBName = wxGetCwd() + wxT("/test4.db");
	wxSQLite3Database* db = new wxSQLite3Database();
	db->Open(testDBName);
//	db->ExecuteUpdate(wxT("CREATE TABLE test (col1 INTEGER)"));
//	db->ExecuteUpdate(wxT("INSERT INTO test (col1) VALUES (2)"));

       // IT IS OK!!!
       
	wxSQLite3ResultSet set = db->ExecuteQuery(wxT("SELECT * FROM test"));
	int count = 0;
	while (set.NextRow())
	{
		count++;
	}
	cout<<"Count one:"<<count<<endl;
	
	//IT IS WRONG!!!
	
	wxSQLite3Statement stmt2 = db->PrepareStatement("select * FROM test");
	set = stmt2.ExecuteQuery();
	stmt2.Reset();
	count = 0;
	cout<<"Reset: count now = "<<count<<endl;
	while (set.NextRow())
	{
		count++;
	}
	cout<<"Count two:"<<count;
	set.Finalize();
					  
}

Why can it happen? Am I using functions incorrectly?
Sorry for my English
Attachments
Error wxSqlite3.png
utelle
Moderator
Moderator
Posts: 1125
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Re: WxSqlite3 - Same action two different results

Post by utelle »

tomicolon wrote:The problem is this, I have a single row loaded in the table. One method returns the data correctly, but the other method returns a wrong row and a good one

For simplicity I made this script, I count the times it enters the loop and I show them on the screen, as I have only one row the correct result is 1, but in the second way it returns a 2

[...]

That is, the first time you enter the loop is junk data, and the second time you enter is the correct data

Why can it happen? Am I using functions incorrectly?
The answer to your last question is yes unfortunately.

Your first method works - obviously - correctly. Your second method does not - because you are tampering with the prepared statement.

The first steps are correct - although a prepared statement doesn't make much sense, if the statement has no bind parameters:

Code: Select all

	wxSQLite3Statement stmt2 = db->PrepareStatement("select * FROM test");
	set = stmt2.ExecuteQuery();
However, then you execute method Reset on the prepared statement:

Code: Select all

	stmt2.Reset();
This effectively invalidates the result set set. The problem is that the result set does not know that you did reset the underlying SQL statement. You could call that a deficiency of wxSQLite3, but please keep in mind that wxSQLite3 is a thin wrapper around SQLite. After having executed a reset, you must re-execute the query to get valid results.

The reason that you get a count of 2 lies in the way how SQLite and wxSQLite3 work internally. To find out whether a result set is empty or not, method ExecuteQuery already tries to read the first row of the result set. The result set "knows" that and does not fetch the next row on executing method NextRow when called for the first time (therefore it gets called 2 times in your case, before it detects that no more rows are available).

In the API documentation for method Reset it reads
Resets the statement back to it's initial state, ready to be re-executed. Any SQL statement variables that had values bound to them retain their values.
If you would re-execute the query after the reset, you would get the expected result again:

Code: Select all

	stmt2.Reset();
	// Now re-execute the query
	set = stmt2.ExecuteQuery();
Method Reset is meant to allow to re-execute an SQL statement with different bind parameter values. This is much more efficient than re-preparing the statement each time for a different set of bind parameters. This is especially true, if maybe only one bind parameter changes while all others keep their values.

Regards,

Ulrich
tomicolon
In need of some credit
In need of some credit
Posts: 8
Joined: Thu Feb 22, 2018 7:40 pm

Re: WxSqlite3 - Same action two different results

Post by tomicolon »

Thank you for the quick return, now it works correctly !

Greetings from Argentina!
Post Reply