I've been using Perl scripts as an interface to my MS Access (Office 2007) databases for quite some time but recently ran into what seems to be an obscure issue. I say 'obscure' because internet searches with appropriate keyword terms usually land me on a PerlMonks page where usually quickly find my answer since others have experienced similar issues before me. I can't seem to dig up a pre-existing explanation/solution and so I present a distilled synopsis of the original more complicated problem.

The Setup:

Basically, I've distilled this down to a simple example using a simplified MS Access DB. The database (Play.mdb) is sitting in a folder and a symbolic data source (System DSN) has been created called "PLAY". I have to use the 32 bit version of the ODBC Data Source Administrator as I'm connecting to an older Office 2007 database which is only available in 32 bit architecture (though my Win 7 is 64-bit). Yup. I'm a dinosaur using an old OS and older (purchased) copy of MS Office and I don't feel like upgrading to new software if that is the solution.

The simplified database contains a single table called Test which has but a single row and contains 4 columns generically labeled (A-D). These columns all have the Data Type=Number and columns A-C are defined as Single while I've changed column D to Double.

The Code:

No laughing at inefficient or naïve Perl code (I learn just enough Perl to get by).

use DBI;

my ($dbh, $sth, $a, $b, $c, $d);

$dbh = DBI->connect('DBI:ODBC:PLAY', '', '', {RaiseError => 1, AutoCom
+mit => 1});

$sth = $dbh->prepare("SELECT A, B, C, D FROM Test");
$sth->execute();
($a, $b, $c, $d) = $sth->fetchrow;

print "A=$a\n";
print "B=$b\n";
print "C=$c\n";
print "D=$d\n";

print "\n";

$sth = $dbh->prepare("SELECT A+0, B+0, C+0, D+0 FROM Test");
$sth->execute();
($a, $b, $c, $d) = $sth->fetchrow;

print "A=$a\n";
print "B=$b\n";
print "C=$c\n";
print "D=$d\n";
[download]

A=6.02518
B= 
C= 
D=8.99280607700348E-2

A=6.02517986297607
B=3.50719429552555E-2
C=8.99280607700348E-2
D=8.99280607700348E-2
[download]

The Problem:

Basically, in a nutshell, I noticed this when certain calculated values in a database table were being returned as empty (but not 'undef') values from my query. You'll see in the first query that the values for B and C are just plain missing. The second query only varies by adding zero to the values for the columns (which I assume may be holding the computed amount as a Double value rather than Single). This allows you to get an idea of what the missing numbers are in the first query. To test this precision theory I changed column D in the table to a Double instead of a Single and then it started showing up as expected in the first query. It should be noted that this problem only seems to occur when the (single precision) value ends up being expressed as a number in scientific notation (e.g. "8.992806E-02").

As I mentioned earlier, this problem is really as simple of a distillation as I can seem to make from what was a much larger program that generates many MS Access tables and fetches the entire contents of the table with a ($sth->fetchall_arrayref) before pasting it into a programmatically constructed spreadsheet representation of the database table. I've tried to strip-away all that clutter to show a simpler example of scientific notation numbers not being returned as expected (or, at least, as I expected) from a simple query.

As my actual query involves a "SELECT * FROM $table_name" type of a query, the "+0" hack will not work as a solution (would be too ugly to live with anyway). My other option is to go back through all of my code that generates these tables and only use DOUBLE rather than SINGLE in my CREATE TABLE statements. This may provide an appropriate workaround (though I do not need that much precision on these values). The other (more tedious) change might be to find the parts of the code that are calculating these values and specifically round the results to just a handful of digits which should keep some of them from having to be expressed by scientific notation (which seems to be the root of this problem).

As I need to progress on with this, I'll likely try a SINGLE => DOUBLE type solution to see if that will get me past this roadblock but I'd really like to know why it seems that single precision numbers expressed in scientific notation cannot be retrieved through the DBI interface. I'm open to all the enlightenment I can get on this quirky little issue.

Thanks.

Been dazed and confused for so long...