Re: DBI SUM function

How about a small sample Perl program that reproduces this behavior? For example, I just tried the following on my local setup:

use DBI;
use strict;
use warnings;

my $dbh = DBI->connect('dbi:mysql:test','root','',{RaiseError => 1})
  or die;
my $sth = $dbh->prepare('select sum(x) from dummy');
$sth->execute();
while(my @val = $sth->fetchrow_array()) {
   print "$val[0]\n";
}
[download]

and it worked like a charm. And yes, the while loop is redundant since this query can't return more than one row but I was trying to follow your model.

Comment on Re: DBI SUM function Download Code

Replies are listed 'Best First'.
Re^2: DBI SUM function by Bruce32903 (Scribe) on Mar 25, 2006 at 18:08 UTC
Thanks for the suggestions. I have tried the suggestion from holli and the code provided above is so similar to my code that for a moment I thought it was mine. So far I still have my NULL problem. I was only able to sneak a quick look at my computer now so all I can say at the moment is an initial "Thank you". Later today I should be able to put together a small sample program that will either work (and shock me) or allow me to demonstrate exactly what is not working. So far the only differences I see between the above code and my code is my use of upper case for the SQL words (shouldn't matter) and my use of a $SQLstr variable inside the prepare statement (I don't think this matters). I'll post a test program later. Thank you, Bruce	[reply]
Re^3: DBI SUM function by Errto (Vicar) on Mar 25, 2006 at 22:32 UTC
One other thought. SUM will return NULL if the query without the SUM would return no rows or if all the rows returned contain NULL values for the column in question. Are you sure you're running the same query against the same database table in the command prompt vs Perl? Are you using the same username etc?	[reply]
Re^3: DBI SUM function by Bruce32903 (Scribe) on Mar 26, 2006 at 13:28 UTC
... Next morning ... A thousand pardons dear Monks. In my application I have batches of data that can be added from time to time. When I run my code I don't know for sure if a batch currently exists. Because of this my SQL statement (including a lot of WHERE stuff) will be expected to return a NULL from time to time. Thus I used a simple "foolproof" way of testing for a null return. ... Yes, you guessed it, my "foolproof" code was actually "foolish" code. Let's just label it an "Intro to Computing 101, week #1 error" and let it fade away. BUT, that leaves me with my current "foolproof code". my $found_something = 0; while (@val = $sth -> fetchrow_array()) { # useful database code goes here $found_something = 1; } if ($found_something == 0) { # "graceful" found nothing code goes here } This should work, but it has a bit of a "bailing wire and chewing gum" look to it. Is there a cleaner way to do it? Thanks for the help and sorry for error on my part. Bruce	[reply]