The Naughty Nought "OEO" blues

[rinceWind]

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";
}

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.

[merlyn]

You could just write what you mean:

while ((my $rows = $dbh->do(<<SQL)) > 0) {

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.

[demerphq]

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";
}

[tye]

I'd write

while( my $rows= 0 + $dbh->do(...) ) {

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.

[demerphq]

Yeah, i think that notationally nicer to read as well. Good point. Yet another ++ for tye :-)

[mpeppler]

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

[thor]

Could you change your while loop to:

while ((my $rows = $dbh->do(<<SQL)) ne '0E0') {

? I realize that it kind of glosses over your question, but it should get you by.

[borisz]

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
}