| Subject: | Errors in reading MS Access database |
| Date: | Thu, 28 Jul 2011 12:13:42 -0400 |
| To: | bug-DBI [...] rt.cpan.org |
| From: | "Kaa ." <kaa.email [...] gmail.com> |
The bug is that fetching certain (small) numbers from an MS Access database
returns invalid values.Specifically, the reference that should point to a
number instead points to a NUL (chr(0)) or to a non-number string. It seems
that the issue is the inability to handle the numbers in scientific
notation.
The relevant data in the database looks as follows:
Index Returns *Index Returns* Index Number Month Return 63 1/31/2006
-0.0171891639590107 63 2/28/2006 1.04229344255789 63 3/31/2006
0.527619016616885 63 4/30/2006 0.778996070749438 63 5/31/2006
0.598470714678423 63 6/30/2006 1.5153242075065 63 7/31/2006
1.96514473171672 63 8/31/2006 3.69323515846371 63 9/30/2006
-0.0383453969681273 63 10/31/2006 1.9777348426463 63 11/30/2006
-0.949641474442719 63 12/31/2006 2.48912119074928
The snippet of the Perl code (Perl 5.14.0 on Windows 7) is
$dbh = open_db() or die "Cannot open the database, fatal...";
$dbh->trace(9, "C:/TEMP/dbi.log");
$sql = "SELECT [Index Returns].[Index Number], [Index Returns].Month, [Index
Returns].Return
FROM [Index Returns]
WHERE [Index Returns].[Index Number]=63 AND YEAR( [Index
Returns].Month)=2006";
$sth = $dbh->prepare($sql);
$sth->execute();
$res = $sth->fetchall_arrayref();
$dbh->trace(0);
foreach (@$res) {
($idx, $dt, $ret) = @$_;
print STDOUT "idx = <$idx>, dt = <$dt>, ret = <$ret>\n";
}
$dbh->disconnect();
and its output is either this (using the MS Access 2007 ODBC driver
(*.mdb)):
C:\Perl\bin>perl_32.exe F:\Andrei\Covi\pl\covi_test.pl
idx = <63>, dt = <2006-01-31 00:00:00>, ret = < >
idx = <63>, dt = <2006-02-28 00:00:00>, ret = <1.04229344255789>
idx = <63>, dt = <2006-03-31 00:00:00>, ret = <0.527619016616885>
idx = <63>, dt = <2006-04-30 00:00:00>, ret = <0.778996070749438>
idx = <63>, dt = <2006-05-31 00:00:00>, ret = <0.598470714678423>
idx = <63>, dt = <2006-06-30 00:00:00>, ret = <1.5153242075065>
idx = <63>, dt = <2006-07-31 00:00:00>, ret = <1.96514473171672>
idx = <63>, dt = <2006-08-31 00:00:00>, ret = <3.69323515846371>
idx = <63>, dt = <2006-09-30 00:00:00>, ret = < >
idx = <63>, dt = <2006-10-31 00:00:00>, ret = <1.9777348426463>
idx = <63>, dt = <2006-11-30 00:00:00>, ret = <-0.949641474442719>
idx = <63>, dt = <2006-12-31 00:00:00>, ret = <2.48912119074928>
(in the debugger the value of ret shows up as "\@" which is ASCII NUL)
or this (using the MS Access 2010 ODBC driver (*.mdb, *.accdb)):
C:\Perl\bin>perl_32.exe F:\Andrei\Covi\pl\covi_test.pl
idx = <63>, dt = <2006-01-31 00:00:00>, ret = <E-2>
idx = <63>, dt = <2006-02-28 00:00:00>, ret = <1.04229344255789>
idx = <63>, dt = <2006-03-31 00:00:00>, ret = <0.527619016616885>
idx = <63>, dt = <2006-04-30 00:00:00>, ret = <0.778996070749438>
idx = <63>, dt = <2006-05-31 00:00:00>, ret = <0.598470714678423>
idx = <63>, dt = <2006-06-30 00:00:00>, ret = <1.5153242075065>
idx = <63>, dt = <2006-07-31 00:00:00>, ret = <1.96514473171672>
idx = <63>, dt = <2006-08-31 00:00:00>, ret = <3.69323515846371>
idx = <63>, dt = <2006-09-30 00:00:00>, ret = <E-2>
idx = <63>, dt = <2006-10-31 00:00:00>, ret = <1.9777348426463>
idx = <63>, dt = <2006-11-30 00:00:00>, ret = <-0.949641474442719>
idx = <63>, dt = <2006-12-31 00:00:00>, ret = <2.48912119074928>
Outside of Perl, e.g. importing the data into Excel through an ODBC query,
works well and returns correct values.
I am attaching the two trace files (dbi_1 is for the (*.mdb) driver, dbi_2
is for the (*.mdb, *.accdb) driver).
Kaa
Message body not shown because it is not plain text.
Message body not shown because it is not plain text.