Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical

The Naughty Nought "OEO" blues

by rinceWind (Monsignor)
on Jan 20, 2005 at 15:40 UTC ( #423714=perlmeditation: print w/replies, xml ) Need Help??

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 = 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.

I'm Not Just Another Perl Hacker

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) {
    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.

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 = SQL print "$rows holidays adjusted\n"; }

      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.

      - tye        

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


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.


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') {
    ? I realize that it kind of glosses over your question, but it should get you by.


    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

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 }

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlmeditation [id://423714]
Approved by Mutant
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (6)
As of 2018-04-19 20:40 GMT
Find Nodes?
    Voting Booth?