NAME
DBI - Database independent interface for Perl
SYNOPSIS
use DBI;
@driver_names = DBI->available_drivers;
@data_sources = DBI->data_sources($driver_name, \%attr);
$dbh = DBI->connect($data_source, $username, $auth, \%attr);
$rv = $dbh->do($statement);
$rv = $dbh->do($statement, \%attr);
$rv = $dbh->do($statement, \%attr, @bind_values);
$ary_ref = $dbh->selectall_arrayref($statement);
$hash_ref = $dbh->selectall_hashref($statement, $key_field);
$ary_ref = $dbh->selectcol_arrayref($statement);
$ary_ref = $dbh->selectcol_arrayref($statement, \%attr);
$ary_ref = $dbh->selectrow_arrayref($statement);
@row_ary = $dbh->selectrow_array($statement);
$hash_ref = $dbh->selectrow_hashref($statement);
$sth = $dbh->prepare($statement);
$sth = $dbh->prepare_cached($statement);
$rv = $sth->bind_param($p_num, $bind_value);
$rv = $sth->bind_param($p_num, $bind_value, $bind_type);
$rv = $sth->bind_param($p_num, $bind_value, \%attr);
$rv = $sth->execute;
$rv = $sth->execute(@bind_values);
$rc = $sth->bind_col($col_num, \$col_variable);
$rc = $sth->bind_columns(@list_of_refs_to_vars_to_bind);
@row_ary = $sth->fetchrow_array;
$ary_ref = $sth->fetchrow_arrayref;
$hash_ref = $sth->fetchrow_hashref;
$ary_ref = $sth->fetchall_arrayref;
$ary_ref = $sth->fetchall_arrayref( { ... } );
$ary_ref = $sth->fetchall_arrayref( [ ... ] );
$hash_ref = $sth->fetchall_hashref( $key_field );
$rv = $sth->rows;
$rc = $dbh->begin_work;
$rc = $dbh->commit;
$rc = $dbh->rollback;
$quoted_string = $dbh->quote($string);
$rc = $h->err;
$str = $h->errstr;
$rv = $h->state;
$rc = $dbh->disconnect;
*This synopsis above only lists the major methods.*
GETTING HELPtop
If you have questions about DBI, you can get help from the
*dbi-users@perl.org* mailing list. You can subscribe to the list by
emailing:
dbi-users-help@perl.org
Also worth a visit is the DBI home page at:
http://dbi.perl.org/
Before asking any questions, reread this document, consult the archives
and read the DBI FAQ. The archives are listed at the end of this
document and on the DBI home page. The FAQ is installed as a the
DBI::FAQ manpage module so you can read it by executing "perldoc
DBI::FAQ".
Please note that Tim Bunce does not maintain the mailing lists or the
web page (generous volunteers do that). So please don't send mail
directly to him; he just doesn't have the time to answer questions
personally. The *dbi-users* mailing list has lots of experienced people
who should be able to help you if you need it.
NOTEtop
This is the DBI specification that corresponds to the DBI version 1.22
("$Date: 2002/05/22 13:14:13 $").
The DBI specification is evolving at a steady pace, so it's important to
check that you have the latest copy.
The significant user-visible changes in each release are documented in
the the DBI::FAQ manpage module so you can read them by executing
"perldoc DBI::Changes".
Note also that whenever the DBI changes, the drivers take some time to
catch up. Recent versions of the DBI have added new features (generally
marked *NEW* in the text) that may not yet be supported by the drivers
you use. Talk to the authors of those drivers if you need the new
features.
Extensions to the DBI use the "DBIx::*" namespace. See the Naming
Conventions and Name Space entry elsewhere in this document and:
http://search.cpan.org/search?mode=module&query=DBIx%3A%3A
DESCRIPTIONtop
The DBI is a database access module for the Perl programming language.
It defines a set of methods, variables, and conventions that provide a
consistent database interface, independent of the actual database being
used.
It is important to remember that the DBI is just an interface. The DBI
is a layer of "glue" between an application and one or more database
*driver* modules. It is the driver modules which do most of the real
work. The DBI provides a standard interface and framework for the
drivers to operate within.
Architecture of a DBI Application
|<- 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.
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.
Outline Usage
To use DBI, first you need to load the DBI module:
use DBI;
use strict;
(The "use strict;" isn't required but is strongly recommended.)
Then you need to the connect entry elsewhere in this document to your
data source and get a *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 "AutoCommit" behavior 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 "$sth" in our examples.
The typical method call sequence for a "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 *non*-"SELECT" statement is:
prepare,
execute,
execute,
execute.
for example:
$sth = $dbh->prepare("INSERT INTO table(foo,bar,baz) VALUES (?,?,?)");
while() {
chomp;
my ($foo,$bar,$baz) = split /,/;
$sth->execute( $foo, $bar, $baz );
}
The "do()" method can be used for non repeated *non*-"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 the AutoCommit entry
elsewhere in this document is off):
$dbh->commit; # or call $dbh->rollback; to undo changes
Finally, when you have finished working with the data source, you should
the disconnect entry elsewhere in this document from it:
$dbh->disconnect;
General Interface Rules & Caveats
The DBI does not have a concept of a "current session". Every session
has a handle object (i.e., a "$dbh") returned from the "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 "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
behavior, 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
("$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 "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 "func()" method. Private driver
attributes are accessed just like standard attributes.
Many methods have an optional "\%attr" parameter which can be used to
pass information to the driver implementing the method. Except where
specifically documented, the "\%attr" parameter can only be used to pass
driver specific hints. In general, you can ignore "\%attr" parameters or
pass it as "undef".
Naming Conventions and Name Space
The DBI package and all packages below it ("DBI::*") are reserved for
use by the DBI. Extensions and related modules use the "DBIx::"
namespace (see "http://www.perl.com/CPAN/modules/by-module/DBIx/").
Package names beginning with "DBD::" are reserved for use by DBI
database drivers. All environment variables used by the DBI or by
individual DBDs begin with ""DBI_"" or ""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., ""ora_"" for Oracle, ""ing_"" for Ingres, etc).
Driver Specific Prefix Registry:
ad_ DBD::AnyData
ado_ DBD::ADO
best_ DBD::BestWins
csv_ DBD::CSV
db2_ DBD::DB2
f_ DBD::File
file_ DBD::TextFile
ib_ DBD::InterBase
ing_ DBD::Ingres
ix_ DBD::Informix
msql_ DBD::mSQL
mysql_ DBD::mysql
odbc_ DBD::ODBC
ora_ DBD::Oracle
pg_ DBD::Pg
proxy_ DBD::Proxy
rdb_ DBD::RDB
sapdb_ DBD::SAP_DB
solid_ DBD::Solid
syb_ DBD::Sybase
tdat_ DBD::Teradata
tuber_ DBD::Tuber
uni_ DBD::Unify
xbase_ DBD::XBase
SQL - A Query Languagetop
Most DBI drivers require applications to use a dialect of SQL
(Structured Query Language) to interact with the database engine. The
following links provide useful information and further links about SQL:
http://www.altavista.com/query?q=sql+tutorial
http://www.jcc.com/sql_stnd.html
http://www.contrib.andrew.cmu.edu/~shadow/sql.html
The DBI itself does not mandate or require any particular language to be
used; it is language independent. In ODBC terms, the DBI is in
"pass-thru" mode, although individual drivers might not be. The only
requirement is that queries and other statements must be expressed as a
single string of characters passed as the first argument to the the
prepare entry elsewhere in this document or the do entry elsewhere in
this document methods.
For an interesting diversion on the *real* history of RDBMS and SQL,
from the people who made it happen, see:
http://ftp.digital.com/pub/DEC/SRC/technical-notes/SRC-1997-018-html/sqlr95.html
Follow the "And the rest" and "Intergalactic dataspeak" links for the
SQL history.
Placeholders and Bind Values
Some drivers support placeholders and bind values. *Placeholders*, also
called parameter markers, are used to indicate values in a database
statement that will be supplied later, before the prepared statement is
executed. For example, an application might use the following to insert
a row of data into the SALES table:
INSERT INTO sales (product_code, qty, price) VALUES (?, ?, ?)
or the following, to select the description for a product:
SELECT description FROM products WHERE product_code = ?
The "?" characters are the placeholders. The association of actual
values with placeholders is known as *binding*, and the values are
referred to as *bind values*.
When using placeholders with the SQL "LIKE" qualifier, you must remember
that the placeholder substitutes for the whole string. So you should use
""... LIKE ? ..."" and include any wildcard characters in the value that
you bind to the placeholder.
Null Values
Undefined values, or "undef", can be used to indicate null values.
However, care must be taken in the particular case of trying to use null
values to qualify a "SELECT" statement. Consider:
SELECT description FROM products WHERE product_code = ?
Binding an "undef" (NULL) to the placeholder will *not* select rows
which have a NULL "product_code"! Refer to the SQL manual for your
database engine or any SQL book for the reasons for this. To explicitly
select NULLs you have to say ""WHERE product_code IS NULL"" and to make
that general you have to say:
... WHERE (product_code = ? OR (? IS NULL AND product_code IS NULL))
and bind the same value to both placeholders.
Performance
Without using placeholders, the insert statement shown previously would
have to contain the literal values to be inserted and would have to be
re-prepared and re-executed for each row. With placeholders, the insert
statement only needs to be prepared once. The bind values for each row
can be given to the "execute" method each time it's called. By avoiding
the need to re-prepare the statement for each row, the application
typically runs many times faster. Here's an example:
my $sth = $dbh->prepare(q{
INSERT INTO sales (product_code, qty, price) VALUES (?, ?, ?)
}) or die $dbh->errstr;
while (<>) {
chomp;
my ($product_code, $qty, $price) = split /,/;
$sth->execute($product_code, $qty, $price) or die $dbh->errstr;
}
$dbh->commit or die $dbh->errstr;
See the execute and bind_param entries elsewhere in this document for
more details.
The "q{...}" style quoting used in this example avoids clashing with
quotes that may be used in the SQL statement. Use the double-quote like
"qq{...}" operator if you want to interpolate variables into the string.
See the section on "Quote and Quote-like Operators" in the perlop
manpage for more details.
See also the the bind_column entry elsewhere in this document method,
which is used to associate Perl variables with the output columns of a
"SELECT" statement.
THE DBI PACKAGE AND CLASStop
In this section, we cover the DBI class methods, utility functions, and
the dynamic attributes associated with generic DBI handles.
DBI Constants
Constants representing the values of the SQL standard types can be
imported individually by name, or all together by importing the special
":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. "SQL_BIGINT" is
(currently) omitted, because SQL/CLI and ODBC provide conflicting codes.
See the the type_info, type_info_all, and bind_param entries elsewhere
in this document 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.
DBI Class Methods
The following methods are provided by the DBI class:
"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
"$data_source". Returns a database handle object if the connection
succeeds. Use "$dbh-">"disconnect" to terminate the connection.
If the connect fails (see below), it returns "undef" and sets both
"$DBI::err" and "$DBI::errstr". (It does *not* set "$!", etc.) You
should generally test the return status of "connect" and "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 "connect"
call for each database and keep a copy of each returned database
handle.
The "$data_source" value should begin with ""dbi:"*driver_name*":"".
The *driver_name* specifies the driver that will be used to make the
connection. (Letter case is significant.)
As a convenience, if the "$data_source" parameter is undefined or
empty, the DBI will substitute the value of the environment variable
"DBI_DSN". If just the *driver_name* part is empty (i.e., the
"$data_source" prefix is ""dbi::""), the environment variable
"DBI_DRIVER" is used. If neither variable is set, then "connect"
dies.
Examples of "$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 *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. (Where a driver
author needs to define a syntax for the "$data_source", it is
recommended that they follow the ODBC style, shown in the last
example above.)
If the environment variable "DBI_AUTOPROXY" is defined (and the
driver in "$data_source" is not ""Proxy"") then the connect request
will automatically be changed to:
dbi:Proxy:$ENV{DBI_AUTOPROXY};dsn=$data_source
and passed to the DBD::Proxy module. "DBI_AUTOPROXY" is typically
set as ""hostname=...;port=..."". See the DBD::Proxy documentation
for more details.
If "$username" or "$password" are undefined (rather than just
empty), then the DBI will substitute the values of the "DBI_USER"
and "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.
"DBI-">"connect" automatically installs the driver if it has not
been installed yet. Driver installation either returns a valid
driver handle, or it *dies* with an error message that includes the
string ""install_driver"" and the underlying problem. So
"DBI-">"connect" will die on a driver installation failure and will
only return "undef" on a connect failure, in which case
"$DBI::errstr" will hold the error message.
The "$data_source" argument (with the ""dbi:...:"" prefix removed)
and the "$username" and "$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 "$data_source", "$username", and "$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 "$data_source" is specified.)
The "AutoCommit" and "PrintError" attributes for each connection
default to "on". (See the AutoCommit and PrintError entries
elsewhere in this document for more information.) However, it is
strongly recommended that you explicitly define "AutoCommit" rather
than rely on the default. Future versions of the DBI may issue a
warning if "AutoCommit" is not explicitly defined.
The "\%attr" parameter can be used to alter the default settings of
"PrintError", "RaiseError", "AutoCommit", and other attributes. For
example:
$dbh = DBI->connect($data_source, $user, $pass, {
PrintError => 0,
AutoCommit => 0
});
You can also define connection attribute values within the
"$data_source" parameter. For example:
dbi:DriverName(PrintError=>0,Taint=>1):...
Individual attributes values specified in this way take precedence
over any conflicting values specified via the "\%attr" parameter to
"connect".
The "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 ("$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 "connect" (instead of
"\%attr"):
$dbh = DBI->connect($data_source, $user, $pass, $driver);
In this "old-style" form of "connect", the "$data_source" should not
start with ""dbi:driver_name:"". (If it does, the embedded
driver_name will be ignored). Also note that in this older form of
"connect", the "$dbh-">"{AutoCommit}" attribute is *undefined*, the
"$dbh-">"{PrintError}" attribute is off, and the old "DBI_DBNAME"
environment variable is checked if "DBI_DSN" is not defined. Beware
that this "old-style" "connect" will be withdrawn in a future
version of DBI.
"connect_cached" *NEW*
$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;
"connect_cached" is like the connect entry elsewhere in this
document, except that the database handle returned is also stored in
a hash associated with the given parameters. If another call is made
to "connect_cached" with the same parameter values, then the
corresponding cached "$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 "ping" method fails.
Note that the behavior of this method differs in several respects
from the behavior of presistent connections implemented by
Apache::DBI.
Caching can be useful in some applications, but it can also cause
problems and should be used with care. The exact behavior of this
method is liable to change, so if you intend to use it in any
production applications you should discuss your needs on the
*dbi-users* mailing list.
The cache can be accessed (and cleared) via the the CachedKids entry
elsewhere in this document attribute.
"available_drivers"
@ary = DBI->available_drivers;
@ary = DBI->available_drivers($quiet);
Returns a list of all available drivers by searching for "DBD::*"
modules through the directories in "@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 "$quiet" will inhibit
the warning.
"data_sources"
@ary = DBI->data_sources($driver);
@ary = DBI->data_sources($driver, \%attr);
Returns a list of all data sources (databases) available via the
named driver. If "$driver" is empty or "undef", then the value of
the "DBI_DRIVER" environment variable is used.
The driver will be loaded if it hasn't been already. Note that if
the driver loading fails then it *dies* with an error message that
includes the string ""install_driver"" and the underlying problem.
Data sources are returned in a form suitable for passing to the the
connect entry elsewhere in this document method (that is, they will
include the ""dbi:$driver:"" prefix).
Note that many drivers have no way of knowing what data sources
might be available for it. These drivers return an empty or
incomplete list or may require driver-specific attributes, such as a
connected database handle, to be supplied.
"trace"
DBI->trace($trace_level)
DBI->trace($trace_level, $trace_filename)
DBI trace information can be enabled for all handles using the
"trace" DBI class method. To enable trace information for a specific
handle, use the similar "$h-">"trace" method described elsewhere.
Trace levels are as follows:
0 - Trace disabled.
1 - Trace DBI method calls returning with results or errors.
2 - Trace method entry with parameters and returning with results.
3 - As above, adding some high-level information from the driver
and some internal information from the DBI.
4 - As above, adding more detailed information from the driver.
Also includes DBI mutex information when using threaded Perl.
5 and above - As above but with more and more obscure information.
Trace level 1 is best for a simple overview of what's happening.
Trace level 2 is a good choice for general purpose tracing. Levels 3
and above (up to 9) are best reserved for investigating a specific
problem, when you need to see "inside" the driver and DBI.
The trace output is detailed and typically very useful. Much of the
trace output is formatted using the the neat entry elsewhere in this
document function, so strings in the trace output may be edited and
truncated.
Initially trace output is written to "STDERR". If "$trace_filename"
is specified and can be opened in append mode then all trace output
(including that from other handles) is redirected to that file. A
warning is generated is the file can't be opened. 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 "STDERR" and the previous trace file is closed. The
"trace" method returns the *previous* tracelevel.
See also the "$h-">"trace" and "$h-">"trace_msg" methods and the the
DEBUGGING entry elsewhere in this document section for information
about the "DBI_TRACE" environment variable.
DBI Utility Functionstop
In addition to the methods listed in the previous section, the DBI
package also provides these utility functions:
"neat"
$str = DBI::neat($value, $maxlen);
Return a string containing a neat (and tidy) representation of the
supplied value.
Strings will be quoted, although internal quotes will *not* be
escaped. Values known to be numeric will be unquoted. Undefined
(NULL) values will be shown as "undef" (without quotes). Unprintable
characters will be replaced by dot (.).
For result strings longer than "$maxlen" the result string will be
truncated to "$maxlen-4" and ""...'"" will be appended. If "$maxlen"
is 0 or "undef", it defaults to "$DBI::neat_maxlen" which, in turn,
defaults to 400.
This function is designed to format values for human consumption. It
is used internally by the DBI for the trace entry elsewhere in this
document output. It should typically *not* be used for formatting
values for database use. (See also the quote entry elsewhere in this
document.)
"neat_list"
$str = DBI::neat_list(\@listref, $maxlen, $field_sep);
Calls "DBI::neat" on each element of the list and returns a string
containing the results joined with "$field_sep". "$field_sep"
defaults to "", "".
"looks_like_number"
@bool = DBI::looks_like_number(@array);
Returns true for each element that looks like a number. Returns
false for each element that does not look like a number. Returns
"undef" for each element that is undefined or empty.
"hash"
$hash_value = DBI::hash($buffer, $type);
Return a 32-bit integer 'hash' value corresponding to the contents
of $buffer. The $type parameter selects which kind of hash algorithm
should be used.
For the technically curious, type 0 (which is the default if $type
isn't specified) is based on the Perl 5.1 hash except that the value
is forced to be negative (for obscure historical reasons). Type 1 is
the better "Fowler / Noll / Vo" (FNV) hash. See
http://www.isthe.com/chongo/tech/comp/fnv/ for more information.
Both types are implemented in C and are very fast.
This function doesn't have much to do with databases, except that it
can be handy to store hash values in a database.
DBI Dynamic Attributestop
Dynamic attributes are always associated with the *last handle used*
(that handle is represented by "$h" in the descriptions below).
Where an attribute is equivalent to a method call, then refer to the
method call for all related documentation.
Warning: these attributes are provided as a convenience but they do have
limitations. Specifically, they have a short lifespan: because they are
associated with the last handle used, they should only be used
*immediately* after calling the method that "sets" them. If in any
doubt, use the corresponding method call.
"$DBI::err"
Equivalent to "$h-">"err".
"$DBI::errstr"
Equivalent to "$h-">"errstr".
"$DBI::state"
Equivalent to "$h-">"state".
"$DBI::rows"
Equivalent to "$h-">"rows". Please refer to the documentation for
the the rows entry elsewhere in this document method.
"$DBI::lasth"
Returns the DBI object handle used for the most recent DBI method
call. If the last DBI method call was a DESTROY then $DBI::lasth
will return the handle of the parent of the destroyed handle, if
there is one.
METHODS COMMON TO ALL HANDLEStop
The following methods can be used by all types of DBI handles.
"err"
$rv = $h->err;
Returns the *native* database engine error code from the last driver
method called. The code is typically an integer but you should not
assume that.
The DBI resets $h->err to undef before most DBI method calls, so the
value only has a short lifespan. Also, most drivers share the same
error variables across all their handles, so calling a method on one
handle will typically reset the error on all the other handles that
are children of that driver.
If you need to test for individual errors *and* have your program be
portable to different database engines, then you'll need to
determine what the corresponding error codes are for all those
engines and test for all of them.
"errstr"
$str = $h->errstr;
Returns the native database engine error message from the last
driver method called. This has the same lifespan issues as the the
err entry elsewhere in this document method described above.
"state"
$str = $h->state;
Returns an error code in the standard SQLSTATE five character
format. Note that the specific success code "00000" is translated to
'' (false). If the driver does not support SQLSTATE (and most
don't), then state will return "S1000" (General Error) for all
errors.
The driver is free to return any value via "state", e.g., warning
codes, even if it has not declared an error by returning a true
value via the the err entry elsewhere in this document method
described above.
"set_err" *NEW*
$rv = $h->set_err($err, $errstr);
$rv = $h->set_err($err, $errstr, $state, $method);
$rv = $h->set_err($err, $errstr, $state, $method, $rv);
Set the "err", "errstr", and "state" values for the handle. This
will trigger the normal DBI error handling mechanisms, such as
"RaiseError" and "HandleError", if they are enabled. This method is
typically only used by DBI drivers and DBI subclasses.
The $method parameter provides an alternate method name, instead of
the fairly unhelpful '"set_err"', for the "RaiseError"/"PrintError"
error string.
The "set_err" method normally returns undef. The $rv parameter
provides an alternate return value. The "HandleError" subroutine can
access and alter this value.
"trace"
$h->trace($trace_level);
$h->trace($trace_level, $trace_filename);
DBI trace information can be enabled for a specific handle (and any
future children of that handle) by setting the trace level using the
"trace" method.
Trace level 1 is best for a simple overview of what's happening.
Trace level 2 is a good choice for general purpose tracing. Levels 3
and above (up to 9) are best reserved for investigating a specific
problem, when you need to see "inside" the driver and DBI. Set
"$trace_level" to 0 to disable the trace.
The trace output is detailed and typically very useful. Much of the
trace output is formatted using the the neat entry elsewhere in this
document function, so strings in the trace output may be edited and
truncated.
Initially, trace output is written to "STDERR". If "$trace_filename"
is specified, then the file is opened in append mode and *all* trace
output (including that from other handles) is redirected to that
file. 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 "STDERR" and the previous
trace file is closed.
See also the "DBI-">"trace" method, the "$h-">"{TraceLevel}"
attribute, and the DEBUGGING entry elsewhere in this document for
information about the "DBI_TRACE" environment variable.
"trace_msg"
$h->trace_msg($message_text);
$h->trace_msg($message_text, $min_level);
Writes "$message_text" to the trace file if trace is enabled for
"$h" or for the DBI as a whole. Can also be called as
"DBI-">"trace_msg($msg)". See the trace entry elsewhere in this
document.
If "$min_level" is defined, then the message is output only if the
trace level is equal to or greater than that level. "$min_level"
defaults to 1.
"func"
$h->func(@func_arguments, $func_name);
The "func" method can be used to call private non-standard and
non-portable methods implemented by the driver. Note that the
function name is given as the last argument.
This method is not directly related to calling stored procedures.
Calling stored procedures is currently not defined by the DBI. Some
drivers, such as DBD::Oracle, support it in non-portable ways. See
driver documentation for more details.
ATTRIBUTES COMMON TO ALL HANDLEStop
These attributes are common to all types of DBI handles.
Some attributes are inherited by child handles. That is, the value of an
inherited attribute in a newly created statement handle is the same as
the value in the parent database handle. Changes to attributes in the
new statement handle do not affect the parent database handle and
changes to the database handle do not affect existing statement handles,
only future ones.
Attempting to set or get the value of an unknown attribute is fatal,
except for private driver specific attributes (which all have names
starting with a lowercase letter).
Example:
$h->{AttributeName} = ...; # set/write
... = $h->{AttributeName}; # get/read
"Warn" (boolean, inherited)
Enables useful warnings for certain bad practices. Enabled by
default. Some emulation layers, especially those for Perl 4
interfaces, disable warnings. Since warnings are generated using the
Perl "warn" function, they can be intercepted using the Perl
"$SIG{__WARN__}" hook.
"Active" (boolean, read-only)
True if the handle object is "active". This is rarely used in
applications. The exact meaning of active is somewhat vague at the
moment. For a database handle it typically means that the handle is
connected to a database ("$dbh-">"disconnect" sets "Active" off).
For a statement handle it typically means that the handle is a
"SELECT" that may have more data to fetch. (Fetching all the data or
calling "$sth-">"finish" sets "Active" off.)
"Kids" (integer, read-only)
For a driver handle, "Kids" is the number of currently existing
database handles that were created from that driver handle. For a
database handle, "Kids" is the number of currently existing
statement handles that were created from that database handle.
"ActiveKids" (integer, read-only)
Like "Kids", but only counting those that are "Active" (as above).
"CachedKids" (hash ref)
For a database handle, returns a reference to the cache (hash) of
statement handles created by the the prepare_cached entry elsewhere
in this document method. For a driver handle, returns a reference to
the cache (hash) of database handles created by the the
connect_cached entry elsewhere in this document method.
"CompatMode" (boolean, inherited)
Used by emulation layers (such as Oraperl) to enable compatible
behavior in the underlying driver (e.g., DBD::Oracle) for this
handle. Not normally set by application code.
"InactiveDestroy" (boolean)
This attribute can be used to disable the *database engine* related
effect of DESTROYing a handle (which would normally close a prepared
statement or disconnect from the database etc).
For a database handle, this attribute does not disable an *explicit*
call to the disconnect method, only the implicit call from DESTROY.
The default value, false, means that a handle will be automatically
destroyed when it passes out of scope. A true value disables
automatic destruction. (Think of the name as meaning 'inactive the
DESTROY method'.)
This attribute is specifically designed for use in Unix applications
that "fork" child processes. Either the parent or the child process,
but not both, should set "InactiveDestroy" on all their shared
handles. Note that some databases, including Oracle, don't support
passing a database connection across a fork.
"PrintError" (boolean, inherited)
This attribute can be used to force errors to generate warnings
(using "warn") in addition to returning error codes in the normal
way. When set "on", any method which results in an error occuring
will cause the DBI to effectively do a "warn("$class $method failed:
$DBI::errstr")" where "$class" is the driver class and "$method" is
the name of the method which failed. E.g.,
DBD::Oracle::db prepare failed: ... error text here ...
By default, "DBI-">"connect" sets "PrintError" "on".
If desired, the warnings can be caught and processed using a
"$SIG{__WARN__}" handler or modules like CGI::Carp and
CGI::ErrorWrap.
"RaiseError" (boolean, inherited)
This attribute can be used to force errors to raise exceptions
rather than simply return error codes in the normal way. It is "off"
by default. When set "on", any method which results in an error will
cause the DBI to effectively do a "die("$class $method failed:
$DBI::errstr")", where "$class" is the driver class and "$method" is
the name of the method that failed. E.g.,
DBD::Oracle::db prepare failed: ... error text here ...
If you turn "RaiseError" on then you'd normally turn "PrintError"
off. If "PrintError" is also on, then the "PrintError" is done first
(naturally).
Typically "RaiseError" is used in conjunction with "eval { ... }" to
catch the exception that's been thrown and followed by an "if ($@) {
... }" block to handle the caught exception. In that eval block the
$DBI::lasth variable can be useful for diagnosis and reporting. For
example, $DBI::lasth->{Type} and $DBI::lasth->{Statement}.
If you want to temporarily turn "RaiseError" off (inside a library
function that is likely to fail, for example), the recommended way
is like this:
{
local $h->{RaiseError}; # localize and turn off for this block
...
}
The original value will automatically and reliably be restored by
Perl, regardless of how the block is exited. The same logic applies
to other attributes, including "PrintError".
Sadly, this doesn't work for Perl versions up to and including
5.004_04. Even more sadly, for Perl 5.5 and 5.6.0 it does work but
leaks memory! For backwards compatibility, you could just use "eval
{ ... }" instead.
"HandleError" (code ref, inherited) *NEW*
This attribute can be used to provide your own alternative behaviour
in case of errors. If set to a reference to a subroutine then that
subroutine is called when an error is detected (at the same point
that "RaiseError" and "PrintError" are handled).
The subroutine is called with three parameters: the error message
string that "RaiseError" and "PrintError" would use, the DBI handle
being used, and the first value being returned by the method that
failed (typically undef).
If the subroutine returns a false value then the "RaiseError" and/or
"PrintError" attributes are checked and acted upon as normal.
For example, to get a full stack trace for any error:
use Carp;
$h->{HandleError} = sub { confess(shift) };
Or to turn errors into exceptions:
use Exception; # or your own favourite exception module
$h->{HandleError} = sub { Exception->new('DBI')->raise($_[0]) };
It is possible to 'stack' multiple HandleError handlers by using
closures:
sub your_subroutine {
my $previous_handler = $h->{HandleError};
$h->{HandleError} = sub {
return 1 if $previous_handler and &$previous_handler(@_);
... your code here ...
}
}
Using a "my" inside a subroutine to store the previous "HandleError"
value is important. See the perlsub manpage and the perlref manpage
for more information about *closures*.
It is possible for "HandleError" to hide an error, to a limited
degree, by using the set_err entry elsewhere in this document 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(0,""); # 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 *good* use for it then
please let me know.
"ShowErrorStatement" (boolean, inherited) *NEW*
This attribute can be used to cause the relevant Statement text to
be appended to the error messages generated by the "RaiseError" and
"PrintError" attributes. Only applies to errors on statement handles
plus the prepare() and do() database handle methods. (The exact
format of the appended text is subject to change.)
"TraceLevel" (integer, inherited) *NEW*
This attribute can be used as an alternative to the the trace entry
elsewhere in this document method to set the DBI trace level for a
specific handle.
"FetchHashKeyName" (string, inherited)
This attribute is used to specify which attribute name the
fetchrow_hashref() method should use to get the field names for the
hash keys. For historical reasons it defaults to '"NAME"' but it is
recommended to set it to '"NAME_lc"' or '"NAME_uc"' according to
your preference. It can only be set for driver and database tables.
For statement handles the value is frozen when prepare() is called.
"ChopBlanks" (boolean, inherited)
This 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 behavior should set the
attribute as needed. Emulation interfaces should set the attribute
to match the behavior of the interface they are emulating.
Drivers are not required to support this attribute, but any driver
which does not support it must arrange to return "undef" as the
attribute value.
"LongReadLen" (unsigned integer, inherited)
This attribute may be used to control the maximum length of long
fields ("blob", "memo", etc.) which the driver will read from the
database automatically when it fetches each row of data. The
"LongReadLen" attribute only relates to fetching and reading long
values; it is not involved in inserting or updating them.
A value of 0 means not to automatically fetch any long data.
("fetch" should return "undef" for long fields when "LongReadLen" is
0.)
The default is typically 0 (zero) bytes but may vary between
drivers. Applications fetching long fields should set this value to
slightly larger than the longest long field value to be fetched.
Some databases return some long types encoded as pairs of hex
digits. For these types, "LongReadLen" relates to the underlying
data length and not the doubled-up length of the encoded string.
Changing the value of "LongReadLen" for a statement handle after it
has been "prepare"'d will typically have no effect, so it's common
to set "LongReadLen" on the "$dbh" before calling "prepare".
Note that the value used here has a direct effect on the memory used
by the application, so don't be too generous.
See the LongTruncOk entry elsewhere in this document for more
information on truncation behavior.
"LongTruncOk" (boolean, inherited)
This attribute may be used to control the effect of fetching a long
field value which has been truncated (typically because it's longer
than the value of the "LongReadLen" attribute).
By default, "LongTruncOk" is false and so fetching a long value that
needs to be truncated will cause the fetch to fail. (Applications
should always be sure to check for errors after a fetch loop in case
an error, such as a divide by zero or long field truncation, caused
the fetch to terminate prematurely.)
If a fetch fails due to a long field truncation when "LongTruncOk"
is false, many drivers will allow you to continue fetching further
rows.
See also the LongReadLen entry elsewhere in this document.
"Taint" (boolean, inherited)
If this attribute is set to a true value *and* Perl is running in
taint mode (e.g., started with the "-T" option), then all data
fetched from the database is tainted, and the arguments to most DBI
method calls are checked for being tainted. *This may change.*
The attribute defaults to off, even if Perl is in taint mode. See
the perlsec manpage for more about taint mode. If Perl is not
running in taint mode, this attribute has no effect.
When fetching data that you trust you can turn off the Taint
attribute, for that statement handle, for the duration of the fetch
loop.
Currently only fetched data is tainted. It is possible that the
results of other DBI method calls, and the value of fetched
attributes, may also be tainted in future versions. That change may
well break your applications unless you take great care now. If you
use DBI Taint mode, please report your experience and any
suggestions for changes.
"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 retreive
any attribute which has a name starting with ""private_"".
It is *strongly* recommended that you use just *one* private
attribute (e.g., use a hash ref) *and* give it a long and
unambiguous name that includes the module or application name that
the attribute relates to (e.g.,
""private_YourFullModuleName_thingy"").
Because of the way the Perl tie mechanism works you cannot reliably
use the "||=" 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} = { ... };
DBI DATABASE HANDLE OBJECTStop
This section covers the methods and attributes associated with database
handles.
Database Handle Methods
The following methods are specified for DBI database handles:
"do"top
$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 "undef" on error. A return value of "-1" means the
number of rows is not known, not applicable, or not available.
This method is typically most useful for *non*-"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 "SELECT" statements because it does not return a
statement handle (so you can't fetch any data).
The default "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;
($rows == 0) ? "0E0" : $rows; # always return true if no error
}
For example:
my $rows_deleted = $dbh->do(q{
DELETE FROM table
WHERE status = ?
}, undef, 'DONE') or die $dbh->errstr;
Using placeholders and "@bind_values" with the "do" method can be
useful because it avoids the need to correctly quote any variables
in the "$statement". But if you'll be executing the statement many
times then it's more efficient to "prepare" it once and call
"execute" many times instead.
The "q{...}" style quoting used in this example avoids clashing with
quotes that may be used in the SQL statement. Use the
double-quote-like "qq{...}" operator if you want to interpolate
variables into the string. See the section on "Quote and Quote-like
Operators" in the perlop manpage for more details.
"selectrow_array"top
@row_ary = $dbh->selectrow_array($statement);
@row_ary = $dbh->selectrow_array($statement, \%attr);
@row_ary = $dbh->selectrow_array($statement, \%attr, @bind_values);
This utility method combines the prepare, execute, and
fetchrow_array entries elsewhere in this document into a single
call. If called in a list context, it returns the first row of data
from the statement. If called in a scalar context, it returns the
first field of the first row. The "$statement" parameter can be a
previously prepared statement handle, in which case the "prepare" is
skipped.
If any method fails, and the RaiseError entry elsewhere in this
document is not set, "selectrow_array" will return an empty list.
In a scalar context, "selectrow_array" returns the value of the
first field. An "undef" is returned if there are no matching rows or
an error occurred. Since that "undef" can't be distinguished from an
"undef" returned because the first field value was NULL, calling
"selectrow_array" in a scalar context should be used with caution.
"selectrow_arrayref"top
$ary_ref = $dbh->selectrow_array($statement);
$ary_ref = $dbh->selectrow_array($statement, \%attr);
$ary_ref = $dbh->selectrow_array($statement, \%attr, @bind_values);
This utility method combines the prepare, execute, and
fetchrow_arrayref entries elsewhere in this document into a single
call. It returns the first row of data from the statement. The
"$statement" parameter can be a previously prepared statement
handle, in which case the "prepare" is skipped.
If any method fails, and the RaiseError entry elsewhere in this
document is not set, "selectrow_array" will return undef.
"selectrow_hashref"top
$hash_ref = $dbh->selectrow_hashref($statement);
$hash_ref = $dbh->selectrow_hashref($statement, \%attr);
$hash_ref = $dbh->selectrow_hashref($statement, \%attr, @bind_values);
This utility method combines the prepare, execute, and
fetchrow_hashref entries elsewhere in this document into a single
call. It returns the first row of data from the statement. The
"$statement" parameter can be a previously prepared statement
handle, in which case the "prepare" is skipped.
If any method fails, and the RaiseError entry elsewhere in this
document is not set, "selectrow_hashref" will return undef.
"selectall_arrayref"top
$ary_ref = $dbh->selectall_arrayref($statement);
$ary_ref = $dbh->selectall_arrayref($statement, \%attr);
$ary_ref = $dbh->selectall_arrayref($statement, \%attr, @bind_values);
This utility method combines the prepare, execute, and
fetchall_arrayref entries elsewhere in this document into a single
call. It returns a reference to an array containing a reference to
an array for each row of data fetched.
The "$statement" parameter can be a previously prepared statement
handle, in which case the "prepare" is skipped. This is recommended
if the statement is going to be executed many times.
If the RaiseError entry elsewhere in this document is not set and
any method except "fetchall_arrayref" fails then
"selectall_arrayref" will return "undef"; if "fetchall_arrayref"
fails then it will return with whatever data has been fetched thus
far. You should check "$sth-">"err" afterwards (or use the
"RaiseError" attribute) to discover if the data is complete or was
truncated due to an error.
The the fetchall_arrayref entry elsewhere in this document method
called by "selectall_arrayref" supports a $slice parameter. You can
specify a value for $slice by including a '"Slice"' or '"Columns"'
attribute in \%attr. The only difference between the two is that if
"Slice" is not defined and "Columns" is an array ref, then the array
is assumed to contain column index values (which count from 1),
rather than perl array index values. In which case the array is
copied and each value decremented before passing to
"/fetchall_arrayref".
"selectall_hashref"top
$hash_ref = $dbh->selectall_hashref($statement, $key_field);
$hash_ref = $dbh->selectall_hashref($statement, $key_field, \%attr);
$hash_ref = $dbh->selectall_hashref($statement, $key_field, \%attr, @bind_values);
This utility method combines the prepare, execute, and
fetchall_hashref entries elsewhere in this document into a single
call. It returns a reference to a hash containing one entry for each
row. The key for each row entry is specified by $key_field. The
value is a reference to a hash returned by "fetchrow_hashref".
The "$statement" parameter can be a previously prepared statement
handle, in which case the "prepare" is skipped. This is recommended
if the statement is going to be executed many times.
If any method except "fetchrow_hashref" fails, and the RaiseError
entry elsewhere in this document is not set, "selectall_hashref"
will return "undef". If "fetchrow_hashref" fails and the RaiseError
entry elsewhere in this document is not set, then it will return
with whatever data it has fetched thus far. $DBI::err should be
checked to catch that.
"selectcol_arrayref"top
$ary_ref = $dbh->selectcol_arrayref($statement);
$ary_ref = $dbh->selectcol_arrayref($statement, \%attr);
$ary_ref = $dbh->selectcol_arrayref($statement, \%attr, @bind_values);
This utility method combines the prepare and execute entries
elsewhere in this document, and fetching one column from all the
rows, into a single call. It returns a reference to an array
containing the values of the first column from each row.
The "$statement" parameter can be a previously prepared statement
handle, in which case the "prepare" is skipped. This is recommended
if the statement is going to be executed many times.
If any method except "fetch" fails, and the RaiseError entry
elsewhere in this document is not set, "selectcol_arrayref" will
return "undef". If "fetch" fails and the RaiseError entry elsewhere
in this document is not set, then it will return with whatever data
it has fetched thus far. $DBI::err should be checked to catch that.
The "selectcol_arrayref" method defaults to pushing a single column
value (the first) from each row into the result array. However, it
can also push another column, or even multiple columns per row, into
the result array. This behaviour can be specified via a '"Columns"'
attribute which must be a ref to an array containing the column
number or numbers to use. For example:
# get array of id and name pairs:
my $ary_ref = $dbh->selectcol_arrayref("select id, name from table", { Columns=>[1,2] });
my %hash = @$ary_ref; # build hash from key-value pairs so $hash{$id} => name
"prepare"top
$sth = $dbh->prepare($statement) or die $dbh->errstr;
$sth = $dbh->prepare($statement, \%attr) or die $dbh->errstr;
Prepares a single statement for later execution by the database
engine and returns a reference to a statement handle object.
The returned statement handle can be used to get attributes of the
statement and invoke the the execute entry elsewhere in this
document method. See the Statement Handle Methods entry elsewhere in
this document.
Drivers for engines without the concept of preparing a statement
will typically just store the statement in the returned handle and
process it when "$sth-">"execute" is called. Such drivers are
unlikely to give much useful information about the statement, such
as "$sth-">"{NUM_OF_FIELDS}", until after "$sth-">"execute" has been
called. Portable applications should take this into account.
In general, DBI drivers do not parse the contents of the statement
(other than simply counting any the Placeholders entry elsewhere in
this document). The statement is passed directly to the database
engine, sometimes known as pass-thru mode. This has advantages and
disadvantages. On the plus side, you can access all the
functionality of the engine being used. On the downside, you're
limited if you're using a simple engine, and you need to take extra
care if writing applications intended to be portable between
engines.
Portable applications should not assume that a new statement can be
prepared and/or executed while still fetching results from a
previous statement.
Some command-line SQL tools use statement terminators, like a
semicolon, to indicate the end of a statement. Such terminators
should not normally be used with the DBI.
"prepare_cached"top
$sth = $dbh->prepare_cached($statement)
$sth = $dbh->prepare_cached($statement, \%attr)
$sth = $dbh->prepare_cached($statement, \%attr, $allow_active)
Like the prepare entry elsewhere in this document except that the
statement handle returned will be stored in a hash associated with
the "$dbh". If another call is made to "prepare_cached" with the
same "$statement" and "%attr" values, then the corresponding cached
"$sth" will be returned without contacting the database server.
Here are some examples of "prepare_cached":
sub insert_hash {
my ($table, $field_values) = @_;
my @fields = sort keys %$field_values; # sort required
my @values = @{$field_values}{@fields};
my $sql = sprintf "insert into %s (%s) values (%s)",
$table, join(",", @fields), join(",", ("?")x@fields);
my $sth = $dbh->prepare_cached($sql);
return $sth->execute(@values);
}
sub search_hash {
my ($table, $field_values) = @_;
my @fields = sort keys %$field_values; # sort required
my @values = @{$field_values}{@fields};
my $qualifier = "";
$qualifier = "where ".join(" and ", map { "$_=?" } @fields) if @fields;
$sth = $dbh->prepare_cached("SELECT * FROM $table $qualifier");
return $dbh->selectall_arrayref($sth, {}, @values);
}
*Caveat emptor:* This caching can be useful in some applications,
but it can also cause problems and should be used with care. Here is
a contrived case where caching would cause a significant problem:
my $sth = $dbh->prepare_cached('SELECT * FROM foo WHERE bar=?');
$sth->execute($bar);
while (my $data = $sth->fetchrow_hashref) {
my $sth2 = $dbh->prepare_cached('SELECT * FROM foo WHERE bar=?');
$sth2->execute($data->{bar});
while (my $data2 = $sth2->fetchrow_arrayref) {
do_stuff(...);
}
}
In this example, since both handles are preparing the exact same
statement, "$sth2" will not be its own statement handle, but a
duplicate of "$sth" returned from the cache. The results will
certainly not be what you expect. Typically the the inner fetch loop
will work normally, fetching all the records and terminating when
there are no more, but now $sth is the same as $sth2 the outer fetch
loop will also terminate.
The "$allow_active" parameter lets you adjust DBI's behavior when
prepare_cached is returning a statement handle that is still active.
There are three settings:
0: A warning will be generated, and "finish" will be called on
the statement handle before it is returned. This is the default
behavior if "$allow_active" is not passed.
1: "finish" will be called on the statement handle, but the
warning is suppressed.
2: DBI will not touch the statement handle before returning it.
You will need to check "$sth-">"{Active}" on the returned
statement handle and deal with it in your own code.
Because the cache used by prepare_cached() is keyed by all the
parameters, including any attributes passed, you can also avoid this
issue by doing something like:
my $sth = $dbh->prepare_cached("...", { dbi_dummy => __FILE__.__LINE__ });
which will ensure that prepare_cached only returns statements cached
by that line of code in that source file.
"commit"top
$rc = $dbh->commit or die $dbh->errstr;
Commit (make permanent) the most recent series of database changes
if the database supports transactions and AutoCommit is off.
If "AutoCommit" is on, then calling "commit" will issue a "commit
ineffective with AutoCommit" warning.
See also the Transactions entry elsewhere in this document in the
the FURTHER INFORMATION entry elsewhere in this document section
below.
"rollback"top
$rc = $dbh->rollback or die $dbh->errstr;
Rollback (undo) the most recent series of uncommitted database
changes if the database supports transactions and AutoCommit is off.
If "AutoCommit" is on, then calling "rollback" will issue a
"rollback ineffective with AutoCommit" warning.
See also the Transactions entry elsewhere in this document in the
the FURTHER INFORMATION entry elsewhere in this document section
below.
"begin_work"top
$rc = $dbh->begin_work or die $dbh->errstr;
Enable transactions (by turning "AutoCommit" off) until the next
call to "commit" or "rollback". After the next "commit" or
"rollback", "AutoCommit" will automatically be turned on again.
If "AutoCommit" is already off when "begin_work" is called then it
does nothing except return an error. If the driver does not support
transactions then when "begin_work" attempts to set "AutoCommit" off
the driver will trigger a fatal error.
See also the Transactions entry elsewhere in this document in the
the FURTHER INFORMATION entry elsewhere in this document section
below.
"disconnect"top
$rc = $dbh->disconnect or warn $dbh->errstr;
Disconnects the database from the database handle. "disconnect" is
typically only used before exiting the program. The handle is of
little use after disconnecting.
The transaction behavior of the "disconnect" method is, sadly,
undefined. Some database systems (such as Oracle and Ingres) will
automatically commit any outstanding changes, but others (such as
Informix) will rollback any outstanding changes. Applications not
using "AutoCommit" should explicitly call "commit" or "rollback"
before calling "disconnect".
The database is automatically disconnected by the "DESTROY" method
if still connected when there are no longer any references to the
handle. The "DESTROY" method for each driver should implicitly call
"rollback" to undo any uncommitted changes. This is vital behavior
to ensure that incomplete transactions don't get committed simply
because Perl calls "DESTROY" on every object before exiting. Also,
do not rely on the order of object destruction during "global
destruction", as it is undefined.
Generally, if you want your changes to be commited or rolled back
when you disconnect, then you should explicitly call the commit
entry elsewhere in this document or the rollback entry elsewhere in
this document before disconnecting.
If you disconnect from a database while you still have active
statement handles (e.g., SELECT statement handles that may have more
data to fetch), you will get a warning. The warning may indicate
that a fetch loop terminated early, perhaps due to an uncaught
error. To avoid the warning call the "finish" method on the active
handles.
"ping"top
$rc = $dbh->ping;
Attempts to determine, in a reasonably efficient way, if the
database server is still running and the connection to it is still
working. Individual drivers should implement this function in the
most suitable manner for their database engine.
The current *default* implementation always returns true without
actually doing anything. Actually, it returns ""0 but true"" which
is true but zero. That way you can tell if the return value is
genuine or just the default. Drivers should override this method
with one that does the right thing for their type of database.
Few applications would have direct use for this method. See the
specialized Apache::DBI module for one example usage.
"get_info" *NEW*top
Warning: This method is experimental and may change.
$value = $dbh->get_info( $info_type );
Returns information about the implementation, i.e. driver and data
source capabilities, restrictions etc. It returns "undef" for
unknown or unimplemented information types. For example:
$database_version = $dbh->get_info( 18 ); # SQL_DBMS_VER
$max_select_tables = $dbh->get_info( 106 ); # SQL_MAXIMUM_TABLES_IN_SELECT
See the section on "Standards Reference Information" for more
detailed information about the information types and their meanings
and possible return values.
The DBI curently doesn't provide a name to number mapping for the
information type codes or the results. Applications are expected to
use the integer values directly, with the name in a comment, or
define their own named values using something like the the constant
manpage pragma.
Because some DBI methods make use of get_info(), drivers are
strongly encouraged to support *at least* the following very minimal
set of information types to ensure the DBI itself works properly:
Type Name Example A Example B
---- -------------------------- ------------ ------------
17 SQL_DBMS_NAME 'ACCESS' 'Oracle'
18 SQL_DBMS_VER '03.50.0000' '08.01.0721'
29 SQL_IDENTIFIER_QUOTE_CHAR '`' '"'
41 SQL_CATALOG_NAME_SEPARATOR '.' '@'
114 SQL_CATALOG_LOCATION 1 2
"table_info" *NEW*top
Warning: This method is experimental and may change.
$sth = $dbh->table_info( $catalog, $schema, $table, $type );
$sth = $dbh->table_info( $catalog, $schema, $table, $type, \%attr );
$sth = $dbh->table_info( \%attr ); # old style
Returns an active statement handle that can be used to fetch
information about tables and views that exist in the database.
The old style interface passes all the parameters as a reference to
an attribute hash with some or all of the following attributes:
%attr = (
TABLE_CAT => $catalog # String value of the catalog name
, TABLE_SCHEM => $schema # String value of the schema name
, TABLE_NAME => $table # String value of the table name
, TABLE_TYPE => $type # String value of the table type(s)
);
The old style interface is deprecated and will be removed in a
future version.
The support for the selection criteria is driver specific. If the
driver doesn't support one or more of them then you may get back
more than you asked for and can do the filtering yourself.
The arguments $catalog, $schema and $table may accept search
patterns according to the database/driver, for example: $table =
'%FOO%'; Remember that the underscore character ('"_"') is a search
pattern that means match any character, so 'FOO_%' is the same as
'FOO%' and 'FOO_BAR%' will match names like 'FOO1BAR'.
The value of $type is a comma-separated list of one or more types of
tables to be returned in the result set. Each value may optionally
be quoted, e.g.:
$type = "TABLE";
$type = "'TABLE','VIEW'";
In addition the following special cases may also be supported by
some drivers:
* If the value of $catalog is '%' and $schema and $table name are
empty strings, the result set contains a list of catalog names. For
example:
$sth = $dbh->table_info('%', '', '');
* If the value of $schema is '%' and $catalog and $table are empty
strings, the result set contains a list of schema names.
* If the value of $type is '%' and $catalog, $schema, and $table are
all empty strings, the result set contains a list of table types.
The statement handle returned has at least the following fields in
the order show below. Other fields, after these, may also be
present.
TABLE_CAT: Table catalog identifier. This field is NULL ("undef") if
not applicable to the data source, which is usually the case. This
field is empty if not applicable to the table.
TABLE_SCHEM: The name of the schema containing the TABLE_NAME value.
This field is NULL ("undef") if not applicable to data source, and
empty if not applicable to the table.
TABLE_NAME: Name of the table (or view, synonym, etc).
TABLE_TYPE: One of the following: "TABLE", "VIEW", "SYSTEM TABLE",
"GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM" or a type
identifier that is specific to the data source.
REMARKS: A description of the table. May be NULL ("undef").
Note that "table_info" might not return records for all tables.
Applications can use any valid table regardless of whether it's
returned by "table_info".
See also the tables entry elsewhere in this document, the section on
"Catalog Methods" and the section on "Standards Reference
Information".
"column_info" *NEW*top
Warning: This method is experimental and may change.
$sth = $dbh->column_info( $catalog, $schema, $table, $column );
Returns an active statement handle that can be used to fetch
information about columns in specified tables.
The arguments $schema, $table and $column may accept search patterns
according to the database/driver, for example: $table = '%FOO%';
Note: The support for the selection criteria is driver specific. If
the driver doesn't support one or more of them then you may get back
more than you asked for and can do the filtering yourself.
The statement handle returned has at least the following fields in
the order shown below. Other fields, after these, may also be
present.
TABLE_CAT: The catalog identifier. This field is NULL ("undef") if
not applicable to the data source, which is often the case. This
field is empty if not applicable to the table.
TABLE_SCHEM: The schema identifier. This field is NULL ("undef") if
not applicable to the data source, and empty if not applicable to
the table.
TABLE_NAME: The table identifier. Note: A driver may provide column
metadata not only for base tables, but also for derived objects like
SYNONYMS etc.
COLUMN_NAME: The column identifier.
DATA_TYPE: The concise data type code.
TYPE_NAME: A data source dependent data type name.
COLUMN_SIZE: The column size. This is the maximum length in
characters for character data types, the number of digits or bits
for numeric data types or the length in the representation of
temporal types. See the relevant specifications for detailed
information.
BUFFER_LENGTH: The length in bytes of transferred data.
DECIMAL_DIGITS: The total number of significant digits to the right
of the decimal point.
NUM_PREC_RADIX: The radix for numeric precision. The value is 10 or
2 for numeric data types and NULL ("undef") if not applicable.
NULLABLE: Indicates if a column can accept NULLs. The following
values are defined:
SQL_NO_NULLS 0
SQL_NULLABLE 1
SQL_NULLABLE_UNKNOWN 2
REMARKS: A description of the column.
COLUMN_DEF: The default value of the column.
SQL_DATA_TYPE: The SQL data type.
SQL_DATETIME_SUB: The subtype code for datetime and interval data
types.
CHAR_OCTET_LENGTH: The maximum length in bytes of a character or
binary data type column.
ORDINAL_POSITION: The column sequence number (starting with 1).
IS_NULLABLE: Indicates if the column can accept NULLs. Possible
values are: 'NO', 'YES' and ''.
SQL/CLI defines the following additional columns:
CHAR_SET_CAT
CHAR_SET_SCHEM
CHAR_SET_NAME
COLLATION_CAT
COLLATION_SCHEM
COLLATION_NAME
UDT_CAT
UDT_SCHEM
UDT_NAME
DOMAIN_CAT
DOMAIN_SCHEM
DOMAIN_NAME
SCOPE_CAT
SCOPE_SCHEM
SCOPE_NAME
MAX_CARDINALITY
DTD_IDENTIFIER
IS_SELF_REF
Drivers capable of supplying any of those values should do so in the
corresponding column and supply undef values for the others.
Drivers wishing to provide extra database/driver specific
information should do so in extra columns beyond all those listed
above, and use lowercase field names with the driver-specific prefix
(i.e., 'ora_...'). Applications accessing such fields should do so
by name and not by column number.
The result set is ordered by TABLE_CAT, TABLE_SCHEM, TABLE_NAME and
ORDINAL_POSITION.
Note: There is some overlap with statement attributes (in perl) and
SQLDescribeCol (in ODBC). However, SQLColumns provides more
metadata.
See also the section on "Catalog Methods" and the section on
"Standards Reference Information".
"primary_key_info" *NEW*top
Warning: This method is experimental and may change.
$sth = $dbh->primary_key_info( $catalog, $schema, $table );
Returns an active statement handle that can be used to fetch
information about columns that make up the primary key for a table.
The arguments don't accept search patterns (unlike table_info()).
For example:
$sth = $dbh->primary_key_info( undef, $user, 'foo' );
$data = $sth->fetchall_arrayref;
Note: The support for the selection criteria, such as $catalog, is
driver specific. If the driver doesn't support catalogs and/or
schemas, it may ignore these criteria.
The statement handle returned has at least the following fields in
the order shown below. Other fields, after these, may also be
present.
TABLE_CAT: The catalog identifier. This field is NULL ("undef") if
not applicable to the data source, which is often the case. This
field is empty if not applicable to the table.
TABLE_SCHEM: The schema identifier. This field is NULL ("undef") if
not applicable to the data source, and empty if not applicable to
the table.
TABLE_NAME: The table identifier.
COLUMN_NAME: The column identifier.
KEY_SEQ: The column sequence number (starting with 1). Note: This
field is named ORDINAL_POSITION in SQL/CLI.
PK_NAME: The primary key constraint identifier. This field is NULL
("undef") if not applicable to the data source.
See also the section on "Catalog Methods" and the section on
"Standards Reference Information".
"primary_key" *NEW*top
Warning: This method is experimental and may change.
@key_column_names = $dbh->primary_key( $catalog, $schema, $table );
Simple interface to the primary_key_info() method. Returns a list of
the column names that comprise the primary key of the specified
table. The list is in primary key column sequence order.
"foreign_key_info" *NEW*top
Warning: This method is experimental and may change.
$sth = $dbh->foreign_key_info( $pk_catalog, $pk_schema, $pk_table
, $fk_catalog, $fk_schema, $fk_table );
Returns an active statement handle that can be used to fetch
information about foreign keys in and/or referencing the specified
table(s). The arguments don't accept search patterns (unlike
table_info()).
"$pk_catalog", "$pk_schema", "$pk_table" identify the primary
(unique) key table (PKT).
"$fk_catalog", "$fk_schema", "$fk_table" identify the foreign key
table (FKT).
If both PKT and FKT are given, the function returns the foreign key,
if any, in table FKT that refers to the primary (unique) key of
table PKT. (Note: In SQL/CLI, the result is implementation-defined.)
If only PKT is given, then the result set contains the primary key
of that table and all foreign keys that refer to it.
If only FKT is given, then the result set contains all foreign keys
in that table and the primary keys to which they refer. (Note: In
SQL/CLI, the result includes unique keys too.)
For example:
$sth = $dbh->foreign_key_info( undef, $user, 'master');
$sth = $dbh->foreign_key_info( undef, undef, undef , undef, $user, 'detail');
$sth = $dbh->foreign_key_info( undef, $user, 'master', undef, $user, 'detail');
Note: The support for the selection criteria, such as "$catalog", is
driver specific. If the driver doesn't support catalogs and/or
schemas, it may ignore these criteria.
The statement handle returned has the following fields in the order
shown below. Because ODBC never includes unique keys, they define
different columns in the result set than SQL/CLI. SQL/CLI column
names are shown in parentheses.
PKTABLE_CAT ( UK_TABLE_CAT ): The primary (unique) key table catalog
identifier. This field is NULL ("undef") if not applicable to the
data source, which is often the case. This field is empty if not
applicable to the table.
PKTABLE_SCHEM ( UK_TABLE_SCHEM ): The primary (unique) key table
schema identifier. This field is NULL ("undef") if not applicable to
the data source, and empty if not applicable to the table.
PKTABLE_NAME ( UK_TABLE_NAME ): The primary (unique) key table
identifier.
PKCOLUMN_NAME (UK_COLUMN_NAME ): The primary (unique) key column
identifier.
FKTABLE_CAT ( FK_TABLE_CAT ): The foreign key table catalog
identifier. This field is NULL ("undef") if not applicable to the
data source, which is often the case. This field is empty if not
applicable to the table.
FKTABLE_SCHEM ( FK_TABLE_SCHEM ): The foreign key table schema
identifier. This field is NULL ("undef") if not applicable to the
data source, and empty if not applicable to the table.
FKTABLE_NAME ( FK_TABLE_NAME ): The foreign key table identifier.
FKCOLUMN_NAME ( FK_COLUMN_NAME ): The foreign key column identifier.
KEY_SEQ ( ORDINAL_POSITION ): The column sequence number (starting
with 1).
UPDATE_RULE ( UPDATE_RULE ): The referential action for the UPDATE
rule. The following codes are defined:
CASCADE 0
RESTRICT 1
SET NULL 2
NO ACTION 3
SET DEFAULT 4
DELETE_RULE ( DELETE_RULE ): The referential action for the DELETE
rule. The codes are the same as for UPDATE_RULE.
FK_NAME ( FK_NAME ): The foreign key name.
PK_NAME ( UK_NAME ): The primary (unique) key name.
DEFERRABILITY ( DEFERABILITY ): The deferrability of the foreign key
constraint. The following codes are defined:
INITIALLY DEFERRED 5
INITIALLY IMMEDIATE 6
NOT DEFERRABLE 7
( UNIQUE_OR_PRIMARY ): This column is necessary if a driver
includes all candidate (i.e. primary and alternate) keys in the
result set (as specified by SQL/CLI). The value of this column is
UNIQUE if the foreign key references an alternate key and PRIMARY if
the foreign key references a primary key, or it may be undefined if
the driver doesn't have access to the information.
See also the section on "Catalog Methods" and the section on
"Standards Reference Information".
"tables" *NEW*top
Warning: This method is experimental and may change.
@names = $dbh->tables( $catalog, $schema, $table, $type );
@names = $dbh->tables; # deprecated
Simple interface to table_info(). Returns a list of matching table
names, possibly including a catalog/schema prefix.
See the table_info entry elsewhere in this document for a
description of the parameters.
If "$dbh-">"get_info(29)" returns true (29 is
SQL_IDENTIFIER_QUOTE_CHAR) then the table names are constructed and
quoted by the quote_identifier entry elsewhere in this document to
ensure they are usable even if they contain whitespace or reserved
words etc.
"type_info_all"top
Warning: This method is experimental and may change.
$type_info_all = $dbh->type_info_all;
Returns a reference to an array which holds information about each
data type variant supported by the database and driver. The array
and its contents should be treated as read-only.
The first item is a reference to an 'index' hash of "Name =">
"Index" pairs. The items following that are references to arrays,
one per supported data type variant. The leading index hash defines
the names and order of the fields within the arrays that follow it.
For example:
$type_info_all = [
{ TYPE_NAME => 0,
DATA_TYPE => 1,
COLUMN_SIZE => 2, # was PRECISION originally
LITERAL_PREFIX => 3,
LITERAL_SUFFIX => 4,
CREATE_PARAMS => 5,
NULLABLE => 6,
CASE_SENSITIVE => 7,
SEARCHABLE => 8,
UNSIGNED_ATTRIBUTE=> 9,
FIXED_PREC_SCALE => 10, # was MONEY originally
AUTO_UNIQUE_VALUE => 11, # was AUTO_INCREMENT originally
LOCAL_TYPE_NAME => 12,
MINIMUM_SCALE => 13,
MAXIMUM_SCALE => 14,
NUM_PREC_RADIX => 15,
},
[ 'VARCHAR', SQL_VARCHAR,
undef, "'","'", undef,0, 1,1,0,0,0,undef,1,255, undef
],
[ 'INTEGER', SQL_INTEGER,
undef, "", "", undef,0, 0,1,0,0,0,undef,0, 0, 10
],
];
Note that more than one row may have the same value in the
"DATA_TYPE" field if there are different ways to spell the type name
and/or there are variants of the type with different attributes
(e.g., with and without "AUTO_UNIQUE_VALUE" set, with and without
"UNSIGNED_ATTRIBUTE", etc).
The rows are ordered by "DATA_TYPE" first and then by how closely
each type maps to the corresponding ODBC SQL data type, closest
first.
The meaning of the fields is described in the documentation for the
the type_info entry elsewhere in this document method. The index
values shown above (e.g., "NULLABLE ="> "6") are for illustration
only. Drivers may define the fields with a different order.
This method is not normally used directly. The the type_info entry
elsewhere in this document method provides a more useful interface
to the data.
Even though an 'index' hash is provided, all the field names in the
index hash defined above will always have the index values defined
above. This is defined behaviour so that you don't need to rely on
the index hash, which is handy because the lettercase of the keys is
not defined. It is usually uppercase, as show here, but drivers are
free to return names with any lettercase. Drivers are also free to
return extra driver-specific columns of information - though it's
recommended that they start at column index 50 to leave room for
expansion of the DBI/ODBC specification.
"type_info"top
Warning: This method is experimental and may change.
@type_info = $dbh->type_info($data_type);
Returns a list of hash references holding information about one or
more variants of $data_type. The list is ordered by "DATA_TYPE"
first and then by how closely each type maps to the corresponding
ODBC SQL data type, closest first. If called in a scalar context
then only the first (best) element is returned.
If $data_type is undefined or "SQL_ALL_TYPES", then the list will
contain hashes for all data type variants supported by the database
and driver.
If $data_type is an array reference then "type_info" returns the
information for the *first* type in the array that has any matches.
The keys of the hash follow the same letter case conventions as the
rest of the DBI (see the Naming Conventions and Name Space entry
elsewhere in this document). The following items should exist:
TYPE_NAME (string)
Data type name for use in CREATE TABLE statements etc.
DATA_TYPE (integer)
SQL data type number.
COLUMN_SIZE (integer)
For numeric types, this is either the total number of digits (if
the NUM_PREC_RADIX value is 10) or the total number of bits
allowed in the column (if NUM_PREC_RADIX is 2).
For string types, this is the maximum size of the string in
bytes.
For date and interval types, this is the maximum number of
characters needed to display the value.
LITERAL_PREFIX (string)
Characters used to prefix a literal. A typical prefix is ""'""
for characters, or possibly ""0x"" for binary values passed as
hexadecimal. NULL ("undef") is returned for data types for which
this is not applicable.
LITERAL_SUFFIX (string)
Characters used to suffix a literal. Typically ""'"" for
characters. NULL ("undef") is returned for data types where this
is not applicable.
CREATE_PARAMS (string)
Parameter names for data type definition. For example,
"CREATE_PARAMS" for a "DECIMAL" would be ""precision,scale"" if
the DECIMAL type should be declared as
"DECIMAL("*precision,scale*")" where *precision* and *scale* are
integer values. For a "VARCHAR" it would be ""max length"". NULL
("undef") is returned for data types for which this is not
applicable.
NULLABLE (integer)
Indicates whether the data type accepts a NULL value: "0" or an
empty string = no, "1" = yes, "2" = unknown.
CASE_SENSITIVE (boolean)
Indicates whether the data type is case sensitive in collations
and comparisons.
SEARCHABLE (integer)
Indicates how the data type can be used in a WHERE clause, as
follows:
0 - Cannot be used in a WHERE clause
1 - Only with a LIKE predicate
2 - All comparison operators except LIKE
3 - Can be used in a WHERE clause with any comparison operator
UNSIGNED_ATTRIBUTE (boolean)
Indicates whether the data type is unsigned. NULL ("undef") is
returned for data types for which this is not applicable.
FIXED_PREC_SCALE (boolean)
Indicates whether the data type always has the same precision
and scale (such as a money type). NULL ("undef") is returned for
data types for which this is not applicable.
AUTO_UNIQUE_VALUE (boolean)
Indicates whether a column of this data type is automatically
set to a unique value whenever a new row is inserted. NULL
("undef") is returned for data types for which this is not
applicable.
LOCAL_TYPE_NAME (string)
Localized version of the "TYPE_NAME" for use in dialog with
users. NULL ("undef") is returned if a localized name is not
available (in which case "TYPE_NAME" should be used).
MINIMUM_SCALE (integer)
The minimum scale of the data type. If a data type has a fixed
scale, then "MAXIMUM_SCALE" holds the same value. NULL ("undef")
is returned for data types for which this is not applicable.
MAXIMUM_SCALE (integer)
The maximum scale of the data type. If a data type has a fixed
scale, then "MINIMUM_SCALE" holds the same value. NULL ("undef")
is returned for data types for which this is not applicable.
SQL_DATA_TYPE (integer)
This column is the same as the "DATA_TYPE" column, except for
interval and datetime data types. For interval and datetime data
types, the "SQL_DATA_TYPE" field will return "SQL_INTERVAL" or
"SQL_DATETIME", and the "SQL_DATETIME_SUB" field below will
return the subcode for the specific interval or datetime data
type. If this field is NULL, then the driver does not support or
report on interval or date subtypes.
SQL_DATETIME_SUB (integer)
For interval or datetime data types, where the "SQL_DATA_TYPE"
field above is "SQL_INTERVAL" or "SQL_DATETIME", this field will
hold the subcode for the specific interval or datetime data
type. Otherwise it will be NULL ("undef").
NUM_PREC_RADIX (integer)
The radix value of the data type. For approximate numeric types,
"NUM_PREC_RADIX" contains the value 2 and "COLUMN_SIZE" holds
the number of bits. For exact numeric types, "NUM_PREC_RADIX"
contains the value 10 and "COLUMN_SIZE" holds the number of
decimal digits. NULL ("undef") is returned either for data types
for which this is not applicable or if the driver cannot report
this information.
INTERVAL_PRECISION (integer)
The interval leading precision for interval types. NULL is
returned either for data types for which this is not applicable
or if the driver cannot report this information.
For example, to find the type name for the fields in a select
statement you can do:
@names = map { scalar $dbh->type_info($_)->{TYPE_NAME} } @{ $sth->{TYPE} }
Since DBI and ODBC drivers vary in how they map their types into the
ISO standard types you may need to search for more than one type.
Here's an example looking for a usable type to store a date:
$my_date_type = $dbh->type_info( [ SQL_DATE, SQL_TIMESTAMP ] );
Similarly, to more reliably find a type to store small integers, you
could use a list starting with "SQL_SMALLINT", "SQL_INTEGER",
"SQL_DECIMAL", etc.
See also the section on "Standards Reference Information".
"quote"top
$sql = $dbh->quote($value);
$sql = $dbh->quote($value, $data_type);
Quote a string literal for use as a literal value in an SQL
statement, by escaping any special characters (such as quotation
marks) contained within the string and adding the required type of
outer quotation marks.
$sql = sprintf "SELECT foo FROM bar WHERE baz = %s",
$dbh->quote("Don't");
For most database types, quote would return "'Don''t'" (including
the outer quotation marks).
An undefined "$value" value will be returned as the string "NULL"
(without single quotation marks) to match how NULLs are represented
in SQL.
If "$data_type" is supplied, it is used to try to determine the
required quoting behavior by using the information returned by the
type_info entry elsewhere in this document. As a special case, the
standard numeric types are optimized to return "$value" without
calling "type_info".
Quote will probably *not* be able to deal with all possible input
(such as binary data or data containing newlines), and is not
related in any way with escaping or quoting shell meta-characters.
There is no need to quote values being used with the section on
"Placeholders and Bind Values".
"quote_identifier"top
$sql = $dbh->quote_identifier( $name );
$sql = $dbh->quote_identifier( $name1, $name2, $name3, \%attr );
Quote an identifier (table name etc.) for use in an SQL statement,
by escaping any special characters (such as double quotation marks)
it contains and adding the required type of outer quotation marks.
Undefined names are ignored and the remainder are quoted and then
joined together, typically with a dot (".") character. For example:
$id = $dbh->quote_identifier( undef, 'Her schema', 'My table' );
would, for most database types, return ""Her schema"."My table""
(including all the double quotation marks).
If three names are supplied then the first is assumed to be a
catalog name and special rules may be applied based on what the
get_info entry elsewhere in this document returns for
SQL_CATALOG_NAME_SEPARATOR (41) and SQL_CATALOG_LOCATION (114). For
example, for Oracle:
$id = $dbh->quote_identifier( 'link', 'schema', 'table' );
would return ""schema"."table"@"link"".
Database Handle Attributes
This section describes attributes specific to database handles.
Changes to these database handle attributes do not affect any other
existing or future database handles.
Attempting to set or get the value of an unknown attribute is fatal,
except for private driver-specific attributes (which all have names
starting with a lowercase letter).
Example:
$h->{AutoCommit} = ...; # set/write
... = $h->{AutoCommit}; # get/read
"AutoCommit" (boolean)
If true, then database changes cannot be rolled-back (undone). If
false, then database changes automatically occur within a
"transaction", which must either be committed or rolled back using
the "commit" or "rollback" methods.
Drivers should always default to "AutoCommit" mode (an unfortunate
choice largely forced on the DBI by ODBC and JDBC conventions.)
Attempting to set "AutoCommit" to an unsupported value is a fatal
error. This is an important feature of the DBI. Applications that
need full transaction behavior can set "$dbh-">"{AutoCommit} = 0"
(or set "AutoCommit" to 0 via the connect entry elsewhere in this
document) without having to check that the value was assigned
successfully.
For the purposes of this description, we can divide databases into
three categories:
Databases which don't support transactions at all.
Databases in which a transaction is always active.
Databases in which a transaction must be explicitly started (C<'BEGIN WORK'>).
* Databases which don't support transactions at all
For these databases, attempting to turn "AutoCommit" off is a fatal
error. "commit" and "rollback" both issue warnings about being
ineffective while "AutoCommit" is in effect.
* Databases in which a transaction is always active
These are typically mainstream commercial relational databases with
"ANSI standard" transaction behavior. If "AutoCommit" is off, then
changes to the database won't have any lasting effect unless the
commit entry elsewhere in this document is called (but see also the
disconnect entry elsewhere in this document). If the rollback entry
elsewhere in this document is called then any changes since the last
commit are undone.
If "AutoCommit" is on, then the effect is the same as if the DBI
called "commit" automatically after every successful database
operation. So calling "commit" or "rollback" explicitly while
"AutoCommit" is on would be ineffective because the changes would
have already been commited.
Changing "AutoCommit" from off to on will trigger a the commit entry
elsewhere in this document.
For databases which don't support a specific auto-commit mode, the
driver has to commit each statement automatically using an explicit
"COMMIT" after it completes successfully (and roll it back using an
explicit "ROLLBACK" if it fails). The error information reported to
the application will correspond to the statement which was executed,
unless it succeeded and the commit or rollback failed.
* Databases in which a transaction must be explicitly started
For these databases, the intention is to have them act like
databases in which a transaction is always active (as described
above).
To do this, the driver will automatically begin an explicit
transaction when "AutoCommit" is turned off, or after a the commit
entry elsewhere in this document or the rollback entry elsewhere in
this document (or when the application issues the next database
operation after one of those events).
In this way, the application does not have to treat these databases
as a special case.
See the commit, disconnect, and Transactions entries elsewhere in
this document for other important notes about transactions.
"Driver" (handle)
Holds the handle of the parent driver. The only recommended use for
this is to find the name of the driver using:
$dbh->{Driver}->{Name}
"Name" (string)
Holds the "name" of the database. Usually (and recommended to be)
the same as the ""dbi:DriverName:..."" string used to connect to the
database, but with the leading ""dbi:DriverName:"" removed.
"Statement" (string, read-only)
Returns the statement string passed to the most recent the prepare
entry elsewhere in this document method called in this database
handle, even if that method failed. This is especially useful where
"RaiseError" is enabled and the exception handler checks $@ and sees
that a 'prepare' method call failed.
"RowCacheSize" (integer)
A hint to the driver indicating the size of the local row cache that
the application would like the driver to use for future "SELECT"
statements. If a row cache is not implemented, then setting
"RowCacheSize" is ignored and getting the value returns "undef".
Some "RowCacheSize" values have special meaning, as follows:
0 - Automatically determine a reasonable cache size for each C