On 29/09/10 22:22, Ken Marks wrote:
> 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
>
>
With Perl 5.10.1 built on Linux and
DBD::ODBC (subversion trunk)
DBI 1.615
Easysoft SQL Server ODBC driver
I get:
foobar=2.77556E-17
foobar=2.77556E-17
On windows with Perl 5.8.9 I get:
sql server odbc driver:
C:\perlbuild_xxx\DBD-ODBC-1.26_1>perl sigdigits.pl
foobar=2.7755575615619999E-17
foobar=2.7755575615629E-17
sql server native client driver:
C:\perlbuild_xxx\DBD-ODBC-1.26_1>perl sigdigits.pl
foobar=2.7755575615619999E-17
foobar=2.7755575615629E-17
On Windows I can add as many digits as I like and always get something back.
What ODBC driver are you using?
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
