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/msg30761.html.

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

Relevant Pages

  • Re: $sth->{NAME} doesnt work
    ... > Tim Bunce wrote: ... Undef could mean any of those and it's not ... > need to execute() first. ... > Or consider porting a program from one DBD driver to another. ...
    (perl.dbi.users)
  • Re: How to call DBD::Oracles dbms_output_get from within a DBIx
    ... Tim Bunce wrote: ... which is usually (from applications) called like this: ... 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 ... Martin J. Evans ...
    (perl.dbi.users)
  • Re: what does $dbh->execute return for a select?
    ... On 19-May-2006 Tim Bunce wrote: ... I thought for a successful select, ... I've made that change but I still don't think execute is returning what the ... Martin J. Evans ...
    (perl.dbi.users)
  • RE: How to call DBD::Oracles dbms_output_get from within a DBIx
    ... Tim Bunce wrote: ... dbms_output_get does create a new statement, prepare and execute it ... A slightly more efficient (maybe less hackish? ... return $ret; ...
    (perl.dbi.users)
  • Re: DCL improvements
    ... > execute. ... Sometimes portions of commands or entire commands are ... but GOSUB may offer a similar possibility: ... The error handler decides whether to return or abort the procedure. ...
    (comp.os.vms)