question about iterating database table rows

[muellerto]

I use the Poco Statement class with simple data binding to get values from an SQL table. My algorithm is like this:

Code: Select all

string a, b, c;
Statement stmt = (session << "SELECT a,b,c FROM MyTable WHERE a='value';"
   , into(a), into(b), into(c)
   , limit(1));
while (!stmt.done() && stmt.execute() == 1)
{
   // use a, b and c here
}


while loops are quite common in the samples. I took the execute call also into the loop condition to detect if execute indeed returned a valid row. As specified the result should be 1 in this case (because limit is 1), and I truely expect that the result is 0 if execute can't fetch valid data. But indeed the result is always 1. This has a very nasty consequence: I get also "something" back if there is no matching row in the table at all. Which values are then stored in the bound variables? Or is this just a bug?

What is the right way to check if returned data are valid or not?

Previously I used the RecordSet class which worked very well in this aspect. The statement class behaves unclear.

(Poco 1.5.0,SQLite, Linux x86_64)

[alex]

It's a bug in SQLite connector. Fix was recently merged into git repo for the next release.

[muellerto]

These changes do not solve the problem. I still get into the body of my while loop regardless of the fact that I have no valid data in my SQL table.

BTW: I'm not sure if the concept of the methods done() and execute() is proper. Look, if two separate methods are a design goal then done() should return also a correct value without execute() has been run. Is this the case? The while loop's condition should be based on the result of done() only:

Code: Select all

while (!stmt.done())
{
   int n = stmt.execute();
   for (int i = 0; i < n; i++)
   {
      // do something with the bound variables
   }
}


Or a bit different using a do-while-loop:

Code: Select all

do {
   int n = stmt.execute();
   for (int i = 0; i < n; i++)
   {
      // do something with the bound variables
   }
} while (!stmt.done());


Both loops open a gap between done() and execute() - in a multiuser database other users can make changes (and surely they do). So execute() can see other data than done() has seen, except the table is locked by some clever mechanism. Is this the case? I can again only trust the result of execute(). This leads to a single method solution, I would indeed prefer: execute() alone should return a relevant information about the validity of the bound variables and a second method like done() should not be needed at all:

Code: Select all

while (int n = stmt.execute())
{
   for (int i = 0; i < n; i++)
   {
      // do something with the bound variables
   }
}


In example this second case is implemented very elegant with the getline() function reading from an input stream:

Code: Select all

string myLine;
while (getline(myStream, myLine).good())
{
   // do something with the line
}


Disclaimer: I speak about the synchronous case. Perhaps in asynchronous calls done() and execute() handle a different approach I don't know at the moment.

[alex]

done() returns true when the statement is completely done (i.e. no more rows left to fetch). execute() returns number of rows fetched (for select) or affected (for update/insert). Database transaction isolation concern is addressed by Transaction class. If the code does not work as documented/expected, please file a bug and we'll look into it.