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



On 1/23/07, Martin Evans <martin.evans@xxxxxxxxxxxx> 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 <martin.evans@xxxxxxxxxxxx> 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::<drivername>::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 <jonathan.leffler@xxxxxxxxx> #include <disclaimer.h>
Guardian of DBD::Informix - v2005.02 - http://dbi.perl.org
"I don't suffer from insanity - I enjoy every minute of it."


Relevant Pages

  • Re: most drivers share error variable for sth/dbh handles?
    ... > AutoCommit which requires extra statements to be executed to ... that is, the commit, unless the prepare or execute raised an error, in which case it would reflect that error. ... Even though sql has been executed 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. ... >> be great except for the fact we use multiple DBDs. ...
    (perl.dbi.users)
  • Re: INSERT statements not INSERTING when using mysql from python
    ... a PSP script that makes many calls to populate a database. ... Then I execute many insert statements in various different loops on ... It occured to me that if I could access the mysql query log that might ... it would appear to be some kind of autocommit problem. ...
    (comp.lang.python)
  • Re: Cross-database execution permissions with certificates and sch
    ... activated user cannot access objects in other schemas in this database. ... injection is defended against, however, the threat exists anywhere EXECUTE ... CREATE USER dispatcher ... the certificate is a trusted authenticator because you granted AUTHENTICATE ...
    (microsoft.public.sqlserver.security)
  • Re: Execute Persmission denied on object sp_OACreate
    ... SQL Server doesn't check permissions on indirectly referenced objects as ... You can prevent ad-hoc execution of powerful master database procs while ... >I have a user who has execute permissions on a store procedure in a>database> which in turns executes 4 stored procedures in the master database. ...
    (microsoft.public.sqlserver.security)
  • Re: SQLCE performance from .NET CF v2.0
    ... Please remember when bulk inserting is being executed against SQL CE, ... > local database functionality. ... > database technology and I am considering switching to SQLCE at the same ... > execute the prepared statement again. ...
    (microsoft.public.dotnet.framework.compactframework)