GNU Info
(mysql.info)Perl DBI Class
The `DBI' Interface
-------------------
*Portable DBI Methods*
`connect' Establishes a connection to a database server.
`disconnect' Disconnects from the database server.
`prepare' Prepares a SQL statement for execution.
`execute' Executes prepared statements.
`do' Prepares and executes a SQL statement.
`quote' Quotes string or `BLOB' values to be inserted.
`fetchrow_array' Fetches the next row as an array of fields.
`fetchrow_arrayref' Fetches next row as a reference array of fields.
`fetchrow_hashref' Fetches next row as a reference to a hashtable.
`fetchall_arrayref' Fetches all data as an array of arrays.
`finish' Finishes a statement and lets the system free
resources.
`rows' Returns the number of rows affected.
`data_sources' Returns an array of databases available on
localhost.
`ChopBlanks' Controls whether `fetchrow_*' methods trim spaces.
`NUM_OF_PARAMS' The number of placeholders in the prepared
statement.
`NULLABLE' Which columns can be `NULL'.
`trace' Perform tracing for debugging.
*MySQL-specific Methods*
`insertid' The latest `AUTO_INCREMENT' value.
`is_blob' Which columns are `BLOB' values.
`is_key' Which columns are keys.
`is_num' Which columns are numeric.
`is_pri_key' Which columns are primary keys.
`is_not_null' Which columns CANNOT be `NULL'. See `NULLABLE'.
`length' Maximum possible column sizes.
`max_length' Maximum column sizes actually present in result.
`NAME' Column names.
`NUM_OF_FIELDS' Number of fields returned.
`table' Table names in returned set.
`type' 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*
`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 `filename' as an option file. For information on option
files, see Note: 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 a 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";
`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 below 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 a 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 boolean values; for each
element of the array, a value of TRUE indicates that this column
may contain `NULL' values. 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*
The methods shown below 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 below:
`insertid'
If you use the `AUTO_INCREMENT' feature of MySQL, the new
auto-incremented values will be stored here. Example:
$new_id = $sth->{insertid};
As an alternative, you can use `$dbh->{'mysql_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};
automatically generated by info2www version 1.2.2.9