I have a query that returns '3' (the correct answer) when I paste
the SQL into an Access Query and run it. However, the same query
returns '0' when I run it using DBD::ODBC. Can anyone please
help me find why?
Here is the simple test script:
_________________________________________________
use warnings;
use strict;
use DBI;
$| = 1;
DBI->trace(3,'trace.log');
my $DSN = 'driver=Microsoft Access Driver (*.mdb);dbq=BLAH.mdb';
my $dbh = DBI->connect("dbi:ODBC:$DSN", '', '', {RaiseError =>
1})
or die "Couldn't connect!";
my $count = $dbh->selectrow_array(<<"");
SELECT Count(YOQsScheduledCode)
FROM (
SMSMaster INNER JOIN tblYOQsScheduled
ON SMSMaster.AccountNum =
tblYOQsScheduled.SMSAccountNum
) INNER JOIN tlkpSiteIDNurseStation
ON SMSMaster.NurseStation =
tlkpSiteIDNurseStation.NurseStation
WHERE tlkpSiteIDNurseStation.Identifier = '1018'
AND ScheduledDate >= #2001-01-01#
AND ScheduledDate <= #2001-03-25#
AND ScheduledRespondents LIKE '*4*'
print "count: $count\n";
$dbh->disconnect;
_________________________________________________
Here is the trace:
_________________________________________________
DBI 1.14-nothread dispatch trace level set to 3
-> DBI->connect(dbi:ODBC:driver=Microsoft Access Driver
(*.mdb);dbq=BLAH.mdb, , ****, HASH(0x176f010))
-> DBI->install_driver(ODBC) for perl=5.006 pid=673689 ruid=0
euid=0
install_driver: DBD::ODBC loaded (version 0.28)
New DBI::dr (for DBD::ODBC::dr, parent=, id=)
dbih_setup_handle(DBI::dr=HASH(0x18328f8)=>DBI::dr=HASH(0x17f1080
), DBD::ODBC::dr, 0, Null!)
dbih_make_com(Null!, DBD::ODBC::dr, 92)
<- install_driver= DBI::dr=HASH(0x18328f8)
-> connect for DBD::ODBC::dr
(DBI::dr=HASH(0x18328f8)~0x17f1080 'driver=Microsoft Access
Driver (*.mdb);dbq=BLAH.mdb' '' **** HASH(0x17650d0))
New DBI::db (for DBD::ODBC::db,
parent=DBI::dr=HASH(0x17f1080), id=)
dbih_setup_handle(DBI::db=HASH(0x17f1020)=>DBI::db=HASH(0x17f1044
), DBD::ODBC::db, 183264c, Null!)
dbih_make_com(DBI::dr=HASH(0x17f1080), DBD::ODBC::db, 92)
Driver connect 'driver=Microsoft Access Driver
(*.mdb);dbq=BLAH.mdb', '', ''
<- connect= DBI::db=HASH(0x17f1020) at DBI.pm line 408.
-> STORE for DBD::ODBC::db (DBI::db=HASH(0x17f1044)~INNER
'RaiseError' 1)
STORE DBI::db=HASH(0x17f1044) 'RaiseError' => 1
<- STORE= 1 at DBI.pm line 433.
-> STORE for DBD::ODBC::db (DBI::db=HASH(0x17f1044)~INNER
'PrintError' 1)
STORE DBI::db=HASH(0x17f1044) 'PrintError' => 1
<- STORE= 1 at DBI.pm line 433.
-> STORE for DBD::ODBC::db (DBI::db=HASH(0x17f1044)~INNER
'AutoCommit' 1)
<- STORE= 1 at DBI.pm line 433.
<- connect= DBI::db=HASH(0x17f1020)
-> selectrow_array in DBD::_::db for DBD::ODBC::db
(DBI::db=HASH(0x17f1020)~0x17f1044 ' SELECT
Count(YOQsScheduledCode)
FROM (
SMSMaster INNER JOIN tblYOQsScheduled
ON SMSMaster.AccountNum =
tblYOQsScheduled.SMSAccountNum
) INNER JOIN tlkpSiteIDNurseStation
ON SMSMaster.NurseStation =
tlkpSiteIDNurseStation.NurseStation
WHERE tlkpSiteIDNurseStation.Identifier = '1018'
AND ScheduledDate >= #2001-01-01#
...')
2 -> prepare for DBD::ODBC::db (DBI::db=HASH(0x17f1044)~INNER '
SELECT Count(YOQsScheduledCode)
FROM (
SMSMaster INNER JOIN tblYOQsScheduled
ON SMSMaster.AccountNum =
tblYOQsScheduled.SMSAccountNum
) INNER JOIN tlkpSiteIDNurseStation
ON SMSMaster.NurseStation =
tlkpSiteIDNurseStation.NurseStation
WHERE tlkpSiteIDNurseStation.Identifier = '1018'
AND ScheduledDate >= #2001-01-01#
...' undef)
New DBI::st (for DBD::ODBC::st,
parent=DBI::db=HASH(0x17f1044), id=)
dbih_setup_handle(DBI::st=HASH(0x17f0ff0)=>DBI::st=HASH(0x17650b8
), DBD::ODBC::st, 17f1014, Null!)
dbih_make_com(DBI::db=HASH(0x17f1044), DBD::ODBC::st, 164)
dbd_st_prepare'd sql f27918464
SELECT Count(YOQsScheduledCode)
FROM (
SMSMaster INNER JOIN tblYOQsScheduled
ON SMSMaster.AccountNum =
tblYOQsScheduled.SMSAccountNum
) INNER JOIN tlkpSiteIDNurseStation
ON SMSMaster.NurseStation =
tlkpSiteIDNurseStation.NurseStation
WHERE tlkpSiteIDNurseStation.Identifier = '1018'
AND ScheduledDate >= #2001-01-01#
AND ScheduledDate <= #2001-03-25#
AND ScheduledRespondents LIKE '*4*'
2 <- prepare= DBI::st=HASH(0x17f0ff0) at DBI.pm line 938.
-> execute for DBD::ODBC::st
(DBI::st=HASH(0x17f0ff0)~0x17650b8)
dbd_st_execute (for sql f27918464 after)...
dbd_describe sql 27918464: num_fields=1
col 1: INTEGER len= 4 disp= 12, prec= 10 scale=0
col 1: 'Expr1000' sqltype=INTEGER, ctype=SQL_C_CHAR,
maxlen=12
<- execute= -1 at DBI.pm line 941.
-> fetchrow_array for DBD::ODBC::st
(DBI::st=HASH(0x17f0ff0)~0x17650b8)
SQLFetch rc 0
dbih_setup_fbav for 1 fields => 0x176f0f4
fetch num_fields=1
<- fetchrow_array= ( '0' ) [1 items] at DBI.pm line 942.
-> finish for DBD::ODBC::st
(DBI::st=HASH(0x17f0ff0)~0x17650b8)
<- finish= 1 at DBI.pm line 943.
<- selectrow_array= '0' at test.pl line 12.
<> DESTROY ignored for outer handle DBI::st=HASH(0x17f0ff0)
(inner DBI::st=HASH(0x17650b8))
-> DESTROY for DBD::ODBC::st (DBI::st=HASH(0x17650b8)~INNER)
<- DESTROY= undef at test.pl line 24.
-> disconnect for DBD::ODBC::db
(DBI::db=HASH(0x17f1020)~0x17f1044)
<- disconnect= 1 at test.pl line 25.
<> DESTROY ignored for outer handle DBI::db=HASH(0x17f1020)
(inner DBI::db=HASH(0x17f1044))
-> DESTROY for DBD::ODBC::db (DBI::db=HASH(0x17f1044)~INNER)
<- DESTROY= undef at unknown location!
-- DBI::END
-> disconnect_all for DBD::ODBC::dr
(DBI::dr=HASH(0x18328f8)~0x17f1080)
<- disconnect_all= '' at DBI.pm line 450.
-> DESTROY in DBD::_::common for DBD::ODBC::dr
(DBI::dr=HASH(0x17f1080)~INNER)
<- DESTROY= undef during global destruction.
<> DESTROY for DBI::dr=HASH(0x18328f8) ignored (inner handle
gone)
_________________________________________________
If I cut and paste the "prepared" SQL from the trace log into an
Access Query the result is '3'. This is the correct result.
Thank You for any help!
Phil R Lawrence
