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
