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.
.

Relevant Pages

  • Re: How to prevent sth sharing error status with parent dbh and siblings
    ... dbh to reflect the error status of the last child sth call. ... when the $dbh->domethod is returning through the DBI ...
    (perl.dbi.users)
  • Re: DBI->state()
    ... there seems to be some confusion here. ... > Ultimately the only access to a handle's err, errstr, and state are ... > on the last handle the DBI used. ...
    (perl.dbi.users)
  • How to prevent sth sharing error status with parent dbh and siblings
    ... By design the DBI clears the err/errstr/state values when it dispatches ... dbh to reflect the error status of the last child sth call. ...
    (perl.dbi.users)
  • Re: DBI->state()
    ... I see the err() and errstr() are still ... I'm trying to check state info on a connection ... and they'll be removed in DBI v2. ...
    (perl.dbi.users)
  • Re: DBI->state()
    ... Ultimately the only access to a handle's err, errstr, and state are ... As a convienience the DBI also provides these *tied* variables ... Where the documentation refers to $h->err the $h means a *handle*. ...
    (perl.dbi.users)