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.e. you can do:
sth1 = SQLAllocHandle(dbh);
sth2 = SQLAloocHandle(dbh);
SQLSomething(dbh) # errors
SQLGetDiagRec(dbh) # this returns the error SQLSomething produced
SQLSomethingElse(sth1) # errors
SQLGetDiagRec(sth1) # returns error on sth1
SQLSomethingElse(sth2) # errors
SQLGetDiagRec(sth2) # returns error on sth2
SQLGetDiagRec(dbh) # returns same error as above with SQLSomething
SQLGetDiagRec(sth1) # returns same error as above
SQLSomethingElse(sth1) # succeeds and clears previous error on sth1
SQLGetDiagRec(sth1) # returns no errors
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.
As another example the OCI interface to Oracle holds errors per
statement and I believe mysql, DB2 and TDS are similar in that respect.
Even in JDBC, you can get the errors at any time you like, because it
returns an sql exception object so the application owns it.
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.
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.
Thanks, I did look at these, I just didn't expect sth errors to be
cleared when another sth was used so I assumed it was a bug.
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.
I don't think that is going to work out here as I'm not even sure the
dbms_output buffer isn't per connection.
2. Save the err/errstr/state values and restore them using set_err().
yes, I can do that but as set_err calls the HandleError routine I guess
it will have to be something like:
$s1->execute;
my ($errstr, $err, $state) = ($s1->errstr, $s1->err, $s1->state);
$s2->execute;
{
local $s1->{HandleError} = undef;
# or HandleError routine will get called twice
$s1->set_err($err, $errstr, $state);
}
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.
I guess 2 will do me for now.
Could I politely request that you consider keeping the handle errors
separate for DBI2.
As always, thanks for the help and insights. Always much appreciated.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
