DBD::Sybase prepare statement returns empty query hash but no errstr

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I am trying to prepare a multi-statement query as referenced here: https://metacpan.org/pod/DBD::Sybase

When I use the prepare statement, and then use Data::Dumper, it returns an empty hash:

$VAR1 = bless( {}, 'DBI::st' );

When I use dbh->trace(3), I get the following:

 -> prepare for DBD::Sybase::db (DBI::db=HASH(0x80a2974)~0x80a28e4 'de
+clare @skiprows int, @getrows int; select @skiprows = 0; select @getr
+ows = 10000; set rowcount @skiprows; select * into #debt from debt or
+der by debt_id, debt_no asc; set rowcount @getrows;select * from debt
+ where (debt_id NOT IN ( select * from #debt ) ) ORDER BY debt_id, de
+bt_no ASC') thr#804d1a0
    syb_st_prepare() -> inUse = 0
    syb_st_prepare() -> set inUse
    <- prepare= ( DBI::st=HASH(0x80b2884) ) [1 items] at export_debt.p
+l line 52
    -> errstr in DBD::_::common for DBD::Sybase::db (DBI::db=HASH(0x80
+a2974)~0x80a28e4) thr#804d1a0
    <- errstr= ( '' ) [1 items] at export_debt.pl line 63
err
$VAR1 = '';
    -> FIRSTKEY in DBD::_::common for DBD::Sybase::st (DBI::st=HASH(0x
+80a2a24)~INNER) thr#804d1a0
    <- FIRSTKEY= ( undef ) [1 items] at Dumper.pm line 232 via  at Dum
+per.pm line 604
[download]

Here is the code in question:

 my $getrows = 10000;
 my $skiprows = 0;
 my $i = 0;


$db->{dbh}->trace(3);

 my $query = $db->{dbh}->prepare( "declare \@skiprows int, \@getrows i
+nt;     " .
"select \@skiprows = $skiprows; " .
 "select \@getrows = $getrows; " .
 "set rowcount \@skiprows; " .
 "select * into #debt from debt " .
 "order by debt_id, debt_no asc; " .
 "set rowcount \@getrows;" .
 "select * from debt where " .
 "(debt_id NOT IN ( select * from #debt ) ) " .
 "ORDER BY debt_id, debt_no ASC" );

print STDERR "err\n", Dumper( $db->{dbh}->errstr );
print STDERR "query\n", Dumper($query);
[download]

I am not sure what I am doing wrong, and I can't get enough information to tell me what is going on.

Any help would be appreciated.

Thanks.

Comment on DBD::Sybase prepare statement returns empty query hash but no errstr Select or Download Code

Replies are listed 'Best First'.
Re: DBD::Sybase prepare statement returns empty query hash but no errstr by Jenda (Abbot) on Oct 30, 2019 at 15:59 UTC
OT, not even Sybase cares about newlines so there's no need to bend over backwards with all those quotes and dots. `my $query = $db->{dbh}->prepare( " declare \@skiprows int, \@getrows int; select \@skiprows = $skiprows; select \@getrows = $getrows; set rowcount \@skiprows; select * into #debt from debt order by debt_id, debt_no asc; set rowcount \@getrows; select * from debt where (debt_id NOT IN ( select * from #debt ) ) ORDER BY debt_id, debt_no ASC" );` [download] Jenda 1984 was supposed to be a warning, not a manual!	[reply] [d/l]
Re^2: DBD::Sybase prepare statement returns empty query hash but no errstr by jcb (Parson) on Oct 30, 2019 at 23:53 UTC
I use a similar syntax for lining up my source code: `my $st = $dbh->prepare (q[SELECT foo, bar, baz FROM whatever] .q[ WHERE foo = xyz AND bar IS NOT NULL AND ...]);` [download]	[reply] [d/l]
Re^3: DBD::Sybase prepare statement returns empty query hash but no errstr by Jenda (Abbot) on Oct 31, 2019 at 12:23 UTC
The thing is there's no need to close and reopen the string literal. You are working too hard and if you then need to copy&paste the SQL elsewhere you have to work again. `my $st = $dbh->prepare(q[ SELECT foo, bar, baz FROM whatever WHERE foo = xyz AND bar IS NOT NULL AND ... ]);` [download] Jenda 1984 was supposed to be a warning, not a manual!	[reply] [d/l]
Re^4: DBD::Sybase prepare statement returns empty query hash but no errstr by jcb (Parson) on Nov 01, 2019 at 03:46 UTC
Re^5: DBD::Sybase prepare statement returns empty query hash but no errstr by Jenda (Abbot) on Nov 01, 2019 at 10:06 UTC
Re: DBD::Sybase prepare statement returns empty query hash but no errstr by jcb (Parson) on Oct 29, 2019 at 23:16 UTC
DBI handles are tied aggregates and appear empty if you try to iterate over them, as `Data::Dumper` or the debugger does. Try dumping `$query->{Statement}` instead.	[reply] [d/l] [select]
Re^2: DBD::Sybase prepare statement returns empty query hash but no errstr by rhysshadow (Novice) on Oct 30, 2019 at 16:06 UTC
I printed out $dbh->{Statement}, and I can see my query now. When I have the trace set to 6, these are the messages I get: -> prepare for DBD::Sybase::db (DBI::db=HASH(0x80a2254)~0x80a21c4 +'declare @skiprows int, @getrows int; select @skiprows = 0; select @g +etrows = 10000; set rowcount @skiprows; select * into #debt from debt + order by debt_id, debt_no asc; set rowcount @getrows;select * from d +ebt where (debt_id NOT IN ( select * from #debt ) ) ORDER BY debt_id, + debt_no ASC') thr#804d1a0 New 'DBI::st' (for DBD::Sybase::st, parent=DBI::db=HASH(0x80a21c4) +, id=undef) dbih_setup_handle(DBI::st=HASH(0x80ab054)=>DBI::st=HASH(0x80a2304) +, DBD::Sybase::st, 80ab014, Null!) dbih_make_com(DBI::db=HASH(0x80a21c4), 80a9804, DBD::Sybase::st, 4 +20, 0) thr#804d1a0 dbih_setup_attrib(DBI::st=HASH(0x80a2304), Err, DBI::db=HASH(0x80a +21c4)) SCALAR(0x81d0754) (already defined) dbih_setup_attrib(DBI::st=HASH(0x80a2304), State, DBI::db=HASH(0x8 +0a21c4)) SCALAR(0x81d07b4) (already defined) dbih_setup_attrib(DBI::st=HASH(0x80a2304), Errstr, DBI::db=HASH(0x +80a21c4)) SCALAR(0x81d0784) (already defined) dbih_setup_attrib(DBI::st=HASH(0x80a2304), TraceLevel, DBI::db=HAS +H(0x80a21c4)) 6 (already defined) dbih_setup_attrib(DBI::st=HASH(0x80a2304), FetchHashKeyName, DBI:: +db=HASH(0x80a21c4)) 'NAME_lc' (already defined) dbih_setup_attrib(DBI::st=HASH(0x80a2304), HandleSetErr, DBI::db=H +ASH(0x80a21c4)) undef (not defined) dbih_setup_attrib(DBI::st=HASH(0x80a2304), HandleError, DBI::db=HA +SH(0x80a21c4)) undef (not defined) dbih_setup_attrib(DBI::st=HASH(0x80a2304), ReadOnly, DBI::db=HASH( +0x80a21c4)) undef (not defined) dbih_setup_attrib(DBI::st=HASH(0x80a2304), Profile, DBI::db=HASH(0 +x80a21c4)) undef (not defined) syb_st_prepare() -> inUse = 0 syb_st_prepare() -> set inUse <- prepare= ( DBI::st=HASH(0x80ab054) ) [1 items] at export_debt.p +l line 62 <> FETCH= ( 'declare @skiprows int, @getrows int; select @skiprows + = 0; select @getrows = 10000; set rowcount @skiprows; select * into +#debt from debt order by debt_id, debt_no asc; set rowcount @getrows; +select * from debt where (debt_id NOT IN ( select * from #debt ) ) OR +DER BY debt_id, debt_no ASC' ) [1 items] ('Statement' from cache) at +/usr/lib/perl5/site_perl/5.28.2/i686-linux-thread-multi/Data/Dumper.p +m line 604 via at export_debt.pl line 78 [download] It then sits there, not doing the query. Is there something wrong with trying to do multiple statements thru dbd::Sybase? Or, is there something wrong with my query?	[reply] [d/l]
Re^3: DBD::Sybase prepare statement returns empty query hash but no errstr by jcb (Parson) on Oct 30, 2019 at 23:57 UTC
DBI is pretty firmly in the "prepare one statement at a time" camp. I am not familiar with DBD::Sybase; are you sure that that entire statement group is not being parsed as "`declare @skiprows int, @getrows int;` `<unparsed leftovers>`" which does nothing and returns nothing?	[reply] [d/l]
Re^4: DBD::Sybase prepare statement returns empty query hash but no errstr by soonix (Chancellor) on Oct 31, 2019 at 07:19 UTC
Re^5: DBD::Sybase prepare statement returns empty query hash but no errstr by jcb (Parson) on Nov 01, 2019 at 03:44 UTC
Re^3: DBD::Sybase prepare statement returns empty query hash but no errstr by poj (Abbot) on Oct 31, 2019 at 15:45 UTC
is there something wrong with my query? Maybe but I'm having difficulty understanding it `select * into #debt from debt` makes a temporary copy of debt. Next query looks for records in debt that are not in the copy #debt. `select * from debt where (debt_id NOT IN ( select * from #debt ) )` [download] I wouldn't expect there to be any ? poj	[reply] [d/l] [select]
Re^4: DBD::Sybase prepare statement returns empty query hash but no errstr by rhysshadow (Novice) on Nov 04, 2019 at 16:41 UTC
Re: DBD::Sybase prepare statement returns empty query hash but no errstr by rhysshadow (Novice) on Oct 29, 2019 at 21:20 UTC
More information (I made this without realizing I wasn't logged in to PM): I set the stack level trace to trace(6), and got some other messages: ows: '2895385' DBI::db=HASH(0x80a28e4) trace level set to 0x0/6 (DBI @ 0x0/0) in +DBI 1.642-ithread (pid 8200) -> prepare for DBD::Sybase::db (DBI::db=HASH(0x80a2974)~0x80a28e4 +'declare @skiprows int, @getrows int; select @skiprows = 0; select @g +etrows = 10000; set rowcount @skiprows; select * into #debt from debt + order by debt_id, debt_no asc; set rowcount @getrows;select * from d +ebt where (debt_id NOT IN ( select * from #debt ) ) ORDER BY debt_id, + debt_no ASC') thr#804d1a0 New 'DBI::st' (for DBD::Sybase::st, parent=DBI::db=HASH(0x80a28e4) +, id=undef) dbih_setup_handle(DBI::st=HASH(0x80a2f54)=>DBI::st=HASH(0x80a2a24) +, DBD::Sybase::st, 80a2f14, Null!) dbih_make_com(DBI::db=HASH(0x80a28e4), 80b5004, DBD::Sybase::st, 4 +20, 0) thr#804d1a0 dbih_setup_attrib(DBI::st=HASH(0x80a2a24), Err, DBI::db=HASH(0x80a +28e4)) SCALAR(0x81d0754) (already defined) dbih_setup_attrib(DBI::st=HASH(0x80a2a24), State, DBI::db=HASH(0x8 +0a28e4)) SCALAR(0x81d07b4) (already defined) dbih_setup_attrib(DBI::st=HASH(0x80a2a24), Errstr, DBI::db=HASH(0x +80a28e4)) SCALAR(0x81d0784) (already defined) dbih_setup_attrib(DBI::st=HASH(0x80a2a24), TraceLevel, DBI::db=HAS +H(0x80a28e4)) 6 (already defined) dbih_setup_attrib(DBI::st=HASH(0x80a2a24), FetchHashKeyName, DBI:: +db=HASH(0x80a28e4)) 'NAME_lc' (already defined) dbih_setup_attrib(DBI::st=HASH(0x80a2a24), HandleSetErr, DBI::db=H +ASH(0x80a28e4)) undef (not defined) dbih_setup_attrib(DBI::st=HASH(0x80a2a24), HandleError, DBI::db=HA +SH(0x80a28e4)) undef (not defined) dbih_setup_attrib(DBI::st=HASH(0x80a2a24), ReadOnly, DBI::db=HASH( +0x80a28e4)) undef (not defined) dbih_setup_attrib(DBI::st=HASH(0x80a2a24), Profile, DBI::db=HASH(0 +x80a28e4)) undef (not defined) syb_st_prepare() -> inUse = 0 syb_st_prepare() -> set inUse <- prepare= ( DBI::st=HASH(0x80a2f54) ) [1 items] at export_debt.p +l line 52 -> errstr in DBD::_::common for DBD::Sybase::db (DBI::db=HASH(0x80 +a2974)~0x80a28e4) thr#804d1a0 <- errstr= ( '' ) [1 items] at export_debt.pl line 63 err $VAR1 = ''; -> FIRSTKEY in DBD::_::common for DBD::Sybase::st (DBI::st=HASH(0x +80a2a24)~INNER) thr#804d1a0 <- FIRSTKEY= ( undef ) [1 items] at /usr/lib/perl5/site_perl/5.28. +2/i686-linux-thread-multi/Data/Dumper.pm line 232 via at /usr/lib/pe +rl5/site_perl/5.28.2/i686-linux-thread-multi/Data/Dumper.pm line 604 query $VAR1 = bless( {}, 'DBI::st' ); -> execute for DBD::Sybase::st (DBI::st=HASH(0x80a2f54)~0x80a2a24) + thr#804d1a0 syb_alloc_cmd() -> CS_COMMAND 85ebd90 for CS_CONNECTION 85a5060 cmd_execute() -> ct_command() OK cmd_execute() -> ct_send() OK cmd_execute() -> set inUse flag [download]	[reply] [d/l]