![]() |
|
[ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] | [Top] | [Contents] | [Index] | [ ? ] |
This section documents the Perl DBI
interface. The former interface
was called mysqlperl
. DBI
/DBD
now is the
recommended Perl interface, so mysqlperl
is obsolete and is not
documented here.
9.5.1 DBI with DBD::mysql | ||
9.5.2 The DBI Interface | ||
9.5.3 More DBI /DBD Information |
DBI
with DBD::mysql
DBI
is a generic interface for many databases. That means that
you can write a script that works with many different database engines
without change. You need a DataBase Driver (DBD) defined for each
database type. For MySQL, this driver is called
DBD::mysql
.
For more information on the Perl5 DBI, please visit the DBI
web
page and read the documentation:
http://dbi.perl.org/ |
Note that if you want to use transactions with Perl, you need to have
DBD-mysql
version 1.2216 or newer. Version 2.1022 or newer
is recommended.
Installation instructions for MySQL Perl support are given in Perl Installation Comments.
If you have the MySQL module installed, you can find information about specific MySQL functionality with one of the following command
shell> |
DBI
Interface Portable DBI Methods and Attributes
Method/Attribute | Description |
| Establishes a connection to a database server. |
| Disconnects from the database server. |
| Prepares an SQL statement for execution. |
| Executes prepared statements. |
| Prepares and executes an SQL statement. |
| Quotes string or |
| Fetches the next row as an array of fields. |
| Fetches next row as a reference array of fields. |
| Fetches next row as a reference to a hashtable. |
| Fetches all data as an array of arrays. |
| Finishes a statement and lets the system free resources. |
| Returns the number of rows affected. |
| Returns an array of databases available on localhost. |
| Controls whether |
| The number of placeholders in the prepared statement. |
| Which columns can be |
| Perform tracing for debugging. |
MySQL-specific Methods and Attributes
Method/Attribute | Description |
| The latest |
| Which columns are |
| Which columns are keys. |
| Which columns are numeric. |
| Which columns are primary keys. |
| Which columns CANNOT be |
| Maximum possible column sizes. |
| Maximum column sizes actually present in result. |
| Column names. |
| Number of fields returned. |
| Table names in returned set. |
| All column types. |
The Perl methods are described in more detail in the following sections. Variables used for method return values have these meanings:
$dbh
Database handle
$sth
Statement handle
$rc
Return code (often a status)
$rv
Return value (often a row count)
Portable DBI Methods and Attributes
connect($data_source, $username, $password)
Use the connect
method to make a database connection to the data
source. The $data_source
value should begin with
DBI:driver_name:
.
Example uses of connect
with the DBD::mysql
driver:
$dbh = DBI->connect("DBI:mysql:$database", $user, $password); $dbh = DBI->connect("DBI:mysql:$database:$hostname", $user, $password); $dbh = DBI->connect("DBI:mysql:$database:$hostname:$port", $user, $password); |
If the user name and/or password are undefined, DBI
uses the
values of the DBI_USER
and DBI_PASS
environment variables,
respectively. If you don't specify a hostname, it defaults to
'localhost'
. If you don't specify a port number, it defaults to the
default MySQL port (3306).
As of Msql-Mysql-modules
Version 1.2009,
the $data_source
value allows certain modifiers:
mysql_read_default_file=file_name
Read `file_name' as an option file. For information on option files, see `my.cnf' Option Files.
mysql_read_default_group=group_name
The default group when reading an option file is normally the
[client]
group. By specifying the mysql_read_default_group
option, the default group becomes the [group_name]
group.
mysql_compression=1
Use compressed communication between the client and server (MySQL Version 3.22.3 or later).
mysql_socket=/path/to/socket
Specify the pathname of the Unix socket that is used to connect to the server (MySQL Version 3.21.15 or later).
Multiple modifiers may be given; each must be preceded by a semicolon.
For example, if you want to avoid hardcoding the user name and password into
a DBI
script, you can take them from the user's `~/.my.cnf'
option file instead by writing your connect
call like this:
$dbh = DBI->connect("DBI:mysql:$database" . ";mysql_read_default_file=$ENV{HOME}/.my.cnf", $user, $password); |
This call will read options defined for the [client]
group in the
option file. If you wanted to do the same thing but use options specified
for the [perl]
group as well, you could use this:
$dbh = DBI->connect("DBI:mysql:$database" . ";mysql_read_default_file=$ENV{HOME}/.my.cnf" . ";mysql_read_default_group=perl", $user, $password); |
disconnect
The disconnect
method disconnects the database handle from the database.
This is typically called right before you exit from the program.
Example:
$rc = $dbh->disconnect; |
prepare($statement)
Prepares an SQL statement for execution by the database engine
and returns a statement handle ($sth)
, which you can use to invoke
the execute
method.
Typically you handle SELECT
statements (and SELECT
-like
statements such as SHOW
, DESCRIBE
, and EXPLAIN
) by
means of prepare
and execute
. Example:
$sth = $dbh->prepare($statement) or die "Can't prepare $statement: $dbh->errstr\n"; |
If you want to read big results to your client you can tell Perl to use
mysql_use_result()
with:
my $sth = $dbh->prepare($statement { "mysql_use_result" => 1}); |
execute
The execute
method executes a prepared statement. For
non-SELECT
statements, execute
returns the number of rows
affected. If no rows are affected, execute
returns "0E0"
,
which Perl treats as zero but regards as true. If an error occurs,
execute
returns undef
. For SELECT
statements,
execute
only starts the SQL query in the database; you need to use one
of the fetch_*
methods described here to retrieve the data.
Example:
$rv = $sth->execute or die "can't execute the query: " . $sth->errstr; |
do($statement)
The do
method prepares and executes an SQL statement and returns the
number of rows affected. If no rows are affected, do
returns
"0E0"
, which Perl treats as zero but regards as true. This method is
generally used for non-SELECT
statements that cannot be prepared in
advance (due to driver limitations) or that do not need to be executed more
than once (inserts, deletes, etc.). Example:
$rv = $dbh->do($statement) or die "Can't execute $statement: $dbh- >errstr\n"; |
Generally the 'do' statement is much faster (and is preferable) than prepare/execute for statements that don't contain parameters.
quote($string)
The quote
method is used to "escape" any special characters contained in
the string and to add the required outer quotation marks.
Example:
$sql = $dbh->quote($string) |
fetchrow_array
This method fetches the next row of data and returns it as an array of field values. Example:
while(@row = $sth->fetchrow_array) { print qw($row[0]\t$row[1]\t$row[2]\n); } |
fetchrow_arrayref
This method fetches the next row of data and returns it as a reference to an array of field values. Example:
while($row_ref = $sth->fetchrow_arrayref) { print qw($row_ref->[0]\t$row_ref->[1]\t$row_ref->[2]\n); } |
fetchrow_hashref
This method fetches a row of data and returns a reference to a hash table containing field name/value pairs. This method is not nearly as efficient as using array references as demonstrated above. Example:
while($hash_ref = $sth->fetchrow_hashref) { print qw($hash_ref->{firstname}\t$hash_ref->{lastname}\t\ $hash_ref->{title}\n); } |
fetchall_arrayref
This method is used to get all the data (rows) to be returned from the SQL statement. It returns a reference to an array of references to arrays for each row. You access or print the data by using a nested loop. Example:
my $table = $sth->fetchall_arrayref or die "$sth->errstr\n"; my($i, $j); for $i ( 0 .. $#{$table} ) { for $j ( 0 .. $#{$table->[$i]} ) { print "$table->[$i][$j]\t"; } print "\n"; } |
finish
Indicates that no more data will be fetched from this statement handle. You call this method to free up the statement handle and any system resources associated with it. Example:
$rc = $sth->finish; |
rows
Returns the number of rows changed (updated, deleted, etc.) by the last
command. This is usually used after a non-SELECT
execute
statement. Example:
$rv = $sth->rows; |
NULLABLE
Returns a reference to an array of values that indicate whether columns may
contain NULL
values.
The possible values for each array element are 0 or the empty string if the
column cannot be NULL
, 1 if it can, and 2 if the column's NULL
status is unknown.
Example:
$null_possible = $sth->{NULLABLE}; |
NUM_OF_FIELDS
This attribute indicates
the number of fields returned by a SELECT
or SHOW FIELDS
statement. You may use this for checking whether a statement returned a
result: A zero value indicates a non-SELECT
statement like
INSERT
, DELETE
, or UPDATE
.
Example:
$nr_of_fields = $sth->{NUM_OF_FIELDS}; |
data_sources($driver_name)
This method returns an array containing names of databases available to the
MySQL server on the host 'localhost'
.
Example:
@dbs = DBI->data_sources("mysql"); |
ChopBlanks
This attribute determines whether the fetchrow_*
methods will chop
leading and trailing blanks from the returned values.
Example:
$sth->{'ChopBlanks'} =1; |
trace($trace_level)
trace($trace_level, $trace_filename)
The trace
method enables or disables tracing. When invoked as a
DBI
class method, it affects tracing for all handles. When invoked as
a database or statement handle method, it affects tracing for the given
handle (and any future children of the handle). Setting $trace_level
to 2 provides detailed trace information. Setting $trace_level
to 0
disables tracing. Trace output goes to the standard error output by
default. If $trace_filename
is specified, the file is opened in
append mode and output for all traced handles is written to that
file. Example:
DBI->trace(2); # trace everything DBI->trace(2,"/tmp/dbi.out"); # trace everything to # /tmp/dbi.out $dth->trace(2); # trace this database handle $sth->trace(2); # trace this statement handle |
You can also enable DBI
tracing by setting the DBI_TRACE
environment variable. Setting it to a numeric value is equivalent to calling
DBI->(value)
. Setting it to a pathname is equivalent to calling
DBI->(2,value)
.
MySQL-specific Methods and Attributes
The methods shown here are MySQL-specific and not part of the
DBI
standard. Several of them are now deprecated:
is_blob
, is_key
, is_num
, is_pri_key
,
is_not_null
, length
, max_length
, and table
.
Where DBI
-standard alternatives exist, they are noted here:
mysql_insertid
If you use the AUTO_INCREMENT
feature of MySQL, the new
auto-incremented values will be stored here.
Example:
$new_id = $sth->{mysql_insertid}; |
With old versions of the DBI interface, you could use $sth->{'insertid'}
.
is_blob
Returns a reference to an array of boolean values; for each element of the
array, a value of TRUE indicates that the
respective column is a BLOB
.
Example:
$keys = $sth->{is_blob}; |
is_key
Returns a reference to an array of boolean values; for each element of the array, a value of TRUE indicates that the respective column is a key. Example:
$keys = $sth->{is_key}; |
is_num
Returns a reference to an array of boolean values; for each element of the array, a value of TRUE indicates that the respective column contains numeric values. Example:
$nums = $sth->{is_num}; |
is_pri_key
Returns a reference to an array of boolean values; for each element of the array, a value of TRUE indicates that the respective column is a primary key. Example:
$pri_keys = $sth->{is_pri_key}; |
is_not_null
Returns a reference to an array of boolean values; for each element of the
array, a value of FALSE indicates that this column may contain NULL
values.
Example:
$not_nulls = $sth->{is_not_null}; |
is_not_null
is deprecated; it is preferable to use the
NULLABLE
attribute (described above), because that is a DBI standard.
length
max_length
Each of these methods returns a reference to an array of column sizes. The
length
array indicates the maximum possible sizes that each column may
be (as declared in the table description). The max_length
array
indicates the maximum sizes actually present in the result table. Example:
$lengths = $sth->{length}; $max_lengths = $sth->{max_length}; |
NAME
Returns a reference to an array of column names. Example:
$names = $sth->{NAME}; |
table
Returns a reference to an array of table names. Example:
$tables = $sth->{table}; |
type
Returns a reference to an array of column types. Example:
$types = $sth->{type}; |
DBI
/DBD
Information You can use the perldoc
command to get more information about
DBI
.
perldoc DBI perldoc DBI::FAQ perldoc DBD::mysql |
You can also use the pod2man
, pod2html
, etc., tools to
translate to other formats.
You can find the latest DBI
information at
the DBI
web page: http://dbi.perl.org/.
[ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] |
Hosting by: Hurra Communications Ltd.
Generated: 2007-01-26 17:58:46