Skip Menu |
 

This queue is for tickets about the DBD-ODBC CPAN distribution.

Report information
The Basics
Id: 69864
Status: resolved
Priority: 0/
Queue: DBD-ODBC

People
Owner: Nobody in particular
Requestors: kaa.email [...] gmail.com
Cc:
AdminCc:

Bug Information
Severity: (no value)
Broken in: (no value)
Fixed in: 1.36_2



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>
Download (untitled) / with headers
text/plain 3.3k
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
Download (untitled) / with headers
text/html 10.4k
Download dbi_1.log
application/octet-stream 7.1k

Message body not shown because it is not plain text.

Download dbi_2.log
application/octet-stream 7.1k

Message body not shown because it is not plain text.

Download (untitled) / with headers
text/plain 3.8k
On Thu Jul 28 12:13:56 2011, kaa.email@gmail.com wrote: Show quoted text
> The bug is that fetching certain (small) numbers from an MS Access
database Show quoted text
> 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 Show quoted text
> 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 Show quoted text
> 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
Apologies for getting to this so late but you put your rt in the DBI queue and it has only just been moved to the correct queue which is DBD::ODBC. If you are still having this problem could you get back to me and I'll take a look at it. Martin -- Martin J. Evans Wetherby, UK
Subject: Re: [rt.cpan.org #69864] Errors in reading MS Access database
Date: Sun, 11 Dec 2011 19:16:49 -0500
To: bug-DBD-ODBC [...] rt.cpan.org
From: "kaa.email" <kaa.email [...] gmail.com>
Download (untitled) / with headers
text/plain 4.4k
Martin, Well, I worked around that bug and haven't had much need to deal with MS Access databases from Perl since, so it's not exactly that I'm still having this problem, but on the other hand I doubt it went away on its own :-) Kaa On Thu, Dec 8, 2011 at 04:07, Martin J Evans via RT < bug-DBD-ODBC@rt.cpan.org> wrote: Show quoted text
> <URL: https://rt.cpan.org/Ticket/Display.html?id=69864 > > > On Thu Jul 28 12:13:56 2011, kaa.email@gmail.com wrote:
> > 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
> > Apologies for getting to this so late but you put your rt in the DBI > queue and it has only just been moved to the correct queue which is > DBD::ODBC. If you are still having this problem could you get back to me > and I'll take a look at it. > > Martin > -- > Martin J. Evans > Wetherby, UK >
On Sun Dec 11 19:17:01 2011, kaa.email@gmail.com wrote: Show quoted text
> Martin, > > Well, I worked around that bug and haven't had much need to deal with > MS > Access databases from Perl since, so it's not exactly that I'm still > having > this problem, but on the other hand I doubt it went away on its own :- > )
Neither do I but as you weren't using it I was in no rush and it waited until I had a bored moment. It turns out the significant thing about the doubles that did not come back is that they are the 2 longest doubles e.g. -0.0171891639590107 which is 19 chrs long. MS Access reports the SQL_COLUMN_DISPLAY_SIZE as 22 and DBD::ODBC gives it a buffer of 23 which should fit. However, in SQLDescribeCol MS Access reports a display size or precision of 53. If I increase the buffer sent to 53+1 the values come back. This is undoubtably a bug in MS Access. The accdb driver is not fixed by this - you still get E-2 so I think this one is even more broken. I've made a specific workaround for MS Access and doubles and it will be in the next release 1.36_2. Martin -- Martin J. Evans Wetherby, UK


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.