The Naughty Nought "OEO" blues

I've been bitten by a gotcha on return values from DBI execute (and Super search tells me that many others have been bitten by it in the past also :).

         For a non-"SELECT" statement, "execute" returns the
         number of rows affected, if known. If no rows were
         affected, then "execute" returns ""0E0"", which Perl
         will treat as 0 but will regard as true. Note that it is
         not an error for no rows to be affected by a statement.
         If the number of rows affected is not known, then
         "execute" returns -1.

Consider the following code:

while (my $rows = $dbh->do(<<SQL)) {
update #LAG
set tmpDate = dateadd( day, case
                when datepart( weekday, cl.tmpDate) = 2 then -3 else -
+1 end, cl.tmpDate)
from #LAG cl,
        $db..HOLIDAY h
where cl.tmpDate = h.Date
and cl.country = h.country
SQL
        print "$rows holidays adjusted\n";
}
[download]

This is Sybase, and I want to run the query repeatedly adjusting tmpDate in temporary table #LAG until none of them fall on holidays for the particular country. But, this code loops forever, as when there are no more rows, do returns "0E0".

I know that this subject has been covered before on PM, see Should string inequality operators return the point the of divergance? and What is truth? (Curiosity corner).

I also recall that the return value of the flip-flop operator .. in scalar context has "E0" appended to signal a terminating match. On some levels, I think that this is very clever, as this is interpreted by perl's string to number parser as "X 10 to the power 0". Hence, using this scalar value in numeric context gives you the line count.

I have several questions in my mind:

Is this technique useful or just plain obscure?
Could it be useful to have a truthful zero as return values elsewhere? Also, what's wrong with "00"?
When does DBI return a "false" 0? Particularly when RaiseError is set to 1. Surely, DBI would then throw an exception if the query failed. Is there any advantage in false 0 over catching an exception with eval?

Update: I realise with hindsight that I hadn't made it clear that I had solved the original problem. This is the reason it was posted as a meditation, not SOPW.

--
I'm Not Just Another Perl Hacker

Comment on The Naughty Nought "OEO" blues Download Code

Replies are listed 'Best First'.
Re: The Naughty Nought "OEO" blues by merlyn (Sage) on Jan 20, 2005 at 17:35 UTC
You could just write what you mean: `while ((my $rows = $dbh->do(<<SQL)) > 0) {` [download] As in, "while the number of rows is greater than 0". All those hacky "+ 0" solutions look wierd to me. {grin} Note that this will quit whether there's 0 rows, or an error. Probably a good plan either way. -- Randal L. Schwartz, Perl hacker Be sure to read my standard disclaimer if this is a reply.	[reply] [d/l]
Re: The Naughty Nought "OEO" blues by demerphq (Chancellor) on Jan 20, 2005 at 17:02 UTC
You have to coerce numeric context. `while (0+(my $rows = $dbh->do(<<SQL))) { update #LAG set tmpDate = dateadd( day, case when datepart( weekday, cl.tmpDate) = 2 then -3 else - +1 end, cl.tmpDate) from #LAG cl, $db..HOLIDAY h where cl.tmpDate = h.Date and cl.country = h.country SQL print "$rows holidays adjusted\n"; }` [download] --- demerphq	[reply] [d/l]
Re^2: The Naughty Nought "OEO" blues (simpler) by tye (Sage) on Jan 20, 2005 at 17:27 UTC
I'd write `while( my $rows= 0 + $dbh->do(...) ) {` [download] in this case. I'd prefer "00" but it looks less like a real number to some people than "0E0". Therefore, "0.0" might be an even better choice. I like the feature. It makes it easy to pick whether you want to care about number of rows or about success (simply add "`0+`" or "`0==`" or don't). When RaiseError is turned on, it isn't useful. So I could see adding an option for PlainZero which would default to "never" (for compatability) but could be changed to "always" or "only when RaiseError is off". Though I probably wouldn't use such a feature since I see too much risk from action-at-a-distance with it. - tye	[reply] [d/l]
Re^3: The Naughty Nought "OEO" blues (simpler) by demerphq (Chancellor) on Jan 20, 2005 at 17:29 UTC
Yeah, i think that notationally nicer to read as well. Good point. Yet another ++ for tye :-) --- demerphq	[reply]
Re: The Naughty Nought "OEO" blues by mpeppler (Vicar) on Jan 20, 2005 at 18:46 UTC
Not everyone uses the raiserror/eval functionality to catch errors - so the false return from do() and execute() make sense and are needed. That said I agree that the "0 but true" return value is misleading/confusing - it's in general not a good idea to have a value returned that has both a status (success/failure) and a non-status (number of rows) signification. A better way might be to have all methods return a success/failure code, and have the rows affected be returned in a different way (i.e. with $sth->rows(), or in an attribute, or...). It's obviously too late to change that for DBI 1.x, but you might want to lobby Tim for DBI 2.x. Michael	[reply]
Re: The Naughty Nought "OEO" blues by thor (Priest) on Jan 20, 2005 at 16:53 UTC
Could you change your while loop to: `while ((my $rows = $dbh->do(<<SQL)) ne '0E0') {` [download] ? I realize that it kind of glosses over your question, but it should get you by. thor `Feel the white light, the light within Be your own disciple, fan the sparks of will For all of us waiting, your kingdom will come`	[reply] [d/l]
Re: The Naughty Nought "OEO" blues by borisz (Canon) on Jan 20, 2005 at 17:41 UTC
DBI returns 0e0 as zero but true instead of a zero. Zero is not a error, like undef. this mean you can write `my $rv = $sth->execute or die "error"; # write $rv > 0 instead of $rv if ( $rv > 0 ) { # do somethiing }` [download] Boris	[reply] [d/l]


There's more than one way to do things
	PerlMonks