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.
