First, thank you Jeff for the PPD distribution of DBD-ODBC 0.39.
ActiveState hasn't been able to get past 0.28.
Second, I still have problems with stored procedures that return
multiple result sets. The code below should return two results
sets: one result set has 1 row of five columns, the other has one
row of two columns. With the trace level set to 9, I see that
it recognizes a second result set, gets the column names for it,
but doesn't return any rows. Does the code below need to be changed
to allow for multiple result sets, or is the problem elsewhere?
Thank you,
glen
----------------------------------------------------------------------------
------
#!d:\perl\bin\perl.exe -w
use DBI;
($instance, $user, $password, $db) =
('gaccardo\test', 'sa', 'gaccardo', 'testdb');
$dbh = DBI->connect("dbi:ODBC:DRIVER={SQL Server};".
"SERVER=$instance;UID=$user;PWD=$password;".
"DATABASE=$db", {RaiseError => 0, PrintError => 0})
or die "\n\nCannot connect.\n\n$DBI::errstr\n";
$dbh->{LongReadLen} = 65536;
unlink 'dbitrace.log' if (-e 'dbitrace.log') ;
DBI->trace(9, 'dbitrace.log');
$sth = $dbh->prepare("exec sp_depends \@objname = ?");
$sth->bind_param(1, '[dbo].[prss_bigint_tb]');
$sth->execute();
while (@query_results = $sth->fetchrow_array) {
print join (', ', @query_results) . "\n";
}
if (DBI::err) {
print "\n$DBI::errstr\n "
}
----------------------------------------------------------------------------
------
DBI 1.21-nothread dispatch trace level set to 9
>> prepare DISPATCH (DBI::db=HASH(0x1fed410) rc1/1 @2 g0 a2007448)
at D:\test\test.pl line 17
-> prepare for DBD::ODBC::db (DBI::db=HASH(0x1fed410)~0x1fed35c 'exec
sp_depends @objname = ?')
New DBI::st (for DBD::ODBC::st, parent=DBI::db=HASH(0x1fed35c), id=)
dbih_setup_handle(DBI::st=HASH(0x1fed488)=>DBI::st=HASH(0x1fed518),
DBD::ODBC::st, 1fed4dc, Null!)
dbih_make_com(DBI::db=HASH(0x1fed35c), DBD::ODBC::st, 200)
dbih_setup_attrib(DBI::st=HASH(0x1fed518), Err, DBI::db=HASH(0x1fed35c))
SCALAR(0x1ce1c50) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x1fed518), State,
DBI::db=HASH(0x1fed35c)) SCALAR(0x1ce1c98) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x1fed518), Errstr,
DBI::db=HASH(0x1fed35c)) SCALAR(0x1ce1c74) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x1fed518), Handlers,
DBI::db=HASH(0x1fed35c)) ARRAY(0x1fed3d4) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x1fed518), Debug,
DBI::db=HASH(0x1fed35c)) 0 (already defined)
dbih_setup_attrib(DBI::st=HASH(0x1fed518), FetchHashKeyName,
DBI::db=HASH(0x1fed35c)) 'NAME' (already defined)
dbih_setup_attrib(DBI::st=HASH(0x1fed518), HandleError,
DBI::db=HASH(0x1fed35c)) undef (not defined)
ignore named placeholders = 0
dbd_preparse scanned 1 distinct placeholders
dbd_st_prepare'd sql f34088664
exec sp_depends @objname = ?
<- prepare= DBI::st=HASH(0x1fed488) at D:\test\test.pl line 17
>> bind_param DISPATCH (DBI::st=HASH(0x1fed488) rc1/1 @3 g0 a200182c)
at D:\test\test.pl line 18
-> bind_param for DBD::ODBC::st (DBI::st=HASH(0x1fed488)~0x1fed518 1
'[dbo].[prss_bigint_tb]')
bind 1 <== '[dbo].[prss_bigint_tb]' (attribs: )
bind 1 <== '[dbo].[prss_bigint_tb]' (size 22/23/0, ptype 4, otype 1)
bind 1 <== '[dbo].[prss_bigint_tb]' (len 22/22, null 0)
bind 1: CTy=1, STy=VARCHAR, CD=80, Sc=0, VM=22.
SQLBindParameter: idx = 1: fParamType=1, name=1, fCtype=1, SQL_Type =
12, cbColDef=80, scale=22, rgbValue = 1e19e64, cbValueMax=22, cbValue = 22
<- bind_param= 1 at D:\test\test.pl line 18
>> execute DISPATCH (DBI::st=HASH(0x1fed488) rc1/1 @1 g0 a2000c04)
at D:\test\test.pl line 19
-> execute for DBD::ODBC::st (DBI::st=HASH(0x1fed488)~0x1fed518)
dbd_st_execute (outparams = 0)...
dbd_st_execute (for hstmt 34088664 before)...
dbd_st_execute (for hstmt 34088664 after)...
dbd_describe sql 34088664: num_fields=5
col 1: UNICODE VARCHAR len=514 disp=258, prec=257 scale=0
col 2: UNICODE VARCHAR len= 32 disp= 17, prec= 16 scale=0
col 3: UNICODE VARCHAR len= 14 disp= 8, prec= 7 scale=0
col 4: UNICODE VARCHAR len= 16 disp= 9, prec= 8 scale=0
col 5: UNICODE VARCHAR len=256 disp=129, prec=128 scale=0
col 1: 'name' sqltype=UNICODE VARCHAR, ctype=SQL_C_CHAR, maxlen=258
col 2: 'type' sqltype=UNICODE VARCHAR, ctype=SQL_C_CHAR, maxlen=17
col 3: 'updated' sqltype=UNICODE VARCHAR, ctype=SQL_C_CHAR, maxlen=8
col 4: 'selected' sqltype=UNICODE VARCHAR, ctype=SQL_C_CHAR, maxlen=9
col 5: 'column' sqltype=UNICODE VARCHAR, ctype=SQL_C_CHAR, maxlen=129
<- execute= -1 at D:\test\test.pl line 19
>> fetchrow_array DISPATCH (DBI::st=HASH(0x1fed488) rc1/1 @1 g1 a0) at
D:\test\test.pl line 20
-> fetchrow_array for DBD::ODBC::st (DBI::st=HASH(0x1fed488)~0x1fed518)
SQLFetch rc 0
dbih_setup_fbav for 5 fields => 0x1fed50c
fetch num_fields=5
fetch col#0 name datalen=18 displ=258
fetch col#1 type datalen=10 displ=17
fetch col#2 updated datalen=2 displ=8
fetch col#3 selected datalen=2 displ=9
fetch col#4 column datalen=7 displ=129
<- fetchrow_array= ( 'dbo.prss_bigint_tb' 'user table' 'no' 'no'
'tbigint' ) [5 items] row1 at D:\test\test.pl line 20
>> fetchrow_array DISPATCH (DBI::st=HASH(0x1fed488) rc1/1 @1 g1 a0) at
D:\test\test.pl line 20
-> fetchrow_array for DBD::ODBC::st (DBI::st=HASH(0x1fed488)~0x1fed518)
SQLFetch rc 100
SQLGetFunctions - SQL_MoreResults supported: 1
MORE Results!
dbd_describe sql 34088664: num_fields=2
col 1: UNICODE VARCHAR len=514 disp=258, prec=257 scale=0
col 2: UNICODE VARCHAR len= 32 disp= 17, prec= 16 scale=0
col 1: 'name' sqltype=UNICODE VARCHAR, ctype=SQL_C_CHAR, maxlen=258
col 2: 'type' sqltype=UNICODE VARCHAR, ctype=SQL_C_CHAR, maxlen=17
<- fetchrow_array= ( ) [0 items] row1 at D:\test\test.pl line 20
-> $DBI::err (*) FETCH from lasth=DBI::st=HASH(0x1fed518)
<- err= undef
-- DBI::END
>> disconnect_all DISPATCH (DBI::dr=HASH(0x2045e20) rc1/4 @1 g0
a20041f8) at D:/Perl/site/lib/DBI.pm line 533 via D:\test\test.pl line 0
-> disconnect_all for DBD::ODBC::dr (DBI::dr=HASH(0x2045e20)~0x1fed458)
<- disconnect_all= '' at D:/Perl/site/lib/DBI.pm line 533 via
D:\test\test.pl line 0
>> DESTROY DISPATCH (DBI::db=HASH(0x1fed35c) rc1/1 @1 g0 a0) during
global destruction
-> DESTROY for DBD::ODBC::db (DBI::db=HASH(0x1fed35c)~INNER)
<- DESTROY= undef during global destruction
dbih_clearcom (h 0x1fed410, com 0x1ce2478):
FLAGS 0x311: COMSET Warn PrintError AutoCommit
TYPE 2
PARENT DBI::dr=HASH(0x1fed458)
KIDS 1 (1 active)
IMP_DATA undef in 'DBD::ODBC::db'
LongReadLen 65536
>> DESTROY DISPATCH (DBI::dr=HASH(0x1fed458) rc1/1 @1 g0 a0) during
global destruction
-> DESTROY in DBD::_::common for DBD::ODBC::dr
(DBI::dr=HASH(0x1fed458)~INNER)
<- DESTROY= undef during global destruction
dbih_clearcom (h 0x2045e20, com 0x203fee0):
FLAGS 0x215: COMSET Active Warn AutoCommit
TYPE 1
PARENT undef
KIDS 1 (1 active)
IMP_DATA undef in 'DBD::ODBC::dr'
dbih_clearcom 0x2045e20 (com 0x203fee0, type 1) done.
dbih_clearcom 0x1fed410 (com 0x1ce2478, type 2) done.
>> DESTROY DISPATCH (DBI::st=HASH(0x1fed518) rc1/1 @1 g0 a0) during
global destruction
-> DESTROY for DBD::ODBC::st (DBI::st=HASH(0x1fed518)~INNER)
<- DESTROY= undef during global destruction
dbih_clearcom (h 0x1fed488, com 0x1fec1b4):
FLAGS 0x111: COMSET Warn PrintError
TYPE 3
PARENT undef
KIDS 0 (0 active)
IMP_DATA undef in 'DBD::ODBC::st'
LongReadLen 65536
NUM_OF_FIELDS 2
NUM_OF_PARAMS 1
dbih_clearcom 0x1fed488 (com 0x1fec1b4, type 3) done.
>> DESTROY DISPATCH (DBI::dr=HASH(0x2045e20) rc1/1 @1 g0 a0) during
global destruction
<> DESTROY for DBI::dr=HASH(0x2045e20) ignored (inner handle gone)
>> DESTROY DISPATCH (DBI::st=HASH(0x1fed488) rc1/1 @1 g0 a0) during
global destruction
<> DESTROY for DBI::st=HASH(0x1fed488) ignored (inner handle gone)
>> DESTROY DISPATCH (DBI::db=HASH(0x1fed410) rc1/1 @1 g0 a0) during
global destruction
<> DESTROY for DBI::db=HASH(0x1fed410) ignored (inner handle gone)
