While trying to run a statistical calculation Perl script that I have
been running for several years it started crashing. I spent some time to
track down the problem to my Perl script selecting a fairly small value
from a table and getting NULL in response. I wrote a simplified script
that demonstrated the problem so I could experiment to see what was
going on. Below is that script:
use DBI;
use English;
use strict;
no strict 'subs';
my ($dbh, $sth, $foobar);
$dbh = DBI->connect('DBI:ODBC:<database_name>', '', '', {RaiseError => 1,
AutoCommit => 1});
$sth = $dbh->prepare("select 2.775557561562E-17");
$sth->execute();
$foobar = $sth->fetchrow;
print "foobar=$foobar\n";
$sth = $dbh->prepare("select 2.7755575615629E-17");
$sth->execute();
$foobar = $sth->fetchrow;
print "foobar=$foobar\n";
exit(0);
When I run this program the output is as follows:
foobar=2.775557561562E-17
foobar=
It seems that when the mantissa of a number has too many significant
digits DBI returns a NULL instead of the number. The only difference in
the second case is the addition of an additional digit. This took some
time to boil my problem down to this simple issue. Searching on the
Internet has yet to yield any similar issues (with or without
solutions). As a result I am appealing to this list to either point out
something incredibly bone-headed that I am doing or to try to reproduce
this in their environment and propose a solution.
Right now I am looking at dropping in a bunch of ROUND() functions in my
SQL that is generating the number giving me fits but that seems to be
more of a kludge workaround than a proper fix. Any thoughts from the
deities on this list?
Before anybody asks, I'm running Perl v5.10.1 (with all the most recent
module versions loaded through ppm) on a Windows 7 machine.
Thanks in advance for anybody spending time on this issue that is
keeping me from doing useful work.
KM