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

Dear Monks,

I need some help with DBI:

My strategy is to use RaiseError=>1 and define a general die handler like this

use strict;
use DBI;
use Carp ('verbose');
...
my $dbh = DBI->connect( $dsn, $username, $password, $options )
  or die "cannot connect to '$dsn'\n";
$dbh->{HandleError} = sub {
    warn $DBI::errstr;
  # how to display the last statement ??
    Carp::confess
};

...

my $insstm = "insert into mytable values ( ?, ? )";
my $inssth = $dbh->prepare( $insstm );

for ( 1..3 ) {
  $inssth->execute( myseq.nextval, $_ );
}

[download]
First the confess messages are somehow shortened like this: 
main::__ANON__('DBD::Oracle::st execute failed: ORA-01722:
 UngM-|ltige Zahl (DBD E...', 'DBI::st=HASH(0x820cacc)', undef)
 called at insert-test.pl line 61

[download]
To facilitate debugging I need to see the actual statment executed. How can I achieve this?

Thanks very much for your time,
Axel.

Replies are listed 'Best First'.
Re: DBI HandleError statement display
by tommyw (Hermit) on Aug 07, 2002 at 09:58 UTC

    The DBI documentation includes $sth->Statement which will give you the text passed to prepare. If you can work out which statement handle to use :(

    Or there's the ShowErrorStatement attribute, which might do what you want, without needing a custom error handler:

    "ShowErrorStatement" (boolean, inherited) NEW
    This attribute can be used to cause the relevant Statement text to be appended to the error messages generated by the "RaiseError" and "PrintError" attributes. Only applies to errors on statement handles plus the prepare(), do(), and the various "select*()" database handle methods. (The exact format of the appended text is subject to change.)

    --
    Tommy
    Too stupid to live.
    Too stubborn to die.

[reply]
      Thanks for your reply, Tommy!

      I tried "$dbh->{ShowErrorStatement} =1" as well as "$inssth->{ShowErrorStaement = 1" without any visible change in the error message.

      The statement handles are indeed varying so I cannot use "$sth->Statement".

      Some improvement though gives: 
      $dbh->{HandleError} = sub {
  warn $DBI::lasth->{Statement};
  Carp::confess;
}

      [download]
      Now the error messages looks like: 
      insert into mytable values ( ?, ? )
main::__ANON__('DBD::Oracle::st execute failed: ORA-01722:
...

      [download]
      I found a message from Tim Bunce in perl.dbi.dev that he is planning an extension for showing the bound parameters as well. That means for the moment I'm stuck unless I give up the placeholders.
[reply]
[d/l]
[select]
        Tommy, have you tried using $dbh->trace? Not sure what your application is doing, but if you're trying to insert invalid data, hopefully that means that you just haven't beefed up your edits enough yet. Use $dbh->trace to help figure out what "bad" data you're trying to insert, then beef up your edits to prevent that data from making it that far. I could swear that I've used Carp's cluck before and gotten a meaningful stack trace that *did* show the placeholder values trying to get inserted(or updated, whatever).

        HTH.
[reply]
        $dbh->{ShowErrorStatement}=1 before the prepare() should certainly work. Please double check and post a trace() extract to dbi-users if you can't fix it. Placeholder values will be shown if ShowErrorStatement is true _and_ the driver supports the new ParamValues attribute (few do yet). Tim.
[reply]
Re: DBI HandleError statement display
by axelrose (Scribe) on Aug 09, 2002 at 09:03 UTC
    Tim helped again -- here is the solution: 
    my $options = { RaiseError => 1, PrintError =>1 };
my $dbh = DBI->connect( $dsn, $username $password, $options )
  or die;
$dbh->{ShowErrorStatement} = 1;
$dbh->{HandleError} = sub { Carp::Confess( $_[0] };

    [download]

    Carp::Confess( @_ ) gives warning within Carp itself ...

    Display of bind parameters is scheduled for the next release of DBD::Oracle. Tim published a patch in the dbi-users mailing list for DBD::Oracle version 1.12.

[reply]
[d/l]

Back to Seekers of Perl Wisdom