MySQL Reference Manual

MySQL Perl API

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.

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://www.symbolstone.org/technology/perl/DBI/

For more information on object oriented programming (OOP) as defined in Perl5, see the Perl OOP page:

http://language.perl.com/info/documentation.html

Note that if you want to use transactions with Perl, you need to have Msql-Mysql-modules Version 1.2216 or newer.

Installation instructions for MySQL Perl support are given in Section 2.7.

The DBI Interface

Portable DBI methods

Method	Description
connect	Establishes a connection to a database server
disconnect	Disconnects from the database server
prepare	Prepares an SQL statement for execution
execute	Executes prepared statements
do	Prepares and executes an 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 the next row as a reference array of fields
fetchrow_hashref	Fetches the 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

Method	Description
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 username 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 Section 4.1.2.

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 username 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";

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 later 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 earlier. 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 following methods 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:

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 earlier), 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};

More 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://www.symbolstone.org/technology/perl/DBI/