Apache-LoggedAuthDBI
view release on metacpan or search on metacpan
# Let drivers know why we are calling disconnect_all:
$DBI::PERL_ENDING = $DBI::PERL_ENDING = 1; # avoid typo warning
DBI->disconnect_all() if %DBI::installed_drh;
}
sub CLONE {
my $olddbis = $DBI::_dbistate;
_clone_dbis() unless $DBI::PurePerl; # clone the DBIS structure
DBI->trace_msg(sprintf "CLONE DBI for new thread %s\n",
$DBI::PurePerl ? "" : sprintf("(dbis %x -> %x)",$olddbis, $DBI::_dbistate));
while ( my ($driver, $drh) = each %DBI::installed_drh) {
no strict 'refs';
next if defined &{"DBD::${driver}::CLONE"};
warn("$driver has no driver CLONE() function so is unsafe threaded\n");
}
%DBI::installed_drh = (); # clear loaded drivers so they have a chance to reinitialize
}
sub parse_dsn {
my ($class, $dsn) = @_;
$dsn =~ s/^(dbi):(\w*?)(?:\((.*?)\))?://i or return;
my ($scheme, $driver, $attr, $attr_hash) = (lc($1), $2, $3);
$driver ||= $ENV{DBI_DRIVER} || '';
$attr_hash = { split /\s*=>?\s*|\s*,\s*/, $attr, -1 } if $attr;
return ($scheme, $driver, $attr, $attr_hash, $dsn);
}
# --- The DBI->connect Front Door methods
sub connect_cached {
# For library code using connect_cached() with mod_perl
# we redirect those calls to Apache::DBI::connect() as well
my ($class, $dsn, $user, $pass, $attr) = @_;
# XXX modifies callers data!
($attr ||= {})->{dbi_connect_method} =
($DBI::connect_via eq "Apache::DBI::connect")
? 'Apache::DBI::connect' : 'connect_cached';
return $class->connect($dsn, $user, $pass, $attr);
}
sub connect {
my $class = shift;
my ($dsn, $user, $pass, $attr, $old_driver) = my @orig_args = @_;
my $driver;
if ($attr and !ref($attr)) { # switch $old_driver<->$attr if called in old style
Carp::carp("DBI->connect using 'old-style' syntax is deprecated and will be an error in future versions");
($old_driver, $attr) = ($attr, $old_driver);
}
my $connect_meth = $attr->{dbi_connect_method};
$connect_meth ||= $DBI::connect_via; # fallback to default
$dsn ||= $ENV{DBI_DSN} || $ENV{DBI_DBNAME} || '' unless $old_driver;
if ($DBI::dbi_debug) {
local $^W = 0;
pop @_ if $connect_meth ne 'connect';
my @args = @_; $args[2] = '****'; # hide password
DBI->trace_msg(" -> $class->$connect_meth(".join(", ",@args).")\n");
}
Carp::croak('Usage: $class->connect([$dsn [,$user [,$passwd [,\%attr]]]])')
if (ref $old_driver or ($attr and not ref $attr) or ref $pass);
# extract dbi:driver prefix from $dsn into $1
$dsn =~ s/^dbi:(\w*?)(?:\((.*?)\))?://i
or '' =~ /()/; # ensure $1 etc are empty if match fails
my $driver_attrib_spec = $2 || '';
# Set $driver. Old style driver, if specified, overrides new dsn style.
$driver = $old_driver || $1 || $ENV{DBI_DRIVER}
or Carp::croak("Can't connect to data source $dsn, no database driver specified "
."and DBI_DSN env var not set");
if ($ENV{DBI_AUTOPROXY} && $driver ne 'Proxy' && $driver ne 'Sponge' && $driver ne 'Switch') {
my $dbi_autoproxy = $ENV{DBI_AUTOPROXY};
my $proxy = 'Proxy';
if ($dbi_autoproxy =~ s/^dbi:(\w*?)(?:\((.*?)\))?://i) {
$proxy = $1;
my $attr_spec = $2 || '';
$driver_attrib_spec = ($driver_attrib_spec) ? "$driver_attrib_spec,$attr_spec" : $attr_spec;
}
$dsn = "$dbi_autoproxy;dsn=dbi:$driver:$dsn";
$driver = $proxy;
DBI->trace_msg(" DBI_AUTOPROXY: dbi:$driver($driver_attrib_spec):$dsn\n");
}
my %attributes; # take a copy we can delete from
if ($old_driver) {
%attributes = %$attr if $attr;
}
else { # new-style connect so new default semantics
%attributes = (
PrintError => 1,
AutoCommit => 1,
ref $attr ? %$attr : (),
# attributes in DSN take precedence over \%attr connect parameter
$driver_attrib_spec ? (split /\s*=>?\s*|\s*,\s*/, $driver_attrib_spec, -1) : (),
);
}
$attr = \%attributes; # now set $attr to refer to our local copy
my $drh = $DBI::installed_drh{$driver} || $class->install_driver($driver)
or die "panic: $class->install_driver($driver) failed";
# attributes in DSN take precedence over \%attr connect parameter
$user = $attr->{Username} if defined $attr->{Username};
$pass = delete $attr->{Password} if defined $attr->{Password};
($user, $pass) = $drh->default_user($user, $pass, $attr)
if !(defined $user && defined $pass);
$attr->{Username} = $user; # store username as attribute
my $connect_closure = sub {
my ($old_dbh, $override_attr) = @_;
my $attr = {
# copy so we can edit them each time we're called
|<- Scope of DBI ->|
.-. .--------------. .-------------.
.-------. | |---| XYZ Driver |---| XYZ Engine |
| Perl | | | `--------------' `-------------'
| script| |A| |D| .--------------. .-------------.
| using |--|P|--|B|---|Oracle Driver |---|Oracle Engine|
| DBI | |I| |I| `--------------' `-------------'
| API | | |...
|methods| | |... Other drivers
`-------' | |...
`-'
The API, or Application Programming Interface, defines the
call interface and variables for Perl scripts to use. The API
is implemented by the Perl DBI extension.
The DBI "dispatches" the method calls to the appropriate driver for
actual execution. The DBI is also responsible for the dynamic loading
of drivers, error checking and handling, providing default
implementations for methods, and many other non-database specific duties.
Each driver
contains implementations of the DBI methods using the
private interface functions of the corresponding database engine. Only authors
of sophisticated/multi-database applications or generic library
functions need be concerned with drivers.
=head2 Notation and Conventions
The following conventions are used in this document:
$dbh Database handle object
$sth Statement handle object
$drh Driver handle object (rarely seen or used in applications)
$h Any of the handle types above ($dbh, $sth, or $drh)
$rc General Return Code (boolean: true=ok, false=error)
$rv General Return Value (typically an integer)
@ary List of values returned from the database, typically a row of data
$rows Number of rows processed (if available, else -1)
$fh A filehandle
undef NULL values are represented by undefined values in Perl
\%attr Reference to a hash of attribute values passed to methods
Note that Perl will automatically destroy database and statement handle objects
if all references to them are deleted.
=head2 Outline Usage
To use DBI,
first you need to load the DBI module:
use DBI;
use strict;
(The C<use strict;> isn't required but is strongly recommended.)
Then you need to L</connect> to your data source and get a I<handle> for that
connection:
$dbh = DBI->connect($dsn, $user, $password,
{ RaiseError => 1, AutoCommit => 0 });
Since connecting can be expensive, you generally just connect at the
start of your program and disconnect at the end.
Explicitly defining the required C<AutoCommit> behaviour is strongly
recommended and may become mandatory in a later version. This
determines whether changes are automatically committed to the
database when executed, or need to be explicitly committed later.
The DBI allows an application to "prepare" statements for later
execution. A prepared statement is identified by a statement handle
held in a Perl variable.
We'll call the Perl variable C<$sth> in our examples.
The typical method call sequence for a C<SELECT> statement is:
prepare,
execute, fetch, fetch, ...
execute, fetch, fetch, ...
execute, fetch, fetch, ...
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:
See L<perlop/"Quote and Quote-like Operators"> for more details.
See also the L</bind_columns> method, which is used to associate Perl
variables with the output columns of a C<SELECT> statement.
=head1 THE DBI PACKAGE AND CLASS
In this section, we cover the DBI class methods, utility functions,
and the dynamic attributes associated with generic DBI handles.
=head2 DBI Constants
Constants representing the values of the SQL standard types can be
imported individually by name, or all together by importing the
special C<:sql_types> tag.
The names and values of all the defined SQL standard types can be
produced like this:
foreach (@{ $DBI::EXPORT_TAGS{sql_types} }) {
printf "%s=%d\n", $_, &{"DBI::$_"};
}
These constants are defined by SQL/CLI, ODBC or both.
C<SQL_BIGINT> is (currently) omitted, because SQL/CLI and ODBC provide
conflicting codes.
See the L</type_info>, L</type_info_all>, and L</bind_param> methods
for possible uses.
Note that just because the DBI defines a named constant for a given
data type doesn't mean that drivers will support that data type.
=head2 DBI Class Methods
The following methods are provided by the DBI class:
=over 4
=item C<parse_dsn>
($scheme, $driver, $attr_string, $attr_hash, $driver_dsn) = DBI->parse_dsn($dsn)
or die "Can't parse DBI DSN '$dsn'";
Breaks apart a DBI Data Source Name (DSN) and returns the individual
parts. If $dsn doesn't contain a valid DSN then parse_dsn() returns
an empty list.
$scheme is the first part of the DSN and is currently always 'dbi'.
$driver is the driver name, possibly defaulted to $ENV{DBI_DRIVER},
and may be undefined. $attr_string is the optional attribute string,
which may be undefined. If $attr_string is true then $attr_hash
is a reference to a hash containing the parsed attribute names and
values. $driver_dsn is the last part of the DBI DSN string.
The parse_dsn() method was added in DBI 1.43.
=item C<connect>
$dbh = DBI->connect($data_source, $username, $password)
or die $DBI::errstr;
$dbh = DBI->connect($data_source, $username, $password, \%attr)
or die $DBI::errstr;
Establishes a database connection, or session, to the requested C<$data_source>.
Returns a database handle object if the connection succeeds. Use
C<$dbh-E<gt>disconnect> to terminate the connection.
If the connect fails (see below), it returns C<undef> and sets both C<$DBI::err>
and C<$DBI::errstr>. (It does I<not> explicitly set C<$!>.) You should generally
test the return status of C<connect> and C<print $DBI::errstr> if it has failed.
Multiple simultaneous connections to multiple databases through multiple
drivers can be made via the DBI. Simply make one C<connect> call for each
database and keep a copy of each returned database handle.
The C<$data_source> value must begin with "C<dbi:>I<driver_name>C<:>".
The I<driver_name> specifies the driver that will be used to make the
connection. (Letter case is significant.)
As a convenience, if the C<$data_source> parameter is undefined or empty,
the DBI will substitute the value of the environment variable C<DBI_DSN>.
If just the I<driver_name> part is empty (i.e., the C<$data_source>
prefix is "C<dbi::>"), the environment variable C<DBI_DRIVER> is
used. If neither variable is set, then C<connect> dies.
Examples of C<$data_source> values are:
dbi:DriverName:database_name
dbi:DriverName:database_name@hostname:port
dbi:DriverName:database=database_name;host=hostname;port=port
There is I<no standard> for the text following the driver name. Each
driver is free to use whatever syntax it wants. The only requirement the
DBI makes is that all the information is supplied in a single string.
You must consult the documentation for the drivers you are using for a
description of the syntax they require.
It is recommended that drivers support the ODBC style, shown in the
last example above. It is also recommended that that they support the
three common names 'C<host>', 'C<port>', and 'C<database>' (plus 'C<db>'
as an alias for C<database>). This simplifies automatic construction
of basic DSNs: C<"dbi:$driver:database=$db;host=$host;port=$port">.
Drivers should aim to 'do something reasonable' when given a DSN
in this form, but if any part is meaningless for that driver (such
as 'port' for Informix) it should generate an error if that part
is not empty.
If the environment variable C<DBI_AUTOPROXY> is defined (and the
driver in C<$data_source> is not "C<Proxy>") then the connect request
will automatically be changed to:
$ENV{DBI_AUTOPROXY};dsn=$data_source
C<DBI_AUTOPROXY> is typically set as "C<dbi:Proxy:hostname=...;port=...>".
If $ENV{DBI_AUTOPROXY} doesn't begin with 'C<dbi:>' then "dbi:Proxy:"
will be prepended to it first. See the DBD::Proxy documentation
for more details.
If C<$username> or C<$password> are undefined (rather than just empty),
then the DBI will substitute the values of the C<DBI_USER> and C<DBI_PASS>
environment variables, respectively. The DBI will warn if the
environment variables are not defined. However, the everyday use
of these environment variables is not recommended for security
reasons. The mechanism is primarily intended to simplify testing.
See below for alternative way to specify the username and password.
C<DBI-E<gt>connect> automatically installs the driver if it has not been
installed yet. Driver installation either returns a valid driver
handle, or it I<dies> with an error message that includes the string
"C<install_driver>" and the underlying problem. So C<DBI-E<gt>connect>
will die
on a driver installation failure and will only return C<undef> on a
connect failure, in which case C<$DBI::errstr> will hold the error message.
Use C<eval { ... }> if you need to catch the "C<install_driver>" error.
The C<$data_source> argument (with the "C<dbi:...:>" prefix removed) and the
C<$username> and C<$password> arguments are then passed to the driver for
processing. The DBI does not define any interpretation for the
contents of these fields. The driver is free to interpret the
C<$data_source>, C<$username>, and C<$password> fields in any way, and supply
whatever defaults are appropriate for the engine being accessed.
(Oracle, for example, uses the ORACLE_SID and TWO_TASK environment
variables if no C<$data_source> is specified.)
The C<AutoCommit> and C<PrintError> attributes for each connection
default to "on". (See L</AutoCommit> and L</PrintError> for more information.)
However, it is strongly recommended that you explicitly define C<AutoCommit>
rather than rely on the default. The C<PrintWarn> attribute defaults to
on if $^W is true, i.e., perl is running with warnings enabled.
The C<\%attr> parameter can be used to alter the default settings of
C<PrintError>, C<RaiseError>, C<AutoCommit>, and other attributes. For example:
$dbh = DBI->connect($data_source, $user, $pass, {
PrintError => 0,
AutoCommit => 0
});
The username and password can also be specified using the attributes
C<Username> and C<Password>, in which case they take precedence
over the C<$username> and C<$password> parameters.
You can also define connection attribute values within the C<$data_source>
parameter. For example:
dbi:DriverName(PrintWarn=>1,PrintError=>0,Taint=>1):...
Individual attributes values specified in this way take precedence over
any conflicting values specified via the C<\%attr> parameter to C<connect>.
The C<dbi_connect_method> attribute can be used to specify which driver
method should be called to establish the connection. The only useful
values are 'connect', 'connect_cached', or some specialized case like
'Apache::DBI::connect' (which is automatically the default when running
within Apache).
Where possible, each session (C<$dbh>) is independent from the transactions
in other sessions. This is useful when you need to hold cursors open
across transactions--for example, if you use one session for your long lifespan
cursors (typically read-only) and another for your short update
transactions.
For compatibility with old DBI scripts, the driver can be specified by
passing its name as the fourth argument to C<connect> (instead of C<\%attr>):
$dbh = DBI->connect($data_source, $user, $pass, $driver);
In this "old-style" form of C<connect>, the C<$data_source> should not start
with "C<dbi:driver_name:>". (If it does, the embedded driver_name
will be ignored). Also note that in this older form of C<connect>,
the C<$dbh-E<gt>{AutoCommit}> attribute is I<undefined>, the
C<$dbh-E<gt>{PrintError}> attribute is off, and the old C<DBI_DBNAME>
environment variable is
checked if C<DBI_DSN> is not defined. Beware that this "old-style"
C<connect> will soon be withdrawn in a future version of DBI.
=item C<connect_cached>
$dbh = DBI->connect_cached($data_source, $username, $password)
or die $DBI::errstr;
$dbh = DBI->connect_cached($data_source, $username, $password, \%attr)
or die $DBI::errstr;
C<connect_cached> is like L</connect>, except that the database handle
returned is also
stored in a hash associated with the given parameters. If another call
is made to C<connect_cached> with the same parameter values, then the
corresponding cached C<$dbh> will be returned if it is still valid.
The cached database handle is replaced with a new connection if it
has been disconnected or if the C<ping> method fails.
Note that the behaviour of this method differs in several respects from the
behaviour of persistent connections implemented by Apache::DBI.
Caching connections can be useful in some applications, but it can
also cause problems, such as too many connections, and so should
be used with care. In particular, avoid changing the attributes of
a database handle created via connect_cached() because it will affect
other code that may be using the same handle.
Where multiple separate parts of a program are using connect_cached()
to connect to the same database with the same (initial) attributes
it is a good idea to add a private attribute to the connect_cached()
call to effectively limit the scope of the caching. For example:
DBI->connect_cached(..., { private_foo_cachekey => "Bar", ... });
Handles returned from that connect_cached() call will only be returned
by other connect_cached() call elsewhere in the code if those other
calls also pass in the same attribute values, including the private one.
(I've used C<private_foo_cachekey> here as an example, you can use
any attribute name with a C<private_> prefix.)
Taking that one step further, you can limit a particular connect_cached()
call to return handles unique to that one place in the code by setting the
private attribute to a unique value for that place:
DBI->connect_cached(..., { private_foo_cachekey => __FILE__.__LINE__, ... });
By using a private attribute you still get connection caching for
the individual calls to connect_cached() but, by making separate
database conections for separate parts of the code, the database
handles are isolated from any attribute changes made to other handles.
The cache can be accessed (and cleared) via the L</CachedKids> attribute:
my $CachedKids_hashref = $dbh->{Driver}->{CachedKids};
%$CachedKids_hashref = () if $CachedKids_hashref;
=item C<available_drivers>
@ary = DBI->available_drivers;
@ary = DBI->available_drivers($quiet);
Returns a list of all available drivers by searching for C<DBD::*> modules
through the directories in C<@INC>. By default, a warning is given if
some drivers are hidden by others of the same name in earlier
directories. Passing a true value for C<$quiet> will inhibit the warning.
=item C<Taint> (boolean, inherited)
The C<Taint> attribute is a shortcut for L</TaintIn> and L</TaintOut> (it is also present
for backwards compatibility).
Setting this attribute sets both L</TaintIn> and L</TaintOut>, and retrieving
it returns a true value if and only if L</TaintIn> and L</TaintOut> are
both set to true values.
=item C<Profile> (inherited)
The C<Profile> attribute enables the collection and reporting of method call timing statistics.
See the L<DBI::Profile> module documentation for I<much> more detail.
The C<Profile> attribute was added in DBI 1.24.
=item C<private_your_module_name_*>
The DBI provides a way to store extra information in a DBI handle as
"private" attributes. The DBI will allow you to store and retrieve any
attribute which has a name starting with "C<private_>".
It is I<strongly> recommended that you use just I<one> private
attribute (e.g., use a hash ref) I<and> give it a long and unambiguous
name that includes the module or application name that the attribute
relates to (e.g., "C<private_YourFullModuleName_thingy>").
Because of the way the Perl tie mechanism works you cannot reliably
use the C<||=> operator directly to initialise the attribute, like this:
my $foo = $dbh->{private_yourmodname_foo} ||= { ... }; # WRONG
you should use a two step approach like this:
my $foo = $dbh->{private_yourmodname_foo};
$foo ||= $dbh->{private_yourmodname_foo} = { ... };
This attribute is primarily of interest to people sub-classing DBI.
=back
=head1 DBI DATABASE HANDLE OBJECTS
This section covers the methods and attributes associated with
database handles.
=head2 Database Handle Methods
The following methods are specified for DBI database handles:
=over 4
=item C<clone>
$new_dbh = $dbh->clone();
$new_dbh = $dbh->clone(\%attr);
The C<clone> method duplicates the $dbh connection by connecting
with the same parameters ($dsn, $user, $password) as originally used.
The attributes for the cloned connect are the same as those used
for the original connect, with some other attribute merged over
them depending on the \%attr parameter.
If \%attr is given then the attributes it contains are merged into
the original attributes and override any with the same names.
Effectively the same as doing:
%attribues_used = ( %original_attributes, %attr );
If \%attr is not given then it defaults to a hash containing all
the attributes in the attribute cache of $dbh excluding any non-code
references, plus the main boolean attributes (RaiseError, PrintError,
AutoCommit, etc.). This behaviour is subject to change.
The clone method can be used even if the database handle is disconnected.
The C<clone> method was added in DBI 1.33. It is very new and likely
to change.
=item C<data_sources>
@ary = $dbh->data_sources();
@ary = $dbh->data_sources(\%attr);
Returns a list of data sources (databases) available via the $dbh
driver's data_sources() method, plus any extra data sources that
the driver can discover via the connected $dbh. Typically the extra
data sources are other databases managed by the same server process
that the $dbh is connected to.
Data sources are returned in a form suitable for passing to the
L</connect> method (that is, they will include the "C<dbi:$driver:>" prefix).
The data_sources() method, for a $dbh, was added in DBI 1.38.
=item C<do>
$rows = $dbh->do($statement) or die $dbh->errstr;
$rows = $dbh->do($statement, \%attr) or die $dbh->errstr;
$rows = $dbh->do($statement, \%attr, @bind_values) or die ...
Prepare and execute a single statement. Returns the number of rows
affected or C<undef> on error. A return value of C<-1> means the
number of rows is not known, not applicable, or not available.
This method is typically most useful for I<non>-C<SELECT> statements that
either cannot be prepared in advance (due to a limitation of the
driver) or do not need to be executed repeatedly. It should not
be used for C<SELECT> statements because it does not return a statement
handle (so you can't fetch any data).
The default C<do> method is logically similar to:
sub do {
my($dbh, $statement, $attr, @bind_values) = @_;
my $sth = $dbh->prepare($statement, $attr) or return undef;
$sth->execute(@bind_values) or return undef;
my $rows = $sth->rows;
eval { $dbh->rollback };
# add other application on-error-clean-up code here
}
If the C<RaiseError> attribute is not set, then DBI calls would need to be
manually checked for errors, typically like this:
$h->method(@args) or die $h->errstr;
With C<RaiseError> set, the DBI will automatically C<die> if any DBI method
call on that handle (or a child handle) fails, so you don't have to
test the return value of each method call. See L</RaiseError> for more
details.
A major advantage of the C<eval> approach is that the transaction will be
properly rolled back if I<any> code (not just DBI calls) in the inner
application dies for any reason. The major advantage of using the
C<$h-E<gt>{RaiseError}> attribute is that all DBI calls will be checked
automatically. Both techniques are strongly recommended.
After calling C<commit> or C<rollback> many drivers will not let you
fetch from a previously active C<SELECT> statement handle that's a child
of the same database handle. A typical way round this is to connect the
the database twice and use one connection for C<SELECT> statements.
See L</AutoCommit> and L</disconnect> for other important information
about transactions.
=head2 Handling BLOB / LONG / Memo Fields
Many databases support "blob" (binary large objects), "long", or similar
datatypes for holding very long strings or large amounts of binary
data in a single field. Some databases support variable length long
values over 2,000,000,000 bytes in length.
Since values of that size can't usually be held in memory, and because
databases can't usually know in advance the length of the longest long
that will be returned from a C<SELECT> statement (unlike other data
types), some special handling is required.
In this situation, the value of the C<$h-E<gt>{LongReadLen}>
attribute is used to determine how much buffer space to allocate
when fetching such fields. The C<$h-E<gt>{LongTruncOk}> attribute
is used to determine how to behave if a fetched value can't fit
into the buffer.
See the description of L</LongReadLen> for more information.
When trying to insert long or binary values, placeholders should be used
since there are often limits on the maximum size of an C<INSERT>
statement and the L</quote> method generally can't cope with binary
data. See L</Placeholders and Bind Values>.
=head2 Simple Examples
Here's a complete example program to select and fetch some data:
my $data_source = "dbi::DriverName:db_name";
my $dbh = DBI->connect($data_source, $user, $password)
or die "Can't connect to $data_source: $DBI::errstr";
my $sth = $dbh->prepare( q{
SELECT name, phone
FROM mytelbook
}) or die "Can't prepare statement: $DBI::errstr";
my $rc = $sth->execute
or die "Can't execute statement: $DBI::errstr";
print "Query will return $sth->{NUM_OF_FIELDS} fields.\n\n";
print "Field names: @{ $sth->{NAME} }\n";
while (($name, $phone) = $sth->fetchrow_array) {
print "$name: $phone\n";
}
# check for problems which may have terminated the fetch early
die $sth->errstr if $sth->err;
$dbh->disconnect;
Here's a complete example program to insert some data from a file.
(This example uses C<RaiseError> to avoid needing to check each call).
my $dbh = DBI->connect("dbi:DriverName:db_name", $user, $password, {
RaiseError => 1, AutoCommit => 0
});
my $sth = $dbh->prepare( q{
INSERT INTO table (name, phone) VALUES (?, ?)
});
open FH, "<phone.csv" or die "Unable to open phone.csv: $!";
while (<FH>) {
chomp;
my ($name, $phone) = split /,/;
$sth->execute($name, $phone);
}
close FH;
$dbh->commit;
$dbh->disconnect;
Here's how to convert fetched NULLs (undefined values) into empty strings:
while($row = $sth->fetchrow_arrayref) {
# this is a fast and simple way to deal with nulls:
foreach (@$row) { $_ = '' unless defined }
print "@$row\n";
}
The C<q{...}> style quoting used in these examples avoids clashing with
quotes that may be used in the SQL statement. Use the double-quote like
C<qq{...}> operator if you want to interpolate variables into the string.
See L<perlop/"Quote and Quote-like Operators"> for more details.
=head2 Threads and Thread Safety
Perl 5.7 and later support a new threading model called iThreads.
(The old "5.005 style" threads are not supported by the DBI.)
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,
Further calls to trace() without a $trace_filename do not alter where
the trace output is sent. If $trace_filename is undefined, then
trace output is sent to C<STDERR> and the previous trace file is closed.
Currently $trace_filename can't be a filehandle. But meanwhile you
can use the special strings C<"STDERR"> and C<"STDOUT"> to select
those filehandles.
=head2 Tracing Tips
You can add tracing to your own application code using the
L</trace_msg> method.
It can sometimes be handy to compare trace files from two different
runs of the same script. However using a tool like C<diff> doesn't work
well because the trace file is full of object addresses that may
differ each run. Here's a handy little command to strip those out:
perl -pe 's/\b0x[\da-f]{6,}/0xNNNN/gi; s/\b[\da-f]{6,}/<long number>/gi'
=head1 DBI ENVIRONMENT VARIABLES
The DBI module recognizes a number of environment variables, but most of
them should not be used most of the time.
It is better to be explicit about what you are doing to avoid the need
for environment variables, especially in a web serving system where web
servers are stingy about which environment variables are available.
=head2 DBI_DSN
The DBI_DSN environment variable is used by DBI->connect if you do not
specify a data source when you issue the connect.
It should have a format such as "dbi:Driver:databasename".
=head2 DBI_DRIVER
The DBI_DRIVER environment variable is used to fill in the database
driver name in DBI->connect if the data source string starts "dbi::"
(thereby omitting the driver).
If DBI_DSN omits the driver name, DBI_DRIVER can fill the gap.
=head2 DBI_AUTOPROXY
The DBI_AUTOPROXY environment variable takes a string value that starts
"dbi:Proxy:" and is typically followed by "hostname=...;port=...".
It is used to alter the behaviour of DBI->connect.
For full details, see DBI::Proxy documentation.
=head2 DBI_USER
The DBI_USER environment variable takes a string value that is used as
the user name if the DBI->connect call is given undef (as distinct from
an empty string) as the username argument.
Be wary of the security implications of using this.
=head2 DBI_PASS
The DBI_PASS environment variable takes a string value that is used as
the password if the DBI->connect call is given undef (as distinct from
an empty string) as the password argument.
Be extra wary of the security implications of using this.
=head2 DBI_DBNAME (obsolete)
The DBI_DBNAME environment variable takes a string value that is used only when the
obsolescent style of DBI->connect (with driver name as fourth parameter) is used, and
when no value is provided for the first (database name) argument.
=head2 DBI_TRACE
The DBI_TRACE environment variable specifies the global default
trace settings for the DBI at startup. Can also be used to direct
trace output to a file. When the DBI is loaded it does:
DBI->trace(split /=/, $ENV{DBI_TRACE}, 2) if $ENV{DBI_TRACE};
So if C<DBI_TRACE> contains an "C<=>" character then what follows
it is used as the name of the file to append the trace to.
output appended to that file. If the name begins with a number
followed by an equal sign (C<=>), then the number and the equal sign are
stripped off from the name, and the number is used to set the trace
level. For example:
DBI_TRACE=1=dbitrace.log perl your_test_script.pl
On Unix-like systems using a Bourne-like shell, you can do this easily
on the command line:
DBI_TRACE=2 perl your_test_script.pl
See L</TRACING> for more information.
=head2 PERL_DBI_DEBUG (obsolete)
An old variable that should no longer be used; equivalent to DBI_TRACE.
=head2 DBI_PROFILE
The DBI_PROFILE environment variable can be used to enable profiling
of DBI method calls. See <DBI::Profile> for more information.
=head2 DBI_PUREPERL
The DBI_PUREPERL environment variable can be used to enable the
use of DBI::PurePerl. See <DBI::PurePerl> for more information.
=head1 WARNING AND ERROR MESSAGES
=head2 Fatal Errors
=over 4
=item Can't call method "prepare" without a package or object reference
The C<$dbh> handle you're using to call C<prepare> is probably undefined because
the preceding C<connect> failed. You should always check the return status of
DBI methods, or use the L</RaiseError> attribute.
=item Can't call method "execute" without a package or object reference
please consider sponsoring its development. Work is performed
rapidly, and usually on a fixed-price payment-on-delivery basis.
Contact me for details.
Using such targeted financing allows you to contribute to DBI
development, and rapidly get something specific and valuable in return.
=head1 ACKNOWLEDGEMENTS
I would like to acknowledge the valuable contributions of the many
people I have worked with on the DBI project, especially in the early
years (1992-1994). In no particular order: Kevin Stock, Buzz Moschetti,
Kurt Andersen, Ted Lemon, William Hails, Garth Kennedy, Michael Peppler,
Neil S. Briscoe, Jeff Urlwin, David J. Hughes, Jeff Stander,
Forrest D Whitcher, Larry Wall, Jeff Fried, Roy Johnson, Paul Hudson,
Georg Rehfeld, Steve Sizemore, Ron Pool, Jon Meek, Tom Christiansen,
Steve Baumgarten, Randal Schwartz, and a whole lot more.
Then, of course, there are the poor souls who have struggled through
untold and undocumented obstacles to actually implement DBI drivers.
Among their ranks are Jochen Wiedmann, Alligator Descartes, Jonathan
Leffler, Jeff Urlwin, Michael Peppler, Henrik Tougaard, Edwin Pratomo,
Davide Migliavacca, Jan Pazdziora, Peter Haworth, Edmund Mergl, Steve
Williams, Thomas Lowery, and Phlip Plumlee. Without them, the DBI would
not be the practical reality it is today. I'm also especially grateful
to Alligator Descartes for starting work on the first edition of the
"Programming the Perl DBI" book and letting me jump on board.
The DBI and DBD::Oracle were originally developed while I was Technical
Director (CTO) of the Paul Ingram Group (www.ig.co.uk). So I'd
especially like to thank Paul for his generosity and vision in
supporting this work for many years.
=head1 CONTRIBUTING
As you can see above, many people have contributed to the DBI and
drivers in many ways over many years.
If you'd like to help then see L<http://dbi.perl.org/contributing>
and L<http://search.cpan.org/~timb/DBI/Roadmap.pod>
If you'd like the DBI to do something new or different then a good way
to make that happen is to do it yourself and send me a patch to the
source code that shows the changes. (But read "Speak before you patch"
below.)
=head2 Browsing the source code repository
Use http://svn.perl.org/modules/dbi/trunk (basic)
or http://svn.perl.org/viewcvs/modules/ (more useful)
=head2 How to create a patch using Subversion
The DBI source code is maintained using Subversion (a replacement
for CVS, see L<http://subversion.tigris.org/>). To access the source
you'll need to install a Subversion client. Then, to get the source
code, do:
svn checkout http://svn.perl.org/modules/dbi/trunk
If it prompts for a username and password use your perl.org account
if you have one, else just 'guest' and 'guest'. The source code will
be in a new subdirectory called C<trunk>.
To keep informed about changes to the source you can send an empty email
to dbi-changes@perl.org after which you'll get an email with the
change log message and diff of each change checked-in to the source.
After making your changes you can generate a patch file, but before
you do, make sure your source is still upto date using:
svn update
If you get any conflicts reported you'll need to fix them first.
Then generate the patch file from within the C<trunk> directory using:
svn diff > foo.patch
Read the patch file, as a sanity check, and then email it to dbi-dev@perl.org.
=head2 How to create a patch without Subversion
Unpack a fresh copy of the distribution:
tar xfz DBI-1.40.tar.gz
Rename the newly created top level directory:
mv DBI-1.40 DBI-1.40.your_foo
Edit the contents of DBI-1.40.your_foo/* till it does what you want.
Test your changes and then remove all temporary files:
make test && make distclean
Go back to the directory you originally unpacked the distribution:
cd ..
Unpack I<another> copy of the original distribution you started with:
tar xfz DBI-1.40.tar.gz
Then create a patch file by performing a recursive C<diff> on the two
top level directories:
diff -r -u DBI-1.40 DBI-1.40.your_foo > DBI-1.40.your_foo.patch
=head2 Speak before you patch
For anything non-trivial or possibly controversial it's a good idea
to discuss (on dbi-dev@perl.org) the changes you propose before
actually spending time working on them. Otherwise you run the risk
of them being rejected because they don't fit into some larger plans
you may not be aware of.
=head1 TRANSLATIONS
A German translation of this manual (possibly slightly out of date) is
available, thanks to O'Reilly, at:
( run in 0.601 second using v1.01-cache-2.11-cpan-0bb4e1dffa6 )