Multiple selectrow_array calls with statement handle error

With DBI 1.602 and DBD::Sybase 1.08 I get:
Can't locate object method "DELETE" via package "DBI::st"
on the second selectrow_array call.

If I replace $sth with $sql in the selectrow_array calls, then it
works correctly.
I did find a similar problem here:
http://www.nntp.perl.org/group/perl.dbi.users/2007/06/msg314 86.html

but I thought that was fixed (did it get unfixed? :-)
I get the same error whether or not I have placeholders and bind parameters.

Here's the code:

use DBI;

my $dbh = DBI->connect(
'dbi:Sybase:server=SERVERNAME;database=dbname',
'user_name', 'password', {
RaiseError => 1,
});
my $sql = 'select some_column from my_table where my_id = ?';

my $sth = $dbh->prepare($sql);
my $id = 10600;

my $total;
( $total ) = $dbh->selectrow_array( $sth, undef, $id );
( $total ) = $dbh->selectrow_array( $sth, undef, $id );

Re: Multiple selectrow_array calls with statement handle error

Hi Douglas,

my 'perldoc DBI' (VERSION=1.43) says:
# @row_ary = $dbh->selectrow_array($statement, \%attr, @bind_values);
# This utility method combines "prepare", "execute" and "fetchrow_array"
# into a single call.

According to this
* it is correct to use your $sql
* there is no need to prepare the statement

Has this changed since V. 1.43?

Yours,
Christian Merz



Douglas Wilson schrieb:
> With DBI 1.602 and DBD::Sybase 1.08 I get:
> Can't locate object method "DELETE" via package "DBI::st"
> on the second selectrow_array call.
>
> If I replace $sth with $sql in the selectrow_array calls, then it
> works correctly.
> I did find a similar problem here:
> http://www.nntp.perl.org/group/perl.dbi.users/2007/06/msg314 86.html
>
> but I thought that was fixed (did it get unfixed? :-)
> I get the same error whether or not I have placeholders and bind parameters.
>
> Here's the code:
>
> use DBI;
>
> my $dbh = DBI->connect(
> 'dbi:Sybase:server=SERVERNAME;database=dbname',
> 'user_name', 'password', {
> RaiseError => 1,
> });
> my $sql = 'select some_column from my_table where my_id = ?';
>
> my $sth = $dbh->prepare($sql);
> my $id = 10600;
>
> my $total;
> ( $total ) = $dbh->selectrow_array( $sth, undef, $id );
> ( $total ) = $dbh->selectrow_array( $sth, undef, $id );

Re: Multiple selectrow_array calls with statement handle error

On Wed, Apr 09, 2008 at 04:29:48PM -0700, Douglas Wilson wrote:
> With DBI 1.602 and DBD::Sybase 1.08 I get:
> Can't locate object method "DELETE" via package "DBI::st"
> on the second selectrow_array call.
>
> If I replace $sth with $sql in the selectrow_array calls, then it
> works correctly.
> I did find a similar problem here:
> http://www.nntp.perl.org/group/perl.dbi.users/2007/06/msg314 86.html
>
> but I thought that was fixed (did it get unfixed? :-)
> I get the same error whether or not I have placeholders and bind parameters.
>
> Here's the code:
>
> use DBI;
>
> my $dbh = DBI->connect(
> 'dbi:Sybase:server=SERVERNAME;database=dbname',
> 'user_name', 'password', { RaiseError => 1 });
> my $sql = 'select some_column from my_table where my_id = ?';
> my $sth = $dbh->prepare($sql);
> my $id = 10600;
> my $total;
> ( $total ) = $dbh->selectrow_array( $sth, undef, $id );
> ( $total ) = $dbh->selectrow_array( $sth, undef, $id );

Code looks fine (it's okay to use an $sth instead of sql for the first arg).
Looks like a driver bug at first sight.
A DBI trace should shed more light on it.

Tim.

Re: Multiple selectrow_array calls with statement handle error

To Christian Merz:
I'm executing this 1000's of times in the actual code, that is why I've
prepared the query rather than passing in the sql string every time.

For now I am just calling execute(), fetchrow_array(), and finish()
explicitly.

Below find the trace output:

On Thu, Apr 10, 2008 at 7:54 AM, Tim Bunce wrote:
>
> On Wed, Apr 09, 2008 at 04:29:48PM -0700, Douglas Wilson wrote:

with trace(9)

$dbh->trace(9);
> > my $total;
> > ( $total ) = $dbh->selectrow_array( $sth, undef, $id );
> > ( $total ) = $dbh->selectrow_array( $sth, undef, $id );

> A DBI trace should shed more light on it.

trace output:
DBI::db=HASH(0x401aae68) trace level set to 0x0/9 (DBI @ 0x0/0)
in DBI 1.602-nothread (pid 1812)
-> selectrow_array for DBD::Sybase::db
(DBI::db=HASH(0x401aaef8)~0x401aae68 DBI::st=HASH(0x401ab0d8) undef
10600)
bind :p1 <== '10600' (attribs: )
bind :p1 () <== 10600 (size 5/8/0, ptype 6, otype 0)
bind :p1 <== '10600' (size 7, ok 1)
datafmt: type=8, name=, status=256, len=4
saved type: 8
cmd_execute() -> ct_send() OK
cmd_execute() -> set inUse flag
st_next_result() -> ct_results(4040) == 1
ct_res_info() returns 1 columns
STORE DBI::st=HASH(0x401ab0d8) 'NUM_OF_FIELDS' => 1
ct_describe(0): type = 10, maxlen = 8
describe() -> col 0, type 10, realtype 10
describe() retcode = 1
st_next_result() -> lasterr = 0, lastsev = 0
dbih_setup_fbav alloc for 1 fields
dbih_setup_fbav now 1 fields
syb_st_fetch() -> ct_fetch() = -204 (0 rows, 1 cols)
st_next_result() -> ct_results(4046) == 1
st_next_result() -> ct_results(4047) == 1
st_next_result() -> ct_results(4046) == 1
ct_results(4046) final retcode = -205
st_next_result() -> lasterr = 0, lastsev = 0
st_next_result() -> got CS_CMD_DONE: resetting ACTIVE,
moreResults, dyn_execed, exec_done
clear_sth_flags() -> resetting ACTIVE, moreResults, dyn_execed, exec_done
clear_sth_flags() -> reset inUse flag
syb_st_fetch() -> st_next_results() == 4046
clear_sth_flags() -> resetting ACTIVE, moreResults, dyn_execed, exec_done
clear_sth_flags() -> reset inUse flag
<- selectrow_array= ( ) [0 items] at ./tst line 22
-> selectrow_array for DBD::Sybase::db
(DBI::db=HASH(0x401aaef8)~0x401aae68 DBI::st=HASH(0x401ab0d8) undef
10600)
bind :p1 <== '10600' (attribs: )
bind :p1 () <== 10600 (size 5/8/7, ptype 6, otype 0)
bind :p1 <== '10600' (size 7, ok 1)
datafmt: type=8, name=, status=256, len=4
saved type: 8
cmd_execute() -> ct_send() OK
cmd_execute() -> set inUse flag
st_next_result() -> ct_results(4040) == 1
Can't locate object method "DELETE" via package "DBI::st" at ./tst line 23.
! -> DESTROY for DBD::Sybase::db (DBI::db=HASH(0x401aae68)~INNER)
syb_db_disconnect() -> ct_close()
! <- DESTROY= undef during global destruction

Re: Multiple selectrow_array calls with statement handle error

On Apr 10, 1:15 pm, douglasg.wil...@gmail.com (Douglas Wilson) wrote:
> To Christian Merz:
> I'm executing this 1000's of times in the actual code, that is why I've
> prepared the query rather than passing in the sql string every time.
>
> For now I am just calling execute(), fetchrow_array(), and finish()
> explicitly.
>
> Below find the trace output:
>
> On Thu, Apr 10, 2008 at 7:54 AM, Tim Bunce wrote:
>
> > On Wed, Apr 09, 2008 at 04:29:48PM -0700, Douglas Wilson wrote:
>
> with trace(9)
>
> $dbh->trace(9);
>
> > > my $total;
> > > ( $total ) = $dbh->selectrow_array( $sth, undef, $id );
> > > ( $total ) = $dbh->selectrow_array( $sth, undef, $id );
> > A DBI trace should shed more light on it.
>
> trace output:
> DBI::db=HASH(0x401aae68) trace level set to 0x0/9 (DBI @ 0x0/0)
> in DBI 1.602-nothread (pid 1812)
> -> selectrow_array for DBD::Sybase::db
> (DBI::db=HASH(0x401aaef8)~0x401aae68 DBI::st=HASH(0x401ab0d8) undef
> 10600)
> bind :p1 <== '10600' (attribs: )
> bind :p1 () <== 10600 (size 5/8/0, ptype 6, otype 0)
> bind :p1 <== '10600' (size 7, ok 1)
> datafmt: type=8, name=, status=256, len=4
> saved type: 8
> cmd_execute() -> ct_send() OK
> cmd_execute() -> set inUse flag
> st_next_result() -> ct_results(4040) == 1
> ct_res_info() returns 1 columns
> STORE DBI::st=HASH(0x401ab0d8) 'NUM_OF_FIELDS' => 1
> ct_describe(0): type = 10, maxlen = 8
> describe() -> col 0, type 10, realtype 10
> describe() retcode = 1
> st_next_result() -> lasterr = 0, lastsev = 0
> dbih_setup_fbav alloc for 1 fields
> dbih_setup_fbav now 1 fields
> syb_st_fetch() -> ct_fetch() = -204 (0 rows, 1 cols)
> st_next_result() -> ct_results(4046) == 1
> st_next_result() -> ct_results(4047) == 1
> st_next_result() -> ct_results(4046) == 1
> ct_results(4046) final retcode = -205
> st_next_result() -> lasterr = 0, lastsev = 0
> st_next_result() -> got CS_CMD_DONE: resetting ACTIVE,
> moreResults, dyn_execed, exec_done
> clear_sth_flags() -> resetting ACTIVE, moreResults, dyn_execed, exec_done
> clear_sth_flags() -> reset inUse flag
> syb_st_fetch() -> st_next_results() == 4046
> clear_sth_flags() -> resetting ACTIVE, moreResults, dyn_execed, exec_done
> clear_sth_flags() -> reset inUse flag
> <- selectrow_array= ( ) [0 items] at ./tst line 22
> -> selectrow_array for DBD::Sybase::db
> (DBI::db=HASH(0x401aaef8)~0x401aae68 DBI::st=HASH(0x401ab0d8) undef
> 10600)
> bind :p1 <== '10600' (attribs: )
> bind :p1 () <== 10600 (size 5/8/7, ptype 6, otype 0)
> bind :p1 <== '10600' (size 7, ok 1)
> datafmt: type=8, name=, status=256, len=4
> saved type: 8
> cmd_execute() -> ct_send() OK
> cmd_execute() -> set inUse flag
> st_next_result() -> ct_results(4040) == 1
> Can't locate object method "DELETE" via package "DBI::st" at ./tst line 23.
> ! -> DESTROY for DBD::Sybase::db (DBI::db=HASH(0x401aae68)~INNER)
> syb_db_disconnect() -> ct_close()
> ! <- DESTROY= undef during global destruction

Two questions, do you have RaiseError set anywhere? (I see no explicit
error checking)

And - could there be only one row of data?

phil

Re: Multiple selectrow_array calls with statement handle error

On Thu, Apr 10, 2008 at 11:15:30AM -0700, Douglas Wilson wrote:
> $dbh->trace(9);
> > > ( $total ) = $dbh->selectrow_array( $sth, undef, $id );
> > > ( $total ) = $dbh->selectrow_array( $sth, undef, $id );
>
> > A DBI trace should shed more light on it.

Thanks. I believe this was fixed in DBI 1.57 (svn rev 9639) June 2007

Fixed XS versions of select*_*() methods to call execute()
fetch() etc., with inner handle instead of outer.

Are you *certain* that that code is running with DBI 1.602?
Try adding a print of $DBI::VERSION.

If you are stuck on an old DBI version try something like this as
a workaround:

sub DBI::st::DELETE { my ($h, $key) = @_; delete $h->{$key} }

Tim.

Re: Multiple selectrow_array calls with statement handle error

On Thu, Apr 10, 2008 at 3:36 PM, Tim Bunce wrote:
> On Thu, Apr 10, 2008 at 11:15:30AM -0700, Douglas Wilson wrote:
>
> Are you *certain* that that code is running with DBI 1.602?
> Try adding a print of $DBI::VERSION.

Yes, I am certain. This is a new install (and I checked versions again).

One difference between this and the problem that the 1.57 version
fixed is that here the error is only on the second selectrow_array call.
The first call works fine.

Something is maybe changing the statement handle?

>
> If you are stuck on an old DBI version try something like this as
> a workaround:
>
> sub DBI::st::DELETE { my ($h, $key) = @_; delete $h->{$key} }

Well, that works...but explicity doing execute, fetchrow_array, and finish
works also. (BTW selectall_arrayref displays the same problem of course).

Re: Multiple selectrow_array calls with statement handle error

On Apr 10, 2:34 pm, pgodf...@gmail.com (Pgodfrin) wrote:
> On Apr 10, 1:15 pm, douglasg.wil...@gmail.com (Douglas Wilson) wrote:
>
>
>
> > To Christian Merz:
> > I'm executing this 1000's of times in the actual code, that is why I've
> > prepared the query rather than passing in the sql string every time.
>
> > For now I am just calling execute(), fetchrow_array(), and finish()
> > explicitly.
>
> > Below find the trace output:
>
> > On Thu, Apr 10, 2008 at 7:54 AM, Tim Bunce wrote:
>
> > > On Wed, Apr 09, 2008 at 04:29:48PM -0700, Douglas Wilson wrote:
>
> > with trace(9)
>
> > $dbh->trace(9);
>
> > > > my $total;
> > > > ( $total ) = $dbh->selectrow_array( $sth, undef, $id );
> > > > ( $total ) = $dbh->selectrow_array( $sth, undef, $id );
> > > A DBI trace should shed more light on it.
>
> > trace output:
> > DBI::db=HASH(0x401aae68) trace level set to 0x0/9 (DBI @ 0x0/0)
> > in DBI 1.602-nothread (pid 1812)
> > -> selectrow_array for DBD::Sybase::db
> > (DBI::db=HASH(0x401aaef8)~0x401aae68 DBI::st=HASH(0x401ab0d8) undef
> > 10600)
> > bind :p1 <== '10600' (attribs: )
> > bind :p1 () <== 10600 (size 5/8/0, ptype 6, otype 0)
> > bind :p1 <== '10600' (size 7, ok 1)
> > datafmt: type=8, name=, status=256, len=4
> > saved type: 8
> > cmd_execute() -> ct_send() OK
> > cmd_execute() -> set inUse flag
> > st_next_result() -> ct_results(4040) == 1
> > ct_res_info() returns 1 columns
> > STORE DBI::st=HASH(0x401ab0d8) 'NUM_OF_FIELDS' => 1
> > ct_describe(0): type = 10, maxlen = 8
> > describe() -> col 0, type 10, realtype 10
> > describe() retcode = 1
> > st_next_result() -> lasterr = 0, lastsev = 0
> > dbih_setup_fbav alloc for 1 fields
> > dbih_setup_fbav now 1 fields
> > syb_st_fetch() -> ct_fetch() = -204 (0 rows, 1 cols)
> > st_next_result() -> ct_results(4046) == 1
> > st_next_result() -> ct_results(4047) == 1
> > st_next_result() -> ct_results(4046) == 1
> > ct_results(4046) final retcode = -205
> > st_next_result() -> lasterr = 0, lastsev = 0
> > st_next_result() -> got CS_CMD_DONE: resetting ACTIVE,
> > moreResults, dyn_execed, exec_done
> > clear_sth_flags() -> resetting ACTIVE, moreResults, dyn_execed, exec_done
> > clear_sth_flags() -> reset inUse flag
> > syb_st_fetch() -> st_next_results() == 4046
> > clear_sth_flags() -> resetting ACTIVE, moreResults, dyn_execed, exec_done
> > clear_sth_flags() -> reset inUse flag
> > <- selectrow_array= ( ) [0 items] at ./tst line 22
> > -> selectrow_array for DBD::Sybase::db
> > (DBI::db=HASH(0x401aaef8)~0x401aae68 DBI::st=HASH(0x401ab0d8) undef
> > 10600)
> > bind :p1 <== '10600' (attribs: )
> > bind :p1 () <== 10600 (size 5/8/7, ptype 6, otype 0)
> > bind :p1 <== '10600' (size 7, ok 1)
> > datafmt: type=8, name=, status=256, len=4
> > saved type: 8
> > cmd_execute() -> ct_send() OK
> > cmd_execute() -> set inUse flag
> > st_next_result() -> ct_results(4040) == 1
> > Can't locate object method "DELETE" via package "DBI::st" at ./tst line 23.
> > ! -> DESTROY for DBD::Sybase::db (DBI::db=HASH(0x401aae68)~INNER)
> > syb_db_disconnect() -> ct_close()
> > ! <- DESTROY= undef during global destruction
>
> Two questions, do you have RaiseError set anywhere? (I see no explicit
> error checking)
>
> And - could there be only one row of data?
>
> phil

I'm a nitwit - sorry...
pg

Re: Multiple selectrow_array calls with statement handle error

On Thu, Apr 10, 2008 at 10:47:32PM -0800, Douglas Wilson wrote:
> On Thu, Apr 10, 2008 at 3:36 PM, Tim Bunce wrote:
> > On Thu, Apr 10, 2008 at 11:15:30AM -0700, Douglas Wilson wrote:
> >
> > Are you *certain* that that code is running with DBI 1.602?
> > Try adding a print of $DBI::VERSION.
>
> Yes, I am certain. This is a new install (and I checked versions again).

Did you build a fresh copy of DBD::Sybase after upgrading DBI?
To get this fix you'd need to do that.
Probably worth retrying, just to be sure.

Tim.

> One difference between this and the problem that the 1.57 version
> fixed is that here the error is only on the second selectrow_array call.
> The first call works fine.
>
> Something is maybe changing the statement handle?
>
> >
> > If you are stuck on an old DBI version try something like this as
> > a workaround:
> >
> > sub DBI::st::DELETE { my ($h, $key) = @_; delete $h->{$key} }
>
> Well, that works...but explicity doing execute, fetchrow_array, and finish
> works also. (BTW selectall_arrayref displays the same problem of course).

Re: Multiple selectrow_array calls with statement handle error

On Mon, Apr 14, 2008 at 3:28 AM, Tim Bunce wrote:
> Did you build a fresh copy of DBD::Sybase after upgrading DBI?
> To get this fix you'd need to do that.
> Probably worth retrying, just to be sure.

I didn't really "upgrade" DBI, as
it is a brand new install of perl (5.10.0), with all
new fresh compiled modules. I also have 5.6.1 installed,
but in a completely different location, so the 5.10 is
not looking at the 5.6.1 libraries.

BTW, the perl 5.6.1 selectrow_array works fine AFAICT, it's
using DBI 1.20 and DBD::Sybase 0.93.

Re: Multiple selectrow_array calls with statement handle error

Douglas Wilson wrote:
> With DBI 1.602 and DBD::Sybase 1.08 I get:
> Can't locate object method "DELETE" via package "DBI::st"
> on the second selectrow_array call.
>
> If I replace $sth with $sql in the selectrow_array calls, then it
> works correctly.
> I did find a similar problem here:
> http://www.nntp.perl.org/group/perl.dbi.users/2007/06/msg314 86.html
>
> but I thought that was fixed (did it get unfixed? :-)
> I get the same error whether or not I have placeholders and bind parameters.
>
> Here's the code:
>
> use DBI;
>
> my $dbh = DBI->connect(
> 'dbi:Sybase:server=SERVERNAME;database=dbname',
> 'user_name', 'password', {
> RaiseError => 1,
> });
> my $sql = 'select some_column from my_table where my_id = ?';
>
> my $sth = $dbh->prepare($sql);
> my $id = 10600;
>
> my $total;
> ( $total ) = $dbh->selectrow_array( $sth, undef, $id );
> ( $total ) = $dbh->selectrow_array( $sth, undef, $id );
>
>

I was able to reproduce it. I don't know yet if this is a DBD::Sybase
problem or something else. Unfortunately I don't have much time to debug
this.

Michael
--
Michael Peppler - Peppler Consulting SaRL
mpeppler@peppler.org - http://www.peppler.org
Sybase DBA/Developer - TeamSybase: http://www.teamsybase.com
Sybase on Linux FAQ - http://www.peppler.org/FAQ/linux.html

Re: Multiple selectrow_array calls with statement handle error

On Tue, Apr 15, 2008 at 09:00:56PM +0200, Michael Peppler wrote:
> Douglas Wilson wrote:
>> With DBI 1.602 and DBD::Sybase 1.08 I get:
>> Can't locate object method "DELETE" via package "DBI::st"
>> on the second selectrow_array call.
>>
>> ( $total ) = $dbh->selectrow_array( $sth, undef, $id );
>> ( $total ) = $dbh->selectrow_array( $sth, undef, $id );
>
> I was able to reproduce it.

Great.

> I don't know yet if this is a DBD::Sybase problem or something else.
> Unfortunately I don't have much time to debug this.

Try this (completely untested) patch to the DBI:

--- Driver.xst (revision 10993)
+++ Driver.xst (working copy)
@@ -157,7 +157,11 @@
AV *row_av;
PPCODE:
if (SvROK(ST(1))) {
- sth = ST(1);
+ MAGIC *mg;
+ sth = ST(1);
+ /* switch to inner handle if not already */
+ if ( (mg = mg_find(SvRV(sth),'P')) )
+ sth = mg->mg_obj;
}
else {
/* --- prepare --- */

(For a quick test it's probably easiest to apply it to the preprocessed
Driver.xsi in the DBD::Sybase directory after a 'make'.)

Tim.

Re: Multiple selectrow_array calls with statement handle error

On Tue, Apr 15, 2008 at 2:30 PM, Tim Bunce wrote:

>
> Try this (completely untested) patch to the DBI:
>
> --- Driver.xst (revision 10993)
> +++ Driver.xst (working copy)
> @@ -157,7 +157,11 @@
> AV *row_av;
> PPCODE:
> if (SvROK(ST(1))) {
> - sth = ST(1);
> + MAGIC *mg;
> + sth = ST(1);
> + /* switch to inner handle if not already */
> + if ( (mg = mg_find(SvRV(sth),'P')) )
> + sth = mg->mg_obj;
> }
> else {
> /* --- prepare --- */
>

That worked for me :-)

-Doug

Re: Multiple selectrow_array calls with statement handle error

On Tue, Apr 15, 2008 at 2:30 PM, Tim Bunce wrote:

> Try this (completely untested) patch to the DBI:

like I said...it works for me, but if that's the fix, then
you'll want to do selectall_arrayref also (same problem).

-Doug

Re: Multiple selectrow_array calls with statement handle error

On Wed, Apr 16, 2008 at 12:12:47PM -0700, Douglas Wilson wrote:
> On Tue, Apr 15, 2008 at 2:30 PM, Tim Bunce wrote:
>
> > Try this (completely untested) patch to the DBI:
>
> like I said...it works for me, but if that's the fix, then
> you'll want to do selectall_arrayref also (same problem).

Well spoted. Done. Thanks.

Tim.