most drivers share error variable for sth/dbh handles?

From the DBI pod under "METHODS COMMON TO ALL HANDLES" for "err:

"The DBI resets $h->err to undef before almost all DBI method calls, so
the value only has a short lifespan. Also, for most drivers, the
statement handles share the same error variable as the parent database
handle, so calling a method on one handle may reset the error on the
related handles."

Given the "most drivers" above I presume some drivers don't share the
error variable for database and statement handles. Which are these
drivers? If you don't know of any, perhaps you can tell me how to find
out whether they do? I did find the following in DBI.pm:

sub _new_drh { # called by DBD::::driver()
my ($class, $initial_attr, $imp_data) = @_;
# Provide default storage for State,Err and Errstr.
# Note that these are shared by all child handles by default! XXX
# State must be undef to get automatic faking in DBI::var::FETCH
my ($h_state_store, $h_err_store, $h_errstr_store) = (undef, 0, '');

The reason I'd like to know is that I have some circumstances where an
error occurs on a statement handle which goes out of scope immediately
so err is not available. I notice the connection handle (with
DBD::Oracle) also contains the same error number/string and this would
be great except for the fact we use multiple DBDs.

Thanks.

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

Re: most drivers share error variable for sth/dbh handles?

------=_Part_162204_13341685.1169572882624
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

DBD::Informix is careful about errors.

Each statement handle keeps a copy of its most recent status/error
information out of the global sqlca variable (plus the sqlstate variable).
Each database handle has a copy of the most recently executed statement's
error/status information. Of course, this is made more complex by
AutoCommit which requires extra statements to be executed to simulate the
AutoCommit; you have to ignore the status of the extra statements when they
succeed, but record the error if they fail.

On 1/23/07, Martin Evans wrote:
>
> From the DBI pod under "METHODS COMMON TO ALL HANDLES" for "err:
>
> "The DBI resets $h->err to undef before almost all DBI method calls, so
> the value only has a short lifespan. Also, for most drivers, the
> statement handles share the same error variable as the parent database
> handle, so calling a method on one handle may reset the error on the
> related handles."
>
> Given the "most drivers" above I presume some drivers don't share the
> error variable for database and statement handles. Which are these
> drivers? If you don't know of any, perhaps you can tell me how to find
> out whether they do? I did find the following in DBI.pm:
>
> sub _new_drh { # called by DBD::::driver()
> my ($class, $initial_attr, $imp_data) = @_;
> # Provide default storage for State,Err and Errstr.
> # Note that these are shared by all child handles by default! XXX
> # State must be undef to get automatic faking in DBI::var::FETCH
> my ($h_state_store, $h_err_store, $h_errstr_store) = (undef, 0, '');
>
> The reason I'd like to know is that I have some circumstances where an
> error occurs on a statement handle which goes out of scope immediately
> so err is not available. I notice the connection handle (with
> DBD::Oracle) also contains the same error number/string and this would
> be great except for the fact we use multiple DBDs.
>
> Thanks.
>
> Martin
> --
> Martin J. Evans
> Easysoft Limited
> http://www.easysoft.com
>



--
Jonathan Leffler #include
Guardian of DBD::Informix - v2005.02 - http://dbi.perl.org
"I don't suffer from insanity - I enjoy every minute of it."

------=_Part_162204_13341685.1169572882624--

Re: most drivers share error variable for sth/dbh handles?

Thanks Jonathan,

Jonathan Leffler wrote:
> DBD::Informix is careful about errors.

I would hope all DBDs are ;-)

> Each statement handle keeps a copy of its most recent status/error
> information out of the global sqlca variable (plus the sqlstate variable).
> Each database handle has a copy of the most recently executed statement's
> error/status information. Of course, this is made more complex by
> AutoCommit which requires extra statements to be executed to simulate the
> AutoCommit; you have to ignore the status of the extra statements when they
> succeed, but record the error if they fail.

So, I think you are saying that if you executed the following with
DBD::Informix:

my $dbh = DBI->connect({RaiseError=>1});

eval {
$dbh->begin_work;
my $sth = $dbh->prepare(q/insert into table values (1)/);
$sth->execute; # execute fails - say duplicate key error
$dbh->commit;
};
$dbh->err here would be what $sth->err was above in the eval after the
execute (assuming you could have looked at $sth->err which you can't in
this case because RaiseError was set).

Yes?

I'm not actually using Informix (at the moment, Oracle, MySQL, DB2) but
we have spent a great deal of effort making sure we work with these 3
databases and don't want to rule out any others.

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

> On 1/23/07, Martin Evans wrote:
>>
>> From the DBI pod under "METHODS COMMON TO ALL HANDLES" for "err:
>>
>> "The DBI resets $h->err to undef before almost all DBI method calls, so
>> the value only has a short lifespan. Also, for most drivers, the
>> statement handles share the same error variable as the parent database
>> handle, so calling a method on one handle may reset the error on the
>> related handles."
>>
>> Given the "most drivers" above I presume some drivers don't share the
>> error variable for database and statement handles. Which are these
>> drivers? If you don't know of any, perhaps you can tell me how to find
>> out whether they do? I did find the following in DBI.pm:
>>
>> sub _new_drh { # called by DBD::::driver()
>> my ($class, $initial_attr, $imp_data) = @_;
>> # Provide default storage for State,Err and Errstr.
>> # Note that these are shared by all child handles by default! XXX
>> # State must be undef to get automatic faking in DBI::var::FETCH
>> my ($h_state_store, $h_err_store, $h_errstr_store) = (undef, 0, '');
>>
>> The reason I'd like to know is that I have some circumstances where an
>> error occurs on a statement handle which goes out of scope immediately
>> so err is not available. I notice the connection handle (with
>> DBD::Oracle) also contains the same error number/string and this would
>> be great except for the fact we use multiple DBDs.
>>
>> Thanks.
>>
>> Martin
>> --
>> Martin J. Evans
>> Easysoft Limited
>> http://www.easysoft.com
>>
>
>
>

Re: most drivers share error variable for sth/dbh handles?

------=_Part_162888_3678509.1169575842710
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 1/23/07, Martin Evans wrote:
>
> Thanks Jonathan,
>
> Jonathan Leffler wrote:
> > DBD::Informix is careful about errors.
>
> I would hope all DBDs are ;-)
>
> > Each statement handle keeps a copy of its most recent status/error
> > information out of the global sqlca variable (plus the sqlstate
> variable).
> > Each database handle has a copy of the most recently executed
> statement's
> > error/status information. Of course, this is made more complex by
> > AutoCommit which requires extra statements to be executed to simulate
> the
> > AutoCommit; you have to ignore the status of the extra statements when
> they
> > succeed, but record the error if they fail.



The 'you' in "you have to ignore" is 'the writer of DBD::Informix', not the
programmer using DBD::Informix. Sorry if that misled you.


So, I think you are saying that if you executed the following with
> DBD::Informix:
>
> my $dbh = DBI->connect({RaiseError=>1});
>
> eval {
> $dbh->begin_work;
> my $sth = $dbh->prepare(q/insert into table values (1)/);
> $sth->execute; # execute fails - say duplicate key error
> $dbh->commit;
> };
> $dbh->err here would be what $sth->err was above in the eval after the
> execute (assuming you could have looked at $sth->err which you can't in
> this case because RaiseError was set).
>


Yes?


No. $dbh->err would reflect the last statement executed on the $dbh - that
is, the commit, unless the prepare or execute (or begin_work) raised an
error, in which case it would reflect that error.

To demonstrate what I was referring to, consider this context:

my $st1 = $dbh->prepare("...");
my $st2 = $dbh->prepare("...");

$dbh->do('...');
# $dbh->err reflects the 'do'
$st1->execute;
# $st1->err reflects the execute; so does $dbh->err
$st2->execute;
# $st2->err reflects the second execute; so does $dbh->err; but $st1->err
hasn't changed.
$dbh->do('...');
# $dbh->err reflects the second 'do', but neither $st1->err nor $st2->err
has been affected.

The AutoCommit stuff I mentioned is related to the implicit begin work
before the statement and implicit commit work after the statement that
achieve the effect of AutoCommit in a database that doesn't auto-commit
anyway. (I'll go into the gory details if you want - but only after you've
read the DBD::Informix documentation and have questions arising.)


> On 1/23/07, Martin Evans wrote:
> >>
> >> From the DBI pod under "METHODS COMMON TO ALL HANDLES" for "err:
> >>
> >> "The DBI resets $h->err to undef before almost all DBI method calls, so
> >> the value only has a short lifespan. Also, for most drivers, the
> >> statement handles share the same error variable as the parent database
> >> handle, so calling a method on one handle may reset the error on the
> >> related handles."
> >>
> >> Given the "most drivers" above I presume some drivers don't share the
> >> error variable for database and statement handles. Which are these
> >> drivers? If you don't know of any, perhaps you can tell me how to find
> >> out whether they do? I did find the following in DBI.pm:
> >>
> >> sub _new_drh { # called by DBD::::driver()
> >> my ($class, $initial_attr, $imp_data) = @_;
> >> # Provide default storage for State,Err and Errstr.
> >> # Note that these are shared by all child handles by default! XXX
> >> # State must be undef to get automatic faking in DBI::var::FETCH
> >> my ($h_state_store, $h_err_store, $h_errstr_store) = (undef, 0,
> '');
> >>
> >> The reason I'd like to know is that I have some circumstances where an
> >> error occurs on a statement handle which goes out of scope immediately
> >> so err is not available. I notice the connection handle (with
> >> DBD::Oracle) also contains the same error number/string and this would
> >> be great except for the fact we use multiple DBDs.
>



--
Jonathan Leffler #include
Guardian of DBD::Informix - v2005.02 - http://dbi.perl.org
"I don't suffer from insanity - I enjoy every minute of it."

------=_Part_162888_3678509.1169575842710--

Re: most drivers share error variable for sth/dbh handles?

Jonathan Leffler wrote:
>
> On 1/23/07, *Martin Evans* > > wrote:
>
> Thanks Jonathan,
>
> Jonathan Leffler wrote:
> > DBD::Informix is careful about errors.
>
> I would hope all DBDs are ;-)
>
> > Each statement handle keeps a copy of its most recent status/error
> > information out of the global sqlca variable (plus the sqlstate
> variable).
> > Each database handle has a copy of the most recently executed
> statement's
> > error/status information. Of course, this is made more complex by
> > AutoCommit which requires extra statements to be executed to
> simulate the
> > AutoCommit; you have to ignore the status of the extra
> statements when they
> > succeed, but record the error if they fail.
>
>
>
> The 'you' in "you have to ignore" is 'the writer of DBD::Informix',
> not the programmer using DBD::Informix. Sorry if that misled you.
>
>
> So, I think you are saying that if you executed the following with
> DBD::Informix:
>
> my $dbh = DBI->connect({RaiseError=>1});
>
> eval {
> $dbh->begin_work;
> my $sth = $dbh->prepare(q/insert into table values (1)/);
> $sth->execute; # execute fails - say duplicate key error
> $dbh->commit;
> };
> $dbh->err here would be what $sth->err was above in the eval after the
> execute (assuming you could have looked at $sth->err which you
> can't in
> this case because RaiseError was set).
>
>
>
> Yes?
>
>
> No. $dbh->err would reflect the last statement executed on the $dbh -
> that is, the commit, unless the prepare or execute (or begin_work)
> raised an error, in which case it would reflect that error.
>
Even though you say "No" in fact your description says yes to me since
my example errored on execute (due to violation of primary key say) and
the commit never ran because RaiseError is set.
> To demonstrate what I was referring to, consider this context:
>
> my $st1 = $dbh->prepare("...");
> my $st2 = $dbh->prepare("...");
>
> $dbh->do('...');
> # $dbh->err reflects the 'do'
> $st1->execute;
> # $st1->err reflects the execute; so does $dbh->err
> $st2->execute;
> # $st2->err reflects the second execute; so does $dbh->err; but
> $st1->err hasn't changed.
> $dbh->do('...');
> # $dbh->err reflects the second 'do', but neither $st1->err nor
> $st2->err has been affected.
>
> The AutoCommit stuff I mentioned is related to the implicit begin work
> before the statement and implicit commit work after the statement that
> achieve the effect of AutoCommit in a database that doesn't
> auto-commit anyway. (I'll go into the gory details if you want - but
> only after you've read the DBD::Informix documentation and have
> questions arising.)
>
>
You have described exactly what I want. Even though sql has been
executed (and errored) on a statement handle (under a connection handle)
I no longer have access to the statement handle since it has gone out of
scope BUT I do have access to the connection handle and hope to see the
last error from the now out of scope statement in the connection handle.
i.e. if all drivers copy the last statement handle error into the
connection handle my problem is solved (I could not find this spelled
out in the DBI pod). It sounds as though DBD::Informix does and I know
from experimentation that DBD::Oracle does.

Thanks for the inside information from a DBD author.

Martin
>
> > On 1/23/07, Martin Evans < martin.evans@easysoft.com
> > wrote:
> >>
> >> From the DBI pod under "METHODS COMMON TO ALL HANDLES" for "err:
> >>
> >> "The DBI resets $h->err to undef before almost all DBI method
> calls, so
> >> the value only has a short lifespan. Also, for most drivers, the
> >> statement handles share the same error variable as the parent
> database
> >> handle, so calling a method on one handle may reset the error
> on the
> >> related handles."
> >>
> >> Given the "most drivers" above I presume some drivers don't
> share the
> >> error variable for database and statement handles. Which are these
> >> drivers? If you don't know of any, perhaps you can tell me how
> to find
> >> out whether they do? I did find the following in DBI.pm:
> >>
> >> sub _new_drh { # called by DBD::::driver()
> >> my ($class, $initial_attr, $imp_data) = @_;
> >> # Provide default storage for State,Err and Errstr.
> >> # Note that these are shared by all child handles by
> default! XXX
> >> # State must be undef to get automatic faking in
> DBI::var::FETCH
> >> my ($h_state_store, $h_err_store, $h_errstr_store) =
> (undef, 0, '');
> >>
> >> The reason I'd like to know is that I have some circumstances
> where an
> >> error occurs on a statement handle which goes out of scope
> immediately
> >> so err is not available. I notice the connection handle (with
> >> DBD::Oracle) also contains the same error number/string and
> this would
> >> be great except for the fact we use multiple DBDs.
>
>
>
>
> --
> Jonathan Leffler > > #include
> Guardian of DBD::Informix - v2005.02 - http://dbi.perl.org
> "I don't suffer from insanity - I enjoy every minute of it."

Re: most drivers share error variable for sth/dbh handles?

On Tue, Jan 23, 2007 at 11:28:42AM +0000, Martin Evans wrote:
> From the DBI pod under "METHODS COMMON TO ALL HANDLES" for "err:
>
> "The DBI resets $h->err to undef before almost all DBI method calls, so
> the value only has a short lifespan. Also, for most drivers, the
> statement handles share the same error variable as the parent database
> handle, so calling a method on one handle may reset the error on the
> related handles."
>
> Given the "most drivers" above I presume some drivers don't share the
> error variable for database and statement handles. Which are these
> drivers? If you don't know of any, perhaps you can tell me how to find
> out whether they do?

I can't dictate what driver authors do, I can just try to make it easy
to do the right thing. Of course 'the right thing' may change over time.

> I did find the following in DBI.pm:
>
> sub _new_drh { # called by DBD::::driver()
> my ($class, $initial_attr, $imp_data) = @_;
> # Provide default storage for State,Err and Errstr.
> # Note that these are shared by all child handles by default! XXX
> # State must be undef to get automatic faking in DBI::var::FETCH
> my ($h_state_store, $h_err_store, $h_errstr_store) = (undef, 0, '');

It used to be that those were shared by all dbh, but that got fixed
back in Feb 2003. The Changes file has this note for DBI 1.33:

Database handles no longer inherit shared $h->err/errstr/state storage
from their drivers, so each $dbh has it's own $h->err etc. values
and is no longer affected by calls made on other dbh's.
Now when a dbh is destroyed it's err/errstr/state values are copied
up to the driver so checking $DBI::errstr still works as expected.

FYI part of the fix is in _new_dbh, which provides new storage for the handle:

...
$attr->{Err} ||= \my $err;
$attr->{Errstr} ||= \my $errstr;
$attr->{State} ||= \my $state;
_new_handle($app_class, $drh, $attr, $imp_data, $imp_class);
...

> The reason I'd like to know is that I have some circumstances where an
> error occurs on a statement handle which goes out of scope immediately
> so err is not available. I notice the connection handle (with
> DBD::Oracle) also contains the same error number/string and this would
> be great except for the fact we use multiple DBDs.

That's the right thing to happen and I'd be very surprised if that
didn't happen for all DBDs. But of course I can't guarantee it.

Check that when the driver calls _new_dbh it's not passing in
Err/Errstr/State attributes. Or if it is, that they're private
ref and not shared by other dbh.

Tim.

Re: most drivers share error variable for sth/dbh handles?

Tim Bunce wrote:
> On Tue, Jan 23, 2007 at 11:28:42AM +0000, Martin Evans wrote:
>> From the DBI pod under "METHODS COMMON TO ALL HANDLES" for "err:
>>
>> "The DBI resets $h->err to undef before almost all DBI method calls, so
>> the value only has a short lifespan. Also, for most drivers, the
>> statement handles share the same error variable as the parent database
>> handle, so calling a method on one handle may reset the error on the
>> related handles."
>>
>> Given the "most drivers" above I presume some drivers don't share the
>> error variable for database and statement handles. Which are these
>> drivers? If you don't know of any, perhaps you can tell me how to find
>> out whether they do?
>
> I can't dictate what driver authors do, I can just try to make it easy
> to do the right thing. Of course 'the right thing' may change over time.
>
>> I did find the following in DBI.pm:
>>
>> sub _new_drh { # called by DBD::::driver()
>> my ($class, $initial_attr, $imp_data) = @_;
>> # Provide default storage for State,Err and Errstr.
>> # Note that these are shared by all child handles by default! XXX
>> # State must be undef to get automatic faking in DBI::var::FETCH
>> my ($h_state_store, $h_err_store, $h_errstr_store) = (undef, 0, '');
>
> It used to be that those were shared by all dbh, but that got fixed
> back in Feb 2003. The Changes file has this note for DBI 1.33:
>
> Database handles no longer inherit shared $h->err/errstr/state storage
> from their drivers, so each $dbh has it's own $h->err etc. values
> and is no longer affected by calls made on other dbh's.
> Now when a dbh is destroyed it's err/errstr/state values are copied
> up to the driver so checking $DBI::errstr still works as expected.
>
> FYI part of the fix is in _new_dbh, which provides new storage for the handle:
>
> ...
> $attr->{Err} ||= \my $err;
> $attr->{Errstr} ||= \my $errstr;
> $attr->{State} ||= \my $state;
> _new_handle($app_class, $drh, $attr, $imp_data, $imp_class);
> ...
>
>> The reason I'd like to know is that I have some circumstances where an
>> error occurs on a statement handle which goes out of scope immediately
>> so err is not available. I notice the connection handle (with
>> DBD::Oracle) also contains the same error number/string and this would
>> be great except for the fact we use multiple DBDs.
>
> That's the right thing to happen and I'd be very surprised if that
> didn't happen for all DBDs. But of course I can't guarantee it.

Good, that's just what I want to know. If that is the right thing to
happen I am happy to use that functionality and if I find a driver which
does not do it "right" it can be changed. What I didn't want to do is
rely on an undocumented feature or use a feature which is optional.

> Check that when the driver calls _new_dbh it's not passing in
> Err/Errstr/State attributes. Or if it is, that they're private
> ref and not shared by other dbh.
>
> Tim.

Thanks Tim.

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com