How to call DBD::Oracle"s dbms_output_get from within a DBIx

Hi,

I have DBIx::Log4perl which is currently connected to a DBD::Oracle. In
DBIx::Log4perl::st::execute I want to call DBD::Oracle's dbms_get_line
which is usually (from applications) called like this:

@lines = $dbh->func('dbms_output_get');

As I'm in st::execute I have a $sth and can get hold of a $dbh. I
imagined all I needed to do was:

$sth::SUPER::dbms_output_get();

but I get

Deep recursion on subroutine "DBD::Oracle::db::dbms_output_get" at
/usr/lib/perl5/site_perl/5.8.8/DBIx/Log4perl/st.pm line 32.

When I look at dbms_output_get is see it is doing:

sub dbms_output_get {
my $dbh = shift;
my $sth = $dbh->prepare_cached("begin dbms_output.get_line(:l,
:s); end;
")
or return;
my ($line, $status, @lines);
# line can be greater that 255 (e.g. 7 byte date is expanded on
output)
$sth->bind_param_inout(':l', \$line, 400, { ora_type => 1 });
$sth->bind_param_inout(':s', \$status, 20, { ora_type => 1 });
if (!wantarray) {
$sth->execute or return undef;
return $line if $status eq '0';
return undef;
}
push @lines, $line while($sth->execute && $status eq '0');
return @lines;
}

Any ideas how I can call dbms_output_get from within
DBIx::Log4perl::st::execute? or is this impossible?

Thanks

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

Re: How to call DBD::Oracle"s dbms_output_get from within a DBIx

On Wed, Feb 21, 2007 at 04:25:54PM +0000, Martin J. Evans wrote:
> Hi,
>
> I have DBIx::Log4perl which is currently connected to a DBD::Oracle. In
> DBIx::Log4perl::st::execute I want to call DBD::Oracle's dbms_get_line
> which is usually (from applications) called like this:
>
> @lines = $dbh->func('dbms_output_get');
>
> As I'm in st::execute I have a $sth and can get hold of a $dbh.

I'd expect this to work:

$dbh = $sth->FETCH('Database');
@lines = $dbh->func('dbms_output_get');

Tim.

Re: How to call DBD::Oracle"s dbms_output_get from within a DBIx

Tim Bunce wrote:
> On Wed, Feb 21, 2007 at 04:25:54PM +0000, Martin J. Evans wrote:
>> Hi,
>>
>> I have DBIx::Log4perl which is currently connected to a DBD::Oracle. In
>> DBIx::Log4perl::st::execute I want to call DBD::Oracle's dbms_get_line
>> which is usually (from applications) called like this:
>>
>> @lines = $dbh->func('dbms_output_get');
>>
>> As I'm in st::execute I have a $sth and can get hold of a $dbh.
>
> I'd expect this to work:
>
> $dbh = $sth->FETCH('Database');
> @lines = $dbh->func('dbms_output_get');
>
> Tim.
>
>

Thanks Tim, but that does not appear to work. In my
DBIx::Log4perl::execute method I now have (simplified):

sub execute {
my ($sth, @args) = @_;

my $ret = $sth->SUPER::execute(@args);

my $dbh = $sth->FETCH('Database');
my @d = $dbh->func('dbms_output_get');

return $ret;
}

and I still get

Deep recursion on subroutine "DBD::Oracle::db::dbms_output_get"

dbms_output_get does create a new statement, prepare and execute it and
it appears when execute is called it ends up back in my
DBIx::Log4perl::execute method. I don't understand why this happens :-(

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

Re: How to call DBD::Oracle"s dbms_output_get from within a DBIx

On Thu, Feb 22, 2007 at 09:12:14AM +0000, Martin Evans wrote:
> Tim Bunce wrote:
> >On Wed, Feb 21, 2007 at 04:25:54PM +0000, Martin J. Evans wrote:
> >>Hi,
> >>
> >>I have DBIx::Log4perl which is currently connected to a DBD::Oracle. In
> >>DBIx::Log4perl::st::execute I want to call DBD::Oracle's dbms_get_line
> >>which is usually (from applications) called like this:
> >>
> >>@lines = $dbh->func('dbms_output_get');
> >>
> >>As I'm in st::execute I have a $sth and can get hold of a $dbh.
> >
> >I'd expect this to work:
> >
> > $dbh = $sth->FETCH('Database');
> > @lines = $dbh->func('dbms_output_get');
>
> Thanks Tim, but that does not appear to work. In my
> DBIx::Log4perl::execute method I now have (simplified):
>
> sub execute {
> my ($sth, @args) = @_;
>
> my $ret = $sth->SUPER::execute(@args);
>
> my $dbh = $sth->FETCH('Database');
> my @d = $dbh->func('dbms_output_get');
>
> return $ret;
> }
>
> and I still get
>
> Deep recursion on subroutine "DBD::Oracle::db::dbms_output_get"
>
> dbms_output_get does create a new statement, prepare and execute it and
> it appears when execute is called it ends up back in my
> DBIx::Log4perl::execute method. I don't understand why this happens :-(

Ah. Of course. Why would you except it not to happen? ;-)

dbms_output_get calls execute() on a statement handle created from your
subclassed dbh handle. So your DBIx::Log4perl::st::execute will be
called when dbms_output_get calls the execute method.

You need to either use a separate non-DBIx::Log4perl dbh for the
dbms_output_get call, or try something more hackish like

my @d = $dbh->func('dbms_output_get')
unless $sth->{Statement} =~ /^begin dbms_output.get_line/;

Tim.

DBI 1.54 RC8 Windows XP testing results

------=_NextPart_000_0A79_01C75658.2C820DE0
Content-Type: text/plain;
format=flowed;
charset="iso-8859-1";
reply-type=original
Content-Transfer-Encoding: 7bit

Ok I Compiled it again on a Windows bos asme as before and it was OK

Just for your info Tim I have attached the output of the 'nmake tes't from
the 85gofer till the end.

Cheers John Scoles


----- Original Message -----
From: "Tim Bunce"
To: "Martin J. Evans"
Cc:
Sent: Wednesday, February 21, 2007 8:50 PM
Subject: Re: How to call DBD::Oracle's dbms_output_get from within a DBIx


> On Wed, Feb 21, 2007 at 04:25:54PM +0000, Martin J. Evans wrote:
>> Hi,
>>
>> I have DBIx::Log4perl which is currently connected to a DBD::Oracle. In
>> DBIx::Log4perl::st::execute I want to call DBD::Oracle's dbms_get_line
>> which is usually (from applications) called like this:
>>
>> @lines = $dbh->func('dbms_output_get');
>>
>> As I'm in st::execute I have a $sth and can get hold of a $dbh.
>
> I'd expect this to work:
>
> $dbh = $sth->FETCH('Database');
> @lines = $dbh->func('dbms_output_get');
>
> Tim.
>

------=_NextPart_000_0A79_01C75658.2C820DE0
Content-Type: text/plain;
format=flowed;
name="dbi-1.54RC8-win-test.txt";
reply-type=original
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
filename="dbi-1.54RC8-win-test.txt"

t/85gofer.................ok
t/pod.....................skipped
all skipped: Test::Pod 1.00 required for testing POD
t/zvg_01basics............ok
4/132 skipped: developer tests
t/zvg_02dbidrv............ok
t/zvg_03handle............ok
44/137 skipped: various reasons
t/zvg_04mods..............ok
t/zvg_05thrclone.......... You can ignore the 10 'Scalars leaked' =
messages you may see her
e (or send me a patch to fix the underlying problem)
t/zvg_05thrclone..........ok 8/43Scalars leaked: 1
Scalars leaked: 1
Scalars leaked: 1
Scalars leaked: 1
Scalars leaked: 1
Scalars leaked: 1
Scalars leaked: 1
Scalars leaked: 1
Scalars leaked: 1
Scalars leaked: 1
t/zvg_05thrclone..........ok
t/zvg_06attrs.............ok
t/zvg_07kids..............ok
t/zvg_08keeperr...........ok
t/zvg_09trace.............ok
t/zvg_10examp.............ok
t/zvg_11fetch.............ok
t/zvg_12quote.............ok
t/zvg_13taint.............skipped
all skipped: Taint attribute tests not functional with =
DBI_AUTOPROXY
t/zvg_14utf8..............ok
t/zvg_15array.............ok
t/zvg_19fhtrace...........ok
t/zvg_20meta..............ok
t/zvg_30subclass..........ok
t/zvg_40profile...........ok
t/zvg_41prof_dump.........ok
t/zvg_42prof_data.........ok
t/zvg_43prof_env..........ok
t/zvg_50dbm...............ok
2/12 skipped: Can't set attributes after connect using =
DBD::Gofer
t/zvg_60preparse..........ok
t/zvg_65transact..........skipped
all skipped: Transactions not supported by DBD::Gofer
t/zvg_70callbacks.........ok
t/zvg_72childhandles......ok
2/14 skipped: slow tests avoided when using DBD::Gofer
t/zvg_80proxy.............ok
t/zvg_85gofer.............ok
t/zvp_01basics............ok
4/132 skipped: developer tests
t/zvp_02dbidrv............ok
10/51 skipped: various reasons
t/zvp_03handle............ok
76/137 skipped: various reasons
t/zvp_04mods..............ok
t/zvp_05thrclone..........ok
t/zvp_06attrs.............ok
7/137 skipped: various reasons
t/zvp_07kids..............skipped
all skipped: $h->{Kids} attribute not supported for =
DBI::PurePerl
t/zvp_08keeperr...........ok
t/zvp_09trace.............ok
t/zvp_10examp.............ok
3/204 skipped: inner/outer handles not fully supported for =
DBI::PurePerl
t/zvp_11fetch.............ok
t/zvp_12quote.............ok
t/zvp_13taint.............skipped
all skipped: Taint attributes not supported with DBI::PurePerl
t/zvp_14utf8..............ok
t/zvp_15array.............ok
t/zvp_19fhtrace...........ok
t/zvp_20meta..............ok
t/zvp_30subclass..........ok
t/zvp_40profile...........skipped
all skipped: profiling not supported for DBI::PurePerl
t/zvp_41prof_dump.........skipped
all skipped: profiling not supported for DBI::PurePerl
t/zvp_42prof_data.........skipped
all skipped: profiling not supported for DBI::PurePerl
t/zvp_43prof_env..........skipped
all skipped: profiling not supported for DBI::PurePerl
t/zvp_50dbm...............ok
t/zvp_60preparse..........skipped
all skipped: preparse not supported for DBI::PurePerl
t/zvp_65transact..........ok
t/zvp_70callbacks.........skipped
all skipped: $h->{Callbacks} attribute not supported for =
DBI::PurePerl
t/zvp_72childhandles......ok
t/zvp_80proxy.............skipped
all skipped: DBD::Proxy currently has a problem under =
DBI::PurePerl
t/zvp_85gofer.............ok
t/zvxgp_01basics..........ok
4/132 skipped: developer tests
t/zvxgp_02dbidrv..........ok
10/51 skipped: various reasons
t/zvxgp_03handle..........ok
76/137 skipped: various reasons
t/zvxgp_04mods............ok
t/zvxgp_05thrclone........ok
t/zvxgp_06attrs...........ok
7/137 skipped: various reasons
t/zvxgp_07kids............skipped
all skipped: $h->{Kids} attribute not supported for =
DBI::PurePerl
t/zvxgp_08keeperr.........ok
t/zvxgp_09trace...........ok
t/zvxgp_10examp...........ok
3/204 skipped: inner/outer handles not fully supported for =
DBI::PurePerl
t/zvxgp_11fetch...........ok
t/zvxgp_12quote...........ok
t/zvxgp_13taint...........skipped
all skipped: Taint attributes not supported with DBI::PurePerl
t/zvxgp_14utf8............ok
t/zvxgp_15array...........ok
t/zvxgp_19fhtrace.........ok
t/zvxgp_20meta............ok
t/zvxgp_30subclass........ok
t/zvxgp_40profile.........skipped
all skipped: profiling not supported for DBI::PurePerl
t/zvxgp_41prof_dump.......skipped
all skipped: profiling not supported for DBI::PurePerl
t/zvxgp_42prof_data.......skipped
all skipped: profiling not supported for DBI::PurePerl
t/zvxgp_43prof_env........skipped
all skipped: profiling not supported for DBI::PurePerl
t/zvxgp_50dbm.............ok
2/12 skipped: Can't set attributes after connect using =
DBD::Gofer
t/zvxgp_60preparse........skipped
all skipped: preparse not supported for DBI::PurePerl
t/zvxgp_65transact........skipped
all skipped: Transactions not supported by DBD::Gofer
t/zvxgp_70callbacks.......skipped
all skipped: $h->{Callbacks} attribute not supported for =
DBI::PurePerl
t/zvxgp_72childhandles....ok
2/14 skipped: slow tests avoided when using DBD::Gofer
t/zvxgp_80proxy...........skipped
all skipped: DBD::Proxy currently has a problem under =
DBI::PurePerl
t/zvxgp_85gofer...........ok
All tests successful, 22 tests and 260 subtests skipped.
Files=3D117, Tests=3D5134, 82 wallclock secs ( 0.00 cusr + 0.00 csys =
=3D 0.00 CPU)
C:\Perl\bin\perl.exe "-Iblib\lib" "-Iblib\arch" test.pl
test.pl
DBI test application $Revision: 8812 $
Switch: DBI 1.54 by Tim Bunce, 1.54
Available Drivers: DBM, ExampleP, File, Gofer, Multiplex, Oracle, Proxy, =
Sponge
dbi:ExampleP:: testing 5 sets of 20 connections:
Connecting... 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
Disconnecting...
Connecting... 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
Disconnecting...
Connecting... 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
Disconnecting...
Connecting... 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
Disconnecting...
Connecting... 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
Disconnecting...
Made 100 connections in 0 wallclock secs ( 0.01 usr + 0.00 sys =3D =
0.01 CPU)

Testing handle creation speed...
8888 NullP sth/s perl 5.008008 MSWin32-x86-multi-thread (cl 12.00.8804 =
-MD -Zi -DNDEBUG -
O1) 0.000113s


------=_NextPart_000_0A79_01C75658.2C820DE0--

RE: How to call DBD::Oracle"s dbms_output_get from within a DBIx

Tim Bunce wrote:
> On Thu, Feb 22, 2007 at 09:12:14AM +0000, Martin Evans wrote:
>> Tim Bunce wrote:
>>> On Wed, Feb 21, 2007 at 04:25:54PM +0000, Martin J. Evans wrote:
=09
>>=20
>> and I still get
>>=20
>> Deep recursion on subroutine "DBD::Oracle::db::dbms_output_get"
>>=20
>> dbms_output_get does create a new statement, prepare and execute it
>> and it appears when execute is called it ends up back in my
>> DBIx::Log4perl::execute method. I don't understand why this happens
>> :-(=20
>=20
> Ah. Of course. Why would you except it not to happen? ;-)
>=20
> dbms_output_get calls execute() on a statement handle created from
> your subclassed dbh handle. So your DBIx::Log4perl::st::execute will
> be called when dbms_output_get calls the execute method.
>=20
> You need to either use a separate non-DBIx::Log4perl dbh for the
> dbms_output_get call, or try something more hackish like
>=20
> my @d =3D $dbh->func('dbms_output_get')
> unless $sth->{Statement} =3D~ /^begin dbms_output.get_line/;

A slightly more efficient (maybe less hackish? ;-) way would be to use a
localized package variable as a semaphore:

sub execute {
my ($sth, @args) =3D @_;

my $ret =3D $sth->SUPER::execute(@args);

if (!$DBIx::Log4perl::st::DBMS_OUTPUT_GET) {
local $DBIx::Log4perl::st::DBMS_OUTPUT_GET =3D 1;
my $dbh =3D $sth->FETCH('Database');
my @d =3D $dbh->func('dbms_output_get');
}
return $ret;
}

- Philip

Re: How to call DBD::Oracle"s dbms_output_get from within a DBIx

Tim Bunce wrote:
> On Thu, Feb 22, 2007 at 09:12:14AM +0000, Martin Evans wrote:
>> Tim Bunce wrote:
>>> On Wed, Feb 21, 2007 at 04:25:54PM +0000, Martin J. Evans wrote:
>>>> Hi,
>>>>
>>>> I have DBIx::Log4perl which is currently connected to a DBD::Oracle. In
>>>> DBIx::Log4perl::st::execute I want to call DBD::Oracle's dbms_get_line
>>>> which is usually (from applications) called like this:
>>>>
>>>> @lines = $dbh->func('dbms_output_get');
>>>>
>>>> As I'm in st::execute I have a $sth and can get hold of a $dbh.
>>> I'd expect this to work:
>>>
>>> $dbh = $sth->FETCH('Database');
>>> @lines = $dbh->func('dbms_output_get');
>> Thanks Tim, but that does not appear to work. In my
>> DBIx::Log4perl::execute method I now have (simplified):
>>
>> sub execute {
>> my ($sth, @args) = @_;
>>
>> my $ret = $sth->SUPER::execute(@args);
>>
>> my $dbh = $sth->FETCH('Database');
>> my @d = $dbh->func('dbms_output_get');
>>
>> return $ret;
>> }
>>
>> and I still get
>>
>> Deep recursion on subroutine "DBD::Oracle::db::dbms_output_get"
>>
>> dbms_output_get does create a new statement, prepare and execute it and
>> it appears when execute is called it ends up back in my
>> DBIx::Log4perl::execute method. I don't understand why this happens :-(
>
> Ah. Of course. Why would you except it not to happen? ;-)

because I was not thinking straight.

> dbms_output_get calls execute() on a statement handle created from your
> subclassed dbh handle. So your DBIx::Log4perl::st::execute will be
> called when dbms_output_get calls the execute method.
>
> You need to either use a separate non-DBIx::Log4perl dbh for the
> dbms_output_get call, or try something more hackish like
>
> my @d = $dbh->func('dbms_output_get')
> unless $sth->{Statement} =~ /^begin dbms_output.get_line/;
>

Cheers, I took a variation on your second suggestion similar to what
Philip Garret put forward. This works but I have (hopefully) one related
last issue. In the following sequence the first statement handle loses
errstr and err values (see comments):

DBIx::Log4perl::st::execute {
my ($sth, @args) = @_;
my $h = $sth->{private_DBIx_Log4perl};

my $ret = $sth->SUPER::execute(@args);

# execute failed and an error handler was called
# $sth->errstr and $sth->err are both true and contain values

if (($h->{logmask} & DBIX_L4P_LOG_DBDSPECIFIC) &&
($h->{driver} eq 'Oracle') && (!$h->{dbd_specific})) {
$h->{dbd_specific} = 1;
my $dbh = $sth->FETCH('Database');

# The following call causes a new sth to be created in
# DBD::Oracle from a prepare_cached call and execute
# to be called multiple times to retrieve dbms_output
# The execute calls bring us back to this method but
# because of $h{dbd_specific} we don't get here again.

my @d = $dbh->func('dbms_output_get');
$sth->_dbix_l4p_debug('dbms', @d);
$h->{dbd_specific} = 0;

# Now $sth->errstr and $sth->err are undef - why?
# Why has creating a new statement and executing
# successfully on it changed errstr/err in another
# statement?
}
return $ret;
}

BTW, this is DBI 1.54rc8 (though using 1.53 makes no difference) and
DBD::Oracle 1.19 (with one tiny patch to execute_array I posted here
last week). This may also be slightly related to my posting "most
drivers share error variable for sth/dbh handles?"
http://www.nntp.perl.org/group/perl.dbi.users/2007/01/msg307 61.html.

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

Re: How to call DBD::Oracle"s dbms_output_get from within a DBIx

Martin Evans wrote:

> Cheers, I took a variation on your second suggestion similar to what
> Philip Garret put forward. This works but I have (hopefully) one related
> last issue. In the following sequence the first statement handle loses
> errstr and err values (see comments):
>
> DBIx::Log4perl::st::execute {
> my ($sth, @args) = @_;
> my $h = $sth->{private_DBIx_Log4perl};
>
> my $ret = $sth->SUPER::execute(@args);
>
> # execute failed and an error handler was called
> # $sth->errstr and $sth->err are both true and contain values
>
> if (($h->{logmask} & DBIX_L4P_LOG_DBDSPECIFIC) &&
> ($h->{driver} eq 'Oracle') && (!$h->{dbd_specific})) {
> $h->{dbd_specific} = 1;
> my $dbh = $sth->FETCH('Database');
>
> # The following call causes a new sth to be created in
> # DBD::Oracle from a prepare_cached call and execute
> # to be called multiple times to retrieve dbms_output
> # The execute calls bring us back to this method but
> # because of $h{dbd_specific} we don't get here again.
>
> my @d = $dbh->func('dbms_output_get');
> $sth->_dbix_l4p_debug('dbms', @d);
> $h->{dbd_specific} = 0;
>
> # Now $sth->errstr and $sth->err are undef - why?
> # Why has creating a new statement and executing
> # successfully on it changed errstr/err in another
> # statement?
> }
> return $ret;
> }
>
> BTW, this is DBI 1.54rc8 (though using 1.53 makes no difference) and
> DBD::Oracle 1.19 (with one tiny patch to execute_array I posted here
> last week). This may also be slightly related to my posting "most
> drivers share error variable for sth/dbh handles?"
> http://www.nntp.perl.org/group/perl.dbi.users/2007/01/msg307 61.html.
>
> Martin

It would appear the problem I'm seeing can be simplified to the
following (not using any DBIx):

use DBI qw(neat);
use strict;
use warnings;

my $h = DBI->connect("dbi:Oracle:XXX","xxx","yyy",
{RaiseError => 0, PrintError => 0});
$h->func('dbms_output_enable');
my $s1 = $h->prepare(q/update fred set fred = 'fred'/);
$s1->execute;
print "errstr:\n" . neat($s1->errstr) .
"\nerr:\n" . neat($s1->err) . "\n";
my $s2 = $h->prepare(q/begin
dbms_output.put_line('fred');
end;/);
$s2->execute;
print "errstr:\n" . neat($s1->errstr) .
"\nerr:\n" . neat($s1->err) . "\n";

which produces:

errstr:
'ORA-00942: table or view does not exist (DBD ERROR: error possibly near
<*> indicator at char 7 in 'update <*>fred set fred = 'fred'')'
err:
'942'
errstr:
undef
err:
undef

So, as you can see the $s1 has lost its errstr and err values. Any idea
why or where to look for this?

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

Re: How to call DBD::Oracle"s dbms_output_get from within a DBIx

On Thu, Feb 22, 2007 at 04:34:08PM +0000, Martin Evans wrote:
> Martin Evans wrote:
>
> It would appear the problem I'm seeing can be simplified to the
> following (not using any DBIx):
>
> use DBI qw(neat);
> use strict;
> use warnings;
>
> my $h = DBI->connect("dbi:Oracle:XXX","xxx","yyy",
> {RaiseError => 0, PrintError => 0});
> $h->func('dbms_output_enable');
> my $s1 = $h->prepare(q/update fred set fred = 'fred'/);
> $s1->execute;
> print "errstr:\n" . neat($s1->errstr) ."\nerr:\n" . neat($s1->err) . "\n";
> my $s2 = $h->prepare(q/begin dbms_output.put_line('fred'); end;/);
> $s2->execute;
> print "errstr:\n" . neat($s1->errstr) . "\nerr:\n" . neat($s1->err) . "\n";
>
> which produces:
>
> errstr:
> 'ORA-00942: table or view does not exist (DBD ERROR: error possibly near
> <*> indicator at char 7 in 'update <*>fred set fred = 'fred'')'
> err:
> '942'
> errstr:
> undef
> err:
> undef
>
> So, as you can see the $s1 has lost its errstr and err values. Any idea
> why or where to look for this?

By design the DBI clears the err/errstr/state values when it dispatches
most method calls.

FYI setting DBI trace level >=4 (or >=1 in 1.54) will show you the
current err/errstr and show you when they're cleared by the DBI.

The issue here is that $s1 and $s2 both share the same storage for the
err/errstr/state values (provided by and shared with the $dbh).
Most drivers are implemented that way.

Options:
1. Use a different $dbh.
2. Save the err/errstr/state values and restore them using set_err().
3. Perform some undocumented surgery on one of the handles to give it
separate storage for the err/errstr/state values.

Tim.

Re: How to call DBD::Oracle"s dbms_output_get from within a DBIx

On Thu, Feb 22, 2007 at 09:13:15AM -0500, Garrett, Philip (MAN-Corporate) wrote:
> Tim Bunce wrote:
> >
> > You need to either use a separate non-DBIx::Log4perl dbh for the
> > dbms_output_get call, or try something more hackish like
> >
> > my @d = $dbh->func('dbms_output_get')
> > unless $sth->{Statement} =~ /^begin dbms_output.get_line/;
>
> A slightly more efficient (maybe less hackish? ;-) way would be to use a
> localized package variable as a semaphore:

Much better, yes. Thanks.

Tim.

Re: How to call DBD::Oracle"s dbms_output_get from within a DBIx

Tim Bunce wrote:
> On Thu, Feb 22, 2007 at 04:34:08PM +0000, Martin Evans wrote:
>> Martin Evans wrote:
>>
>> It would appear the problem I'm seeing can be simplified to the
>> following (not using any DBIx):
>>
>> use DBI qw(neat);
>> use strict;
>> use warnings;
>>
>> my $h = DBI->connect("dbi:Oracle:XXX","xxx","yyy",
>> {RaiseError => 0, PrintError => 0});
>> $h->func('dbms_output_enable');
>> my $s1 = $h->prepare(q/update fred set fred = 'fred'/);
>> $s1->execute;
>> print "errstr:\n" . neat($s1->errstr) ."\nerr:\n" . neat($s1->err) . "\n";
>> my $s2 = $h->prepare(q/begin dbms_output.put_line('fred'); end;/);
>> $s2->execute;
>> print "errstr:\n" . neat($s1->errstr) . "\nerr:\n" . neat($s1->err) . "\n";
>>
>> which produces:
>>
>> errstr:
>> 'ORA-00942: table or view does not exist (DBD ERROR: error possibly near
>> <*> indicator at char 7 in 'update <*>fred set fred = 'fred'')'
>> err:
>> '942'
>> errstr:
>> undef
>> err:
>> undef
>>
>> So, as you can see the $s1 has lost its errstr and err values. Any idea
>> why or where to look for this?
>
> By design the DBI clears the err/errstr/state values when it dispatches
> most method calls.

ok, I doubt I'm going to get anywhere arguing with the "By design" but
I'll give it a go anyway. Comparing it with ODBC, a statement handle has
it's own errors separate from a connection handle. i.e. you can do:

sth1 = SQLAllocHandle(dbh);
sth2 = SQLAloocHandle(dbh);
SQLSomething(dbh) # errors
SQLGetDiagRec(dbh) # this returns the error SQLSomething produced
SQLSomethingElse(sth1) # errors
SQLGetDiagRec(sth1) # returns error on sth1
SQLSomethingElse(sth2) # errors
SQLGetDiagRec(sth2) # returns error on sth2
SQLGetDiagRec(dbh) # returns same error as above with SQLSomething
SQLGetDiagRec(sth1) # returns same error as above
SQLSomethingElse(sth1) # succeeds and clears previous error on sth1
SQLGetDiagRec(sth1) # returns no errors

How ODBC differs from DBI is that DBI appears to clear all errors in all
statements and the connection when any method is called. ODBC only
clears the errors on a handle, when /that/ handle is used again.

As another example the OCI interface to Oracle holds errors per
statement and I believe mysql, DB2 and TDS are similar in that respect.

Even in JDBC, you can get the errors at any time you like, because it
returns an sql exception object so the application owns it.

Now, even if you see my point and were inclined to be persuaded to
change the "By design" you're probably going to say you'll accept
patches ;-) That may be something I could do but I'm guessing it is
going to take some working out.

> FYI setting DBI trace level >=4 (or >=1 in 1.54) will show you the
> current err/errstr and show you when they're cleared by the DBI.

Thanks, I did look at these, I just didn't expect sth errors to be
cleared when another sth was used so I assumed it was a bug.

> The issue here is that $s1 and $s2 both share the same storage for the
> err/errstr/state values (provided by and shared with the $dbh).
> Most drivers are implemented that way.
>
> Options:
> 1. Use a different $dbh.

I don't think that is going to work out here as I'm not even sure the
dbms_output buffer isn't per connection.

> 2. Save the err/errstr/state values and restore them using set_err().

yes, I can do that but as set_err calls the HandleError routine I guess
it will have to be something like:

$s1->execute;
my ($errstr, $err, $state) = ($s1->errstr, $s1->err, $s1->state);
$s2->execute;
{
local $s1->{HandleError} = undef;
# or HandleError routine will get called twice
$s1->set_err($err, $errstr, $state);
}

> 3. Perform some undocumented surgery on one of the handles to give it
> separate storage for the err/errstr/state values.

If I knew how I might give it a go.

I guess 2 will do me for now.

Could I politely request that you consider keeping the handle errors
separate for DBI2.

As always, thanks for the help and insights. Always much appreciated.

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

How to prevent sth sharing error status with parent dbh and siblings

On Thu, Feb 22, 2007 at 06:11:38PM +0000, Martin Evans wrote:
> Tim Bunce wrote:
> >On Thu, Feb 22, 2007 at 04:34:08PM +0000, Martin Evans wrote:
> >>Martin Evans wrote:
> >>
> >>It would appear the problem I'm seeing can be simplified to the
> >>following (not using any DBIx):
> >>
> >>use DBI qw(neat);
> >>use strict;
> >>use warnings;
> >>
> >>my $h = DBI->connect("dbi:Oracle:XXX","xxx","yyy",
> >> {RaiseError => 0, PrintError => 0});
> >>$h->func('dbms_output_enable');
> >>my $s1 = $h->prepare(q/update fred set fred = 'fred'/);
> >>$s1->execute;
> >>print "errstr:\n" . neat($s1->errstr) ."\nerr:\n" . neat($s1->err) . "\n";
> >>my $s2 = $h->prepare(q/begin dbms_output.put_line('fred'); end;/);
> >>$s2->execute;
> >>print "errstr:\n" . neat($s1->errstr) . "\nerr:\n" . neat($s1->err) .
> >>"\n";
> >>
> >>which produces:
> >>
> >>errstr:
> >>'ORA-00942: table or view does not exist (DBD ERROR: error possibly near
> >><*> indicator at char 7 in 'update <*>fred set fred = 'fred'')'
> >>err:
> >>'942'
> >>errstr:
> >>undef
> >>err:
> >>undef
> >>
> >>So, as you can see the $s1 has lost its errstr and err values. Any idea
> >>why or where to look for this?
> >
> >By design the DBI clears the err/errstr/state values when it dispatches
> >most method calls.
>
> ok, I doubt I'm going to get anywhere arguing with the "By design" but
> I'll give it a go anyway. Comparing it with ODBC, a statement handle has
> it's own errors separate from a connection handle.

I didn't say it was right :) Many core parts of the DBI are over 10 years old
and there are pleanty of things I'd do differently now.

> How ODBC differs from DBI is that DBI appears to clear all errors in all
> statements and the connection when any method is called. ODBC only
> clears the errors on a handle, when /that/ handle is used again.

The DBI just clears the values for the handle being used. It's just
that, by default, the handles are sharing the same storage.

> Now, even if you see my point and were inclined to be persuaded to
> change the "By design" you're probably going to say you'll accept
> patches ;-) That may be something I could do but I'm guessing it is
> going to take some working out.

In this case it's up to the driver authors. They can arrange for handles
to have their own storage for err/errsr/state.

If you want to experiment, take a look at the driver's prepare method
where it calls DBI::_new_sth:

my $sth = DBI::_new_sth($dbh, {
Statement => $statement,
});

and add these lines:

my $sth = DBI::_new_sth($dbh, {
Statement => $statement,
Err => \my $err,
Errstr => \my $errstr,
State => \my $state,
});

Alternatively you could try doing the same thing after calling prepare:

$sth = $dbh->prepare(...);
$sth->{Err} = \my $err;
$sth->{Errstr} = \my $errstr;
$sth->{State} = \my $state;

but I've not tried that and there's a chance it won't work.

Also, be aware that there may be subtle issues where code expects the
dbh to reflect the error status of the last child sth call.

For example, when the $dbh->do() method is returning through the DBI
dispatcher with RaiseError/PrintError enabled, the DBI checks the $dbh
for an error. But for most drivers the error would not have happened on
the dbh itself, but on a child sth. In this case you might be okay
because the sth should have been DESTROYed by then and the DBI copies
up the error status from the sth to the dbh on DESTROY, but you're
relying on the timing of the DESTROY call - and I recall that some
(older) versions of perl could delay the call beyond do() returning.

That's just one example. There may be others.

> >Options:
>
> >3. Perform some undocumented surgery on one of the handles to give it
> > separate storage for the err/errstr/state values.
>
> If I knew how I might give it a go.

See above. I was saving the effort of explaining it until I was sure
you needed it :)

> Could I politely request that you consider keeping the handle errors
> separate for DBI2.

Sure. (The basic mantra for DBI2 will be 'do what JDBC does' :-)

Tim.

Re: How to prevent sth sharing error status with parent dbh and siblings

Tim Bunce wrote:
> On Thu, Feb 22, 2007 at 06:11:38PM +0000, Martin Evans wrote:
>> Tim Bunce wrote:
>>> On Thu, Feb 22, 2007 at 04:34:08PM +0000, Martin Evans wrote:
>>>> Martin Evans wrote:
>>>>
>>>> It would appear the problem I'm seeing can be simplified to the
>>>> following (not using any DBIx):
>>>>
>>>> use DBI qw(neat);
>>>> use strict;
>>>> use warnings;
>>>>
>>>> my $h = DBI->connect("dbi:Oracle:XXX","xxx","yyy",
>>>> {RaiseError => 0, PrintError => 0});
>>>> $h->func('dbms_output_enable');
>>>> my $s1 = $h->prepare(q/update fred set fred = 'fred'/);
>>>> $s1->execute;
>>>> print "errstr:\n" . neat($s1->errstr) ."\nerr:\n" . neat($s1->err) . "\n";
>>>> my $s2 = $h->prepare(q/begin dbms_output.put_line('fred'); end;/);
>>>> $s2->execute;
>>>> print "errstr:\n" . neat($s1->errstr) . "\nerr:\n" . neat($s1->err) .
>>>> "\n";
>>>>
>>>> which produces:
>>>>
>>>> errstr:
>>>> 'ORA-00942: table or view does not exist (DBD ERROR: error possibly near
>>>> <*> indicator at char 7 in 'update <*>fred set fred = 'fred'')'
>>>> err:
>>>> '942'
>>>> errstr:
>>>> undef
>>>> err:
>>>> undef
>>>>
>>>> So, as you can see the $s1 has lost its errstr and err values. Any idea
>>>> why or where to look for this?
>>> By design the DBI clears the err/errstr/state values when it dispatches
>>> most method calls.
>> ok, I doubt I'm going to get anywhere arguing with the "By design" but
>> I'll give it a go anyway. Comparing it with ODBC, a statement handle has
>> it's own errors separate from a connection handle.
>
> I didn't say it was right :) Many core parts of the DBI are over 10 years old
> and there are pleanty of things I'd do differently now.
>
>> How ODBC differs from DBI is that DBI appears to clear all errors in all
>> statements and the connection when any method is called. ODBC only
>> clears the errors on a handle, when /that/ handle is used again.
>
> The DBI just clears the values for the handle being used. It's just
> that, by default, the handles are sharing the same storage.
>
>> Now, even if you see my point and were inclined to be persuaded to
>> change the "By design" you're probably going to say you'll accept
>> patches ;-) That may be something I could do but I'm guessing it is
>> going to take some working out.
>
> In this case it's up to the driver authors. They can arrange for handles
> to have their own storage for err/errsr/state.
>
> If you want to experiment, take a look at the driver's prepare method
> where it calls DBI::_new_sth:
>
> my $sth = DBI::_new_sth($dbh, {
> Statement => $statement,
> });
>
> and add these lines:
>
> my $sth = DBI::_new_sth($dbh, {
> Statement => $statement,
> Err => \my $err,
> Errstr => \my $errstr,
> State => \my $state,
> });

As you point out later in your email, this could break do. Result of
DBD::Oracle test after that change was:

t/10general.............ok 1/33

# Failed test 'eval error: ``'' expected 'do failed:''
t/10general.............NOK 20/33# at t/10general.t line 82.


There were 3 others all generated from tests like this:

eval {
local $SIG{__WARN__} = sub { $warn = $_[0] };
$dbh->{RaiseError} = 1;
$dbh->do("some invalid sql statement");
};
ok($@ =~ /DBD::Oracle::db do failed:/, "eval error: ``$@'' expected
'do failed:'");

so it looks like the statement for the do has not been destroyed yet.

> Alternatively you could try doing the same thing after calling prepare:
>
> $sth = $dbh->prepare(...);
> $sth->{Err} = \my $err;
> $sth->{Errstr} = \my $errstr;
> $sth->{State} = \my $state;
>
> but I've not tried that and there's a chance it won't work.

It doesn't:

"Can't set DBI::st=HASH(0x8c78ffc)->{Err}: unrecognised attribute name
or invalid value" * 3

> Also, be aware that there may be subtle issues where code expects the
> dbh to reflect the error status of the last child sth call.
>
> For example, when the $dbh->do() method is returning through the DBI
> dispatcher with RaiseError/PrintError enabled, the DBI checks the $dbh
> for an error. But for most drivers the error would not have happened on
> the dbh itself, but on a child sth. In this case you might be okay
> because the sth should have been DESTROYed by then and the DBI copies
> up the error status from the sth to the dbh on DESTROY, but you're
> relying on the timing of the DESTROY call - and I recall that some
> (older) versions of perl could delay the call beyond do() returning.
>
> That's just one example. There may be others.

That is the only one I've found so far - with perl 5.8.8 and DBI 1.54
and DBD::Oracle 1.19.

>>> Options:
>>> 3. Perform some undocumented surgery on one of the handles to give it
>>> separate storage for the err/errstr/state values.
>> If I knew how I might give it a go.
>
> See above. I was saving the effort of explaining it until I was sure
> you needed it :)

The only method that reliably works now is one of your earlier
suggestions which is to store the values and use set_err to put them
back later (I disable HandleError/HandleSetError during the restore).

>> Could I politely request that you consider keeping the handle errors
>> separate for DBI2.
>
> Sure. (The basic mantra for DBI2 will be 'do what JDBC does' :-)
>
> Tim.
>
>

Thanks for all the help.

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

Re: How to prevent sth sharing error status with parent dbh and siblings

On Mon, Feb 26, 2007 at 10:29:37AM +0000, Martin Evans wrote:
> Tim Bunce wrote:
>
> >Alternatively you could try doing the same thing after calling prepare:
> >
> > $sth = $dbh->prepare(...);
> > $sth->{Err} = \my $err;
> > $sth->{Errstr} = \my $errstr;
> > $sth->{State} = \my $state;
> >
> >but I've not tried that and there's a chance it won't work.
>
> It doesn't:
>
> "Can't set DBI::st=HASH(0x8c78ffc)->{Err}: unrecognised attribute name or invalid value" * 3

You can get round that:

my $sth_inner = tied %$sth;
$sth_inner->{Err} = \my $err;
$sth_inner->{Errstr} = \my $errstr;
$sth_inner->{State} = \my $state;

but it's breaking encapsulation, so don't tell anyone I said so ;-)
And there's still a chance it won't work.

> >Also, be aware that there may be subtle issues where code expects the
> >dbh to reflect the error status of the last child sth call.
> >
> >For example, when the $dbh->do() method is returning through the DBI
> >dispatcher with RaiseError/PrintError enabled, the DBI checks the $dbh
> >for an error. But for most drivers the error would not have happened on
> >the dbh itself, but on a child sth. In this case you might be okay
> >because the sth should have been DESTROYed by then and the DBI copies
> >up the error status from the sth to the dbh on DESTROY, but you're
> >relying on the timing of the DESTROY call - and I recall that some
> >(older) versions of perl could delay the call beyond do() returning.
> >
> >That's just one example. There may be others.
>
> That is the only one I've found so far - with perl 5.8.8 and DBI 1.54
> and DBD::Oracle 1.19.

Perhaps a better approach would be to have set_err explicitly copy the
err/errstr/state to the parent handle. That should work for drivers
using set_err to record errors - which all should be by now.
(That's DBIh_SET_ERR_SV / DBIh_SET_ERR_CHAR for drivers written in C.)

> The only method that reliably works now is one of your earlier
> suggestions which is to store the values and use set_err to put them
> back later (I disable HandleError/HandleSetError during the restore).
>
> Thanks for all the help.

No problem Martin.

Tim.