20.5 MySQL Perl API

This section documents the Perl DBI interface. The former interface was called mysqlperl. Since DBI/DBD now is the recommended Perl interface, mysqlperl is obsolete and is not documented here.

20.5.1 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/index.html

For more information on Object Oriented Programming (OOP) as defined in Perl5, see the Perl OOP page:

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

Installation instructions for MySQL Perl support are given in 4.10 Perl installation comments.

20.5.2 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 let 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 column 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 4.15.4 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 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 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. 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 which cannot be prepared in advance (due to driver limitations) or which do not need to executed more than once (inserts, deletes, etc.). Example:
$rv = $dbh->do($statement)
        or die "Can't execute $statement: $dbh- >errstr\n";
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), since 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};

20.5.3 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.

And of course you can find the latest DBI information at the DBI web page:

http://www.symbolstone.org/technology/perl/DBI/index.html