>
>
> First, thank you Jeff for the PPD distribution of DBD-ODBC 0.39.
> ActiveState hasn't been able to get past 0.28.
You are welcome. Note that I have posted messages to ActiveState's mailing
list for ActivePerl and I get no response as to what the problem is. I made
some changes to the script which should make it better. The key one is the
do {
get results for result set
} while ($sth->{odbc_more_results});
I made a few nit changes and use strict (it's a good habit!) and the
RaiseError=1 to RaiseError => 1.
I don't have [dbo].[prss_bigint_tb], so I couldn't actually run it. I'm not
a SQL Server expert and don't have time to research what it would take to
make it run here, but here's my version...(the connect changed to make it
easier for me to test...)
#!perl.exe -w
use strict;
use DBI;
my $dbh = DBI->connect($ENV{DBI_DSN}, $ENV{DBI_USER}, $ENV{DBI_PASS},
{RaiseError => 1, 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');
my $sth = $dbh->prepare("exec sp_depends \@objname = ?");
$sth->bind_param(1, '[users].[perl_dbd_test]');
$sth->execute();
do {
my @query_results;
while (@query_results = $sth->fetchrow_array) {
print join (', ', @query_results) . "\n";
}
} while ( $sth->{odbc_more_results} );
if ($DBI::err) {
print "\n$DBI::errstr\n "
}
>
> 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?
Yep you need a change...
>
> 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)
>
