Apache-LoggedAuthDBI
view release on metacpan or search on metacpan
for example:
$sth = $dbh->prepare("SELECT foo, bar FROM table WHERE baz=?");
$sth->execute( $baz );
while ( @row = $sth->fetchrow_array ) {
print "@row\n";
}
The typical method call sequence for a I<non>-C<SELECT> statement is:
prepare,
execute,
execute,
execute.
for example:
$sth = $dbh->prepare("INSERT INTO table(foo,bar,baz) VALUES (?,?,?)");
while(<CSV>) {
chomp;
my ($foo,$bar,$baz) = split /,/;
$sth->execute( $foo, $bar, $baz );
}
The C<do()> method can be used for non repeated I<non>-C<SELECT> statement
(or with drivers that don't support placeholders):
$rows_affected = $dbh->do("UPDATE your_table SET foo = foo + 1");
To commit your changes to the database (when L</AutoCommit> is off):
$dbh->commit; # or call $dbh->rollback; to undo changes
Finally, when you have finished working with the data source, you should
L</disconnect> from it:
$dbh->disconnect;
=head2 General Interface Rules & Caveats
The DBI does not have a concept of a "current session". Every session
has a handle object (i.e., a C<$dbh>) returned from the C<connect> method.
That handle object is used to invoke database related methods.
Most data is returned to the Perl script as strings. (Null values are
returned as C<undef>.) This allows arbitrary precision numeric data to be
handled without loss of accuracy. Beware that Perl may not preserve
the same accuracy when the string is used as a number.
Dates and times are returned as character strings in the current
default format of the corresponding database engine. Time zone effects
are database/driver dependent.
Perl supports binary data in Perl strings, and the DBI will pass binary
data to and from the driver without change. It is up to the driver
implementors to decide how they wish to handle such binary data.
Most databases that understand multiple character sets have a
default global charset. Text stored in the database is, or should
be, stored in that charset; if not, then that's the fault of either
the database or the application that inserted the data. When text is
fetched it should be automatically converted to the charset of the
client, presumably based on the locale. If a driver needs to set a
flag to get that behaviour, then it should do so; it should not require
the application to do that.
Multiple SQL statements may not be combined in a single statement
handle (C<$sth>), although some databases and drivers do support this
(notably Sybase and SQL Server).
Non-sequential record reads are not supported in this version of the DBI.
In other words, records can only be fetched in the order that the
database returned them, and once fetched they are forgotten.
Positioned updates and deletes are not directly supported by the DBI.
See the description of the C<CursorName> attribute for an alternative.
Individual driver implementors are free to provide any private
functions and/or handle attributes that they feel are useful.
Private driver functions can be invoked using the DBI C<func()> method.
Private driver attributes are accessed just like standard attributes.
Many methods have an optional C<\%attr> parameter which can be used to
pass information to the driver implementing the method. Except where
specifically documented, the C<\%attr> parameter can only be used to pass
driver specific hints. In general, you can ignore C<\%attr> parameters
or pass it as C<undef>.
=head2 Naming Conventions and Name Space
The DBI package and all packages below it (C<DBI::*>) are reserved for
use by the DBI. Extensions and related modules use the C<DBIx::>
namespace (see L<http://www.perl.com/CPAN/modules/by-module/DBIx/>).
Package names beginning with C<DBD::> are reserved for use
by DBI database drivers. All environment variables used by the DBI
or by individual DBDs begin with "C<DBI_>" or "C<DBD_>".
The letter case used for attribute names is significant and plays an
important part in the portability of DBI scripts. The case of the
attribute name is used to signify who defined the meaning of that name
and its values.
Case of name Has a meaning defined by
------------ ------------------------
UPPER_CASE Standards, e.g., X/Open, ISO SQL92 etc (portable)
MixedCase DBI API (portable), underscores are not used.
lower_case Driver or database engine specific (non-portable)
It is of the utmost importance that Driver developers only use
lowercase attribute names when defining private attributes. Private
attribute names must be prefixed with the driver name or suitable
abbreviation (e.g., "C<ora_>" for Oracle, "C<ing_>" for Ingres, etc).
=head2 SQL - A Query Language
... your code here ...
};
}
Using a C<my> inside a subroutine to store the previous C<HandleError>
value is important. See L<perlsub> and L<perlref> for more information
about I<closures>.
It is possible for C<HandleError> to alter the error message that
will be used by C<RaiseError> and C<PrintError> if it returns false.
It can do that by altering the value of $_[0]. This example appends
a stack trace to all errors and, unlike the previous example using
Carp::confess, this will work C<PrintError> as well as C<RaiseError>:
$h->{HandleError} = sub { $_[0]=Carp::longmess($_[0]); 0; };
It is also possible for C<HandleError> to hide an error, to a limited
degree, by using L</set_err> to reset $DBI::err and $DBI::errstr,
and altering the return value of the failed method. For example:
$h->{HandleError} = sub {
return 0 unless $_[0] =~ /^\S+ fetchrow_arrayref failed:/;
return 0 unless $_[1]->err == 1234; # the error to 'hide'
$h->set_err(undef,undef); # turn off the error
$_[2] = [ ... ]; # supply alternative return value
return 1;
};
This only works for methods which return a single value and is hard
to make reliable (avoiding infinite loops, for example) and so isn't
recommended for general use! If you find a I<good> use for it then
please let me know.
=item C<HandleSetErr> (code ref, inherited)
The C<HandleSetErr> attribute can be used to intercept
the setting of handle C<err>, C<errstr>, and C<state> values.
If set to a reference to a subroutine then that subroutine is called
whenever set_err() is called, typically by the driver or a subclass.
The subroutine is called with five arguments, the first five that
were passed to set_err(): the handle, the C<err>, C<errstr>, and
C<state> values being set, and the method name. These can be altered
by changing the values in the @_ array. The return value affects
set_err() behaviour, see L</set_err> for details.
It is possible to 'stack' multiple HandleSetErr handlers by using
closures. See L</HandleError> for an example.
The C<HandleSetErr> and C<HandleError> subroutines differ in subtle
but significant ways. HandleError is only invoked at the point where
the DBI is about to return to the application with C<err> set true.
It's not invoked by the failure of a method that's been called by
another DBI method. HandleSetErr, on the other hand, is called
whenever set_err() is called with a defined C<err> value, even if false.
So it's not just for errors, despite the name, but also warn and info states.
The set_err() method, and thus HandleSetErr, may be called multiple
times within a method and is usually invoked from deep within driver code.
In theory a driver can use the return value from HandleSetErr via
set_err() to decide whether to continue or not. If set_err() returns
an empty list, indicating that the HandleSetErr code has 'handled'
the 'error', the driver could then continue instead of failing (if
that's a reasonable thing to do). This isn't excepted to be
common and any such cases should be clearly marked in the driver
documentation and discussed on the dbi-dev mailing list.
The C<HandleSetErr> attribute was added in DBI 1.41.
=item C<ErrCount> (unsigned integer)
The C<ErrCount> attribute is incremented whenever the set_err()
method records an error. It isn't incremented by warnings or
information states. It is not reset by the DBI at any time.
The C<ErrCount> attribute was added in DBI 1.41. Older drivers may
not have been updated to use set_err() to record errors and so this
attribute may not be incremented when using them.
=item C<ShowErrorStatement> (boolean, inherited)
The C<ShowErrorStatement> attribute can be used to cause the relevant
Statement text to be appended to the error messages generated by
the C<RaiseError>, C<PrintError>, and C<PrintWarn> attributes.
Only applies to errors on statement handles
plus the prepare(), do(), and the various C<select*()> database handle methods.
(The exact format of the appended text is subject to change.)
If C<$h-E<gt>{ParamValues}> returns a hash reference of parameter
(placeholder) values then those are formatted and appended to the
end of the Statement text in the error message.
=item C<TraceLevel> (integer, inherited)
The C<TraceLevel> attribute can be used as an alternative to the
L</trace> method to set the DBI trace level and trace flags for a
specific handle. See L</TRACING> for more details.
The C<TraceLevel> attribute is especially useful combined with
C<local> to alter the trace settings for just a single block of code.
=item C<FetchHashKeyName> (string, inherited)
The C<FetchHashKeyName> attribute is used to specify whether the fetchrow_hashref()
method should perform case conversion on the field names used for
the hash keys. For historical reasons it defaults to 'C<NAME>' but
it is recommended to set it to 'C<NAME_lc>' (convert to lower case)
or 'C<NAME_uc>' (convert to upper case) according to your preference.
It can only be set for driver and database handles. For statement
handles the value is frozen when prepare() is called.
=item C<ChopBlanks> (boolean, inherited)
The C<ChopBlanks> attribute can be used to control the trimming of trailing space
characters from fixed width character (CHAR) fields. No other field
types are affected, even where field values have trailing spaces.
The default is false (although it is possible that the default may change).
Applications that need specific behaviour should set the attribute as
In the iThreads model each thread has it's own copy of the perl
interpreter. When a new thread is created the original perl
interpreter is 'cloned' to create a new copy for the new thread.
If the DBI and drivers are loaded and handles created before the
thread is created then it will get a cloned copy of the DBI, the
drivers and the handles.
However, the internal pointer data within the handles will refer
to the DBI and drivers in the original interpreter. Using those
handles in the new interpreter thread is not safe, so the DBI detects
this and croaks on any method call using handles that don't belong
to the current thread (except for DESTROY).
Because of this (possibly temporary) restriction, newly created
threads must make their own connctions to the database. Handles
can't be shared across threads.
But BEWARE, some underlying database APIs (the code the DBD driver
uses to talk to the database, often supplied by the database vendor)
are not thread safe. If it's not thread safe, then allowing more
than one thread to enter the code at the same time may cause
subtle/serious problems. In some cases allowing more than
one thread to enter the code, even if I<not> at the same time,
can cause problems. You have been warned.
Using DBI with perl threads is not yet recommended for production
environments. For more information see
L<http://www.perlmonks.org/index.pl?node_id=288022>
Note: There is a bug in perl 5.8.2 when configured with threads
and debugging enabled (bug #24463) which causes a DBI test to fail.
=head2 Signal Handling and Canceling Operations
[The following only applies to systems with unix-like signal handling.
I'd welcome additions for other systems, especially Windows.]
The first thing to say is that signal handling in Perl versions less
than 5.8 is I<not> safe. There is always a small risk of Perl
crashing and/or core dumping when, or after, handling a signal
because the signal could arrive and be handled while internal data
structures are being changed. If the signal handling code
used those same internal data structures it could cause all manner
of subtle and not-so-subtle problems. The risk was reduced with
5.4.4 but was still present in all perls up through 5.8.0.
Beginning in perl 5.8.0 perl implements 'safe' signal handling if
your system has the POSIX sigaction() routine. Now when a signal
is delivered perl just makes a note of it but does I<not> run the
%SIG handler. The handling is 'defered' until a 'safe' moment.
Although this change made signal handling safe, it also lead to
a problem with signals being defered for longer than you'd like.
If a signal arrived while executing a system call, such as waiting
for data on a network connection, the signal is noted and then the
system call that was executing returns with an EINTR error code
to indicate that it was interrupted. All fine so far.
The problem comes when the code that made the system call sees the
EINTR code and decides it's going to call it again. Perl doesn't
do that, but database code sometimes does. If that happens then the
signal handler doesn't get called untill later. Maybe much later.
Fortunately there are ways around this which we'll discuss below.
Unfortunately they make signals unsafe again.
The two most common uses of signals in relation to the DBI are for
canceling operations when the user types Ctrl-C (interrupt), and for
implementing a timeout using C<alarm()> and C<$SIG{ALRM}>.
=over 4
=item Cancel
The DBI provides a C<cancel> method for statement handles. The
C<cancel> method should abort the current operation and is designed
to be called from a signal handler. For example:
$SIG{INT} = sub { $sth->cancel };
However, few drivers implement this (the DBI provides a default
method that just returns C<undef>) and, even if implemented, there
is still a possibility that the statement handle, and even the
parent database handle, will not be usable afterwards.
If C<cancel> returns true, then it has successfully
invoked the database engine's own cancel function. If it returns false,
then C<cancel> failed. If it returns C<undef>, then the database
driver does not have cancel implemented.
=item Timeout
The traditional way to implement a timeout is to set C<$SIG{ALRM}>
to refer to some code that will be executed when an ALRM signal
arrives and then to call alarm($seconds) to schedule an ALRM signal
to be delivered $seconds in the future. For example:
eval {
local $SIG{ALRM} = sub { die "TIMEOUT\n" };
alarm($seconds);
... code to execute with timeout here ...
alarm(0); # cancel alarm (if code ran fast)
};
alarm(0); # cancel alarm (if eval failed)
if ( $@ eq "TIMEOUT" ) { ... }
Unfortunately, as described above, this won't always work as expected,
depending on your perl version and the underlying database code.
With Oracle for instance (DBD::Oracle), if the system which hosts
the database is down the DBI->connect() call will hang for several
minutes before returning an error.
=back
The solution on these systems is to use the C<POSIX::sigaction()>
routine to gain low level access to how the signal handler is installed.
The code would look something like this (for the DBD-Oracle connect()):
( run in 0.797 second using v1.01-cache-2.11-cpan-0bb4e1dffa6 )