Re: How to call DBD::Oracle's dbms_output_get from within a DBIx

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
.

Relevant Pages

  • Re: RDB limitations: from Jimmy Gavan
    ... Your DBI QUESTION: ... Bearing in mind no compiler to reference or even test my thoughts, ... The unique Id is used as a DB connection ... move SQLSTATE to MOST-SQLSTATE ...
    (comp.lang.cobol)
  • RE: :Oracle - with ora10g oci.dll
    ... I agree it's not a DBD or DBI error. ... your connection descriptor seems suspect: ... how to get to our nameservers. ... 10g installation is really just a few files in the /perl/bin directory. ...
    (perl.dbi.users)
  • Re: Mysql::Simple?
    ... GetOptions passthrough to interpret standard mysql options, ... it work with standard DBI environment variables, ... This way I find it easy to set up and wangle connection details. ... > Fetches the next row of data and returns a reference to an array holding the ...
    (comp.lang.perl.modules)
  • Re: DBI v2 - The Plan and How You Can Help
    ... $sth1.prepare; # always with connection, even if DBD doesn't use it $sth1.execute; # always with connection ... method the arguments you would have given to prepare, and you invoke prepareon the result with no arguments; each DBD would decide for itself how the work is divided between compileand preparewith the limitation that compileis not allowed to access the database; ideally the DBD would place as much work there as is possible, which would vary between Oracle/Pg/etc. ... designed to effectively support wrapper modules, the wrapper modules would also be altered from their current DBI-1-geared designs to accomodate DBI-2. ... module just because a new driver came into existence whose name has not yet been hard-coded into DBI. ...
    (perl.dbi.users)
  • Re: DBD::DB2 question need an example
    ... I've installed the latest Active state Perl, the latest DBI and DBD::DB2 ... but I can't make the connection work...... ... connecting to the database ...
    (perl.dbi.users)