Overview
Features
Download
Documentation
Community
Add-Ons & Services

question about iterating database table rows

A general discussion forum.

question about iterating database table rows

Postby muellerto » 25 Nov 2012, 18:28

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)
muellerto
 
Posts: 23
Joined: 13 May 2009, 18:06

Re: question about iterating database table rows

Postby alex » 26 Nov 2012, 06:01

It's a bug in SQLite connector. Fix was recently merged into git repo for the next release.
alex
 
Posts: 1130
Joined: 11 Jul 2006, 16:27
Location: United_States

Re: question about iterating database table rows

Postby muellerto » 27 Nov 2012, 19:04

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.
muellerto
 
Posts: 23
Joined: 13 May 2009, 18:06

Re: question about iterating database table rows

Postby alex » 28 Nov 2012, 05:53

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.
alex
 
Posts: 1130
Joined: 11 Jul 2006, 16:27
Location: United_States


Return to General Discussion

Who is online

Users browsing this forum: No registered users and 1 guest

cron