Note from Mike Miller: Please note that only minimal updates have been made to this document. I fixed a typo or two, made a minor corrections and fixed the example to work with the latest version of MySQL/DBD/DBI, etc.

1. Just type: perldoc DBI and/or perldoc mysql
2. Use the POD converters: pod2man, pod2html, pod2text, pod2laytex
i.e. pod2html DBI
Last but not least, the modules tar file you downloaded has excellent code examples used to test the modules installation. Usually found in the ./t directory (relative to the directory you untar'd it in).

• THE DBI CLASS
• DBI Class Methods 

  ---

• connect
$dbh = DBI->connect("DBI:mysql:$database", undef, undef);
$dbh = DBI->connect("DBI:mysql:$database", $username, $password);
$dbh=DBI->connect("DBI:mysql:$database:$hostname",$username, $password);
$dbh = DBI->connect("DBI:mysql:$database:$hostname:$port",
$username, $password);

You use the connect method to make a database connection to the data source. The $data_source value should begin with 'DBI:driver_name:'.  If the username and/or password are undefined, then the DBI will use the values of the DBI_USER, DBI_PASS environment variables respectively. If you don't specify a hostname, then it will default to "localhost". If you don't specify a port, then it defaults to the default mysql port (3306).
Here is a short example connect script:

#!/usr/bin/perl -w
use DBI;
use strict;
my $database = "ContactDB";
my $data_source = "DBI:mysql:$database";
my $username = "fty";
my $password = "password";
my $dbh = DBI->connect( $data_source, $username, $password)
or die "Can't connect to $data_source: $dbh->errstr\n";
$dbh->disconnect;
exit(0);
__END__


---

• available_drivers
@drivers = DBI->available_drivers;
@drivers = DBI->available_drivers($quiet);
 
This method returns an array of available drivers by searching through the DBD::* directories included in the @INC array. A warning will be given if some drivers are hidden by drivers of the same name in a previous directory. to inhibit the warning set $quiet to 1 (true value) as the methods paramater. Example:

#!/usr/bin/perl -w
use DBI;
use strict;
my $quiet = 1;
my @drivers = DBI->available_drivers($quiet);
print "Available Drivers:\n";
for(@drivers) {
    print "$_\n";
}
exit;
__END__


---

• data_sources
@databases = DBI->data_sources($driver);

This method returns an array of databases available for the named driver ($driver). If $driver is omitted then the environment variable DBI_DRIVER is used. Example:

#!/usr/bin/perl -w
use DBI;
use strict;
my $quiet = 1;
my ($driver,$database);
my @drivers = DBI->available_drivers($quiet);
print "Available Databases:\n";
foreach $driver(@drivers) {
    print "_____________________\n";
    print "$driver:\n";
    print "_____________________\n";
    @databases = DBI->data_sources($driver);
    foreach $database(@databases) {
        print "$database\n";
    }
print "\n";
}
exit;
__END__


---

• trace
DBI->trace($trace_level);
DBI->trace($trace_level, $trace_file);

DBI trace information can be enables for all hadles using this method. To enable it for a specific handle use the similar $dbh->trace handle method.Set $trace_level to 2 to see detailed information including parameters and return values. Set $trace_level to 0 to disable trace. If $trace_file is specified then all trace information is appended to this file. Example:

#!/usr/bin/perl -w
use DBI;
use strict;
my $trace_level = 2;
$DBI->trace($trace_level);
my $dbh = DBI->connect("DBI:mysql:contact",undef,undef)
or die "Unable to connect to Contacts Database: $dbh->errstr\n";
my $sth = $dbh->prepare("SELECT * FROM contact");
$sth->execute or die "Unable to execute query: $dbh->errstr\n";
$sth->finish;
$dbh->disconnect;
exit;
__END__


---

• DBI Utility Functions
• neat
$str = DBI::neat($value, $maxlen);

Returns a neat (formatted for human consumption) representation of the supplied value. This function is used internally by the DBI for trace output. Should not be used for formatting values for database use.


---

• neat_list
$str = DBI::neat_list(\@listref, $maxlen, $field_sep);

Calls DBI::neat on each element of @listref and returns a string of the results joined by $field_sep. Should not be used for formatting values for database use.


---

• dump_results
$rows = DBI::dump_results($sth, maxlen, $lsep, $fsep, $fh);

This function fetches all the rows from the statemnent handle ($sth), calls the function DBI::neat_list for each row and prints the results to the filehandle ($fh). $fh defaults to STDOUT, line separator ($lsep) to '\n', field separator ($fsep) to ',' , and maximum length ($maxlen) to 35. Example:

#!/usr/bin/perl -w
use DBI;
use strict;
my $dbh = DBI->connect("DBI:mysql:contact",undef,undef)
or die "Unable to connect to Contacts Database: $dbh->errstr\n";
my $sth = $dbh->prepare("SELECT * FROM contacts");
DBI::dump_results($sth) or die "$DBI::errstr\n";
$sth->finish;
$dbh->disconnect;
exit;
__END__


---

• DBI Dynamic Attributes

All of the dynamic attributes below are volitile. That is, they have a short life span because they are always associated with the last handle used. Because of this they should be used immediately after calling the method that set them. There may also be a problem with the multi-threading in Perl 5.005. It is better to use the equivilent attributes mentioned below.
 
• $DBI::err
Equivilent to $dbh->err
• $DBI::errstr
Equivilent to $dbh->errstr
• $DBI::state
Equivilent to $dbh->state
• $DBI::rows
Equivilent to $dbh->rows (Should this be $sth->rows?)


---

• Methods Common to All Handles
• err
$err = $dbh->err;

This method returns the native database engine error code from the last driver function called


---

• errstr
$errstr = $dbh->errstr;

This method returns the native database engine error message from the last driver function called


---

• state
$state = $dbh->state;

?Not supported by mySQL?


---

• trace
$dbh->trace($trace_level);

Very similar to DBI::trace with one exception. The trace is only associated with the specific handle it is used with.


---

• func
$dbh->func(@func_arguments, $func_name);

This method is used to call private methods implemented by the driver (DBD::*)


---


Example using the above methods:

#!/usr/bin/perl -w
use DBI;
use strict;
my $trace_level = 2;
my $dbh = DBI->connect("DBI:mysql:contact",undef,undef)
$dbh->trace($trace_level);
or die "Unable to connect to contact Database: $dbh->errstr\n";
my $sth = $dbh->prepare("SELECT * FROM contact");
$sth->execute or die "Unable execute query:$dbh->err, $dbh->errstr\n";
$sth->finish;
@tables = $dbh->func('_ListTables')
or die "Unable to list tables: $dbh->errstr\n";
foreach $table(@tables) {
    print "$table\n";
}
$dbh->disconnect;
exit;
__END__


---

• Attributes Common to All Handles
Most of these attributes are inheritable. That is, thier child handles will inherit the attribute set by theier parent (i.e statement handles will inherit from the database handle that created them).

$dbh->{AttributeName} = 1;
$value = $dbh->{AttributeName};
 

• Warn (boolean, inherited)
 Enables useful warning. Enabled by default.


---

• CompatMode (boolean, inherited)
Used by emulation layers to enable compatible behavior in the driver fort this handle. Not applicable to the mySQL driver.


---

• InactiveDestroy (boolean)
This attribute is used to disable the effects of destroying a handle. It is specifically designed for unix applications that fork child processes. Either the parent or the child process should set this attribute on all thier handles, but not both.


---

• PrintError (boolean, inherited)
This attribute is used to force errors to generate warnings in addition to returning error codes. By default DBI->connect sets PrintError on.


---

• RaiseError (boolean, inherited)
This attribute is used to force errors to raise exceptions rather than just return error codes. Default is set to off.


---

• ChopBlanks (boolean, inherited)
This attribute is used to control the trimming of trailing spaces from fixed width fields. Default is false.


---

• LongReadLen (integer, inherited)
 This attribute is used to control the maximum length of blob fields. A value of 0 means don't automatically fetch fetch long data (fetch should return undef for blob when set to 0). Default is usually 0 but may vary between drivers. This attribute must be set before a statement is prepared.


---

• LongTruncOk (boolean, inherited)
This attribute is used to control how a fetch handles blob fields that have been truncated (due to the field value being longer than what is set with the LongReadLen attribute). Default is false and will cause the fetch to fail. Many drivers will allow you to continue fetching further rows when this attribute is set to false.


---

• Database Handle Methods
• prepare
$sth = $dbh->prepare($statement);
$sth = $dbh->prepare($statement, \%attr);

This method prepares a single SQL statement for execution and returns a reference to a statement handle ($sth) to be used to get attributes of the statement as well as to execute the statement ($sth->execute). Some drivers only store the statement in the handle and can only give useful information (NAME, NUM_OF_FIELDS) after the execute method has beed called.


---

• do
$rc = $dbh->do($statement);
$rc = $dbh->do($statement, \%attr);
$rc = $dbh->do($statement, \%attr, @bind_values);

This method prepares and executes a statement. Returns the number of rows affected, or -1 if not known, or undef on error. This method is usually used for non-select statements which can not be prepared in advance or do not need to be executed repeatedly.


---

• commit
$dbh->commit; (Not supported by mySQL)


---

• rollback
$dbh->rollback; (Not supported by mySQL)


---

• disconnect
$dbh->disconnect;

This method disconnects the database from the database handle. Normally called just before exiting the application. If you call this method while there are active statement handles you will get a warning. Use the finish method ($sth->finish) for each statement handle defined.


---

• ping
$result = $dbh->ping;

This method checks to see if the database server is still running and the connection is still working. Not generally used. See the Apache::DBI module for example usage.


---

• quote
$sql = $dbh->quote($string);

This method escapes special characters (quotation marks, etc.) from strings and adds the required outer quoatation marks. May not be able to handle all types of input (i.e binary data).


---


Example using the above methods:

#!/usr/bin/perl -w
use DBI;
use strict;
my $dbh = DBI->connect("DBI:mysql:contacts",undef,undef)
or die "Unable to connect to contacts Database: $dbh->errstr\n";
my $sth = $dbh->prepare("SELECT uid FROM contact WHERE last_name = 'Flaherty'");
$sth->execute or die "Unable to execute query: $dbh->errstr\n";
my $row = $sth->fetchrow_arrayref;
my $uid = $row->[0];
$sth->finish;
my $newname = $dbh->quote("The Flahertys'");
my $statement =qq(UPDATE contact SET last_name = '$newname'
                  WHERE uid = $uid);
my $rc = $dbh->do($statement) or die "Unable to prepare/execute $statement: $dbh->errstr\n";
print "$rc rows were updated\n";
$dbh->disconnect;
exit;
__END__


---

• Database Handle Attributes
• AutoCommit
$dbh->{AutoCommit} = 1;
$value = $dbh->{AutoCommit}; (Not supported by mySQL)


---

• Satement Handle Methods
• bind_param
$sth->bind_param($param_num, $bind_value);
$sth->bind_param($param_num, $bind_value, \%attr);
$sth->bind_param($param_num, $bind_value, $bind_type);

This method is used to assign a value with a placeholder (indicated with a question mark) in a prepared statement. %attr can be used to specify the data type for the placeholder. i.e

$sth->bind_param(1, $value, { TYPE => SQL_INTEGER });

A shortcut is to use the bind type directly in place of the hash reference. i.e:

$sth->bind_param(1, $value, SQL_INTEGER);

Example:

#!/usr/bin/perl -w
use DBI;
use strict;
my $dbh = DBI->connect("DBI:mysql:contacts",undef,undef)
or die "Unable to connect to contacts Database: $dbh->errstr\n";
my @letters = [A..Z];
my $i;
$dbh->{RaiseError} = 1;
my $sth = $dbh->prepare("SELECT first_name, last_name
                         FROM contact
                         WHERE last_name LIKE ?");
for($i = 1; $i <= 26, $i++) {
    $sth->bind_param($i, "@letters[$i]%");
    $sth->execute;
    print qq(
    Last Name beginning with @letters[$i]:\n
    FIRST                    LAST\n
    -----                    ----\n
    while(my $row = $sth->fetchrow_arrayref) {
        my($first,$last) = @$row;
        print qq(
        $first                   $last\n
    }
    print "\n";
    $sth->finish;
}
$dbh->disconnect;
exit;
__END__


---

• bind_param_inout
$sth->bind_param_inout($param_num, \$bind_value, $max_len);
$sth->bind_param_inout($param_num, \$bind_value, $max_len, \%attr);
$sth->bind_param_inout($param_num, \$bind_value, $max_len, $bind_type); (not supported in mySQL)


---

• execute
$rc = $sth->execute;
$rc = $sth->execute(@bind_values);

This method executes the prepared statement and returns true if successful and undef if an error occured. For non-select statement (UPDATE, INSERT, etc.) the return value is the number of rows affected.  Zero rows is returned as '0E0' which perl treats as '0' but regards as true. For select statements execute just starts the query engine. You need to use one of the fetch methods below to retrieve the data. If you pass any arguments then execute will call bind_param for each value, and set the type to SQL_VARCHAR. Example:

#!/usr/bin/perl -w
use DBI;
use strict;
my $dbh = DBI->connect("DBI:mysql:contacts",undef,undef)
or die "Unable to connect to contacts Database: $dbh->errstr\n";
my @names = ['Flaherty', 'Jones', 'Smith'];
$dbh->{RaiseError} = 1;
my $sth = $dbh->prepare("UPDATE contact
                         SET phone = '555-1212'
                         WHERE last_name = ?");
$sth->execute(@names);
$sth->finish;
$dbh->disconnect;
exit;
__END__


---

• fetchrow_arrayref
$row = $sth->fetchrow_arrayref;

This method fetches the next row of data and returns a reference to an array of field values. If there are no more rows to fetch then it returns undef.When used in conjuction with the bind_columns method, it is the fastest way to fetch data. Example:

#!/usr/bin/perl -w
use DBI;
use strict;
my $dbh = DBI->connect("DBI:mysql:contacts",undef,undef)
or die "Unable to connect to contacts Database: $dbh->errstr\n";
$dbh->{RaiseError} = 1;
my $sth = $dbh->prepare("SELECT first_name, last_name
                         FROM contact");
$sth->execute or
die "Unable to execute query: $dbh->errstr\n";
my $row;
while($row = $sth->fetchrow_arrayref) {
    print "$row->[0] $row->[1]\n";
}
$sth->finish;
$dbh->disconnect;
exit;
__END__


---

• fetchrow_array
@row = $sth->fetchrow_array;

Same as the fetchrow_arrayref execpt it returns an array of field values instead of an array reference.
Example:

#!/usr/bin/perl -w
use DBI;
use strict;
my $dbh = DBI->connect("DBI:mysql:contacts",undef,undef)
or die "Unable to connect to contacts Database: $dbh->errstr\n";
$dbh->{RaiseError} = 1;
my $sth = $dbh->prepare("SELECT first_name, last_name
                         FROM contact");
$sth->execute or
die "Unable to execute query: $dbh->errstr\n";
my @row;
while(@row = $sth->fetchrow_array) {
    print "$row[0] $row[1]\n";
}
$sth->finish;
$dbh->disconnect;
exit;
__END__


---

• fetchrow_hashref
$row_hash = $sth->fetchrow_hashref;

Another alternative method of fetching a row of data. This method returns a reference to a hash containing field name/field value pairs. The keys of the hash are the same field names returned from $sth->{NAME} method. This is not as efficient as the previous fetch methods. Example:

#!/usr/bin/perl -w
use DBI;
use strict;
my $dbh = DBI->connect("DBI:mysql:contacts",undef,undef)
or die "Unable to connect to contacts Database: $dbh->errstr\n";
$dbh->{RaiseError} = 1;
my $sth = $dbh->prepare("SELECT first_name, last_name
                         FROM contact");
$sth->execute or
die "Unable to execute query: $dbh->errstr\n";
my $row_hash;
while($row_hash = $sth->fetchrow_hashref) {
    print "$row_hash->{first_name} $row_hash->{last_name}\n";
}
$sth->finish;
$dbh->disconnect;
exit;
__END__


---

• fetchall_arrayref
$table = $sth->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 arrays of references to each row. You access/print the data by using a nested loop. Example:

#!/usr/bin/perl -w
use DBI;
use strict;
my $dbh = DBI->connect("DBI:mysql:contacts",undef,undef)
or die "Unable to connect to contacts Database: $dbh->errstr\n";
$dbh->{RaiseError} = 1;
my $sth = $dbh->prepare("SELECT first_name, last_name
                         FROM contact");
$sth->execute or
die "Unable to execute query: $dbh->errstr\n";
my $table = $sth->fetchall_arrayref;
my($i, $j);
for $i ( 0 .. $#{$table} ) {
    for $j ( 0 .. $#{$table->[$i]} ) {
        print "$table->[$i][$j]\t";
    }
print "\n";
}
$sth->finish;
$dbh->disconnect;
exit;
__END__


---

• finish
$sth->finish;

This method is used when no more data will be fetched from this statement handle before it is prepared again or destroyed. This method is mostly used for internal housekeeping (free up resources such as read locks). No need to call this method if you are about to destroy or reuse the statement handle. It's good to get in the habit of using it though.


---

• rows
$rc = $sth->rows;

This method returns the number of rows affected by the last non-select execute. Example:

#!/usr/bin/perl -w
use DBI;
use strict;
my $dbh = DBI->connect("DBI:mysql:contacts",undef,undef)
or die "Unable to connect to contacts Database: $dbh->errstr\n";
my @names = ['Flaherty', 'Jones', 'Smith'];
$dbh->{RaiseError} = 1;
my $sth = $dbh->prepare("UPDATE contact
                         SET phone = '555-1212'
                         WHERE last_name = ?");
$sth->execute(@names);
my $rc = $sth->rows;
print "Number of records updated: $rc\n";
$sth->finish;
$dbh->disconnect;
exit;
__END__


---

• bind_col
$sth->bind_col($column_number, \$var_to_bind);
$sth->bind_col($column_number, \$var_to_bind, \%attr);

This method assigns a column (field) to a variable. Whenever a row is fetched the coresponding variable is automatically updated. This makes the fetch very effecient.


---

• bind_columns
$sth->bind_columns(\%attr, @list_of_refs_to_vars_to_bind);

This method calls bind_col for each column of the select statement.  Example:

#!/usr/bin/perl -w
use DBI;
use strict;
my $dbh = DBI->connect("DBI:mysql:contacts",undef,undef)
or die "Unable to connect to contacts Database: $dbh->errstr\n";
$dbh->{RaiseError} = 1;
my $sth = $dbh->prepare("SELECT first_name, last_name FROM contact");
my($first, $last);
$sth->bind_columns(undef, \$first, \$last);
$sth->execute;
while($sth->fetchrow_arrayref) {
    print "$first $last\n";
}
$sth->finish;
$dbh->disconnect;
exit;
__END__


---

• Statement Handle Attributes
Most of these attributes are read-only. Some drivers can not provide values till after the execute method has been called.
 
• NUM_OF_FIELDS
$num_fields = $sth->{NUM_OF_FIELDS};

This attribute stores the number of fields the prepared statement will return.


---

• NUM_OF_PARAMS
$num_params = $sth->{NUM_OF_PARAMS};

This attribute holds the number of paramaters (placeholders) in the prepared statement.


---

• NAME
$names = $sth->{NAME};

This attribute returns a reference to an array of field names for each column returned.


---

• NULLABLE
$nulls = $sth->{NULLABLE};

This attribute returns a reference to an array indicating wether a particular column returned is a null (true/false).


---

• CursorName
$cursor_name = $sth->{CursorName}; (not supported in mySQL)

Returns the name of the cursor associated with the statement handle.


---

• Debugging
• DBI_TRACE
DBI_TRACE=2 perl your script.pl

In addition to the trace method you can also set trace information by setting the environment varible DBI_TRACE before running your script. If you set DBI_TRACE to a non numeric value then it is assumed to be a file name where all trace information will be appended.


---

• THE DBD CLASS

• Private Metadata Methods
• ListDBs
@databases = $dbh->func("$hostname:$port", '_ListDBs');

Returns a list of databases managed by the mysql daemon running on hostname $hostname, port $port. This method is not needed when running on localhost using the default port number. You shoud use the data_sources method.


---

• ListTables
@tables = $dbh->func('_ListTables');

This function returns an array of tables associated with the database used in the connect method. Example:

#!/usr/bin/perl -w
use DBI;
use strict;
my $dbh = DBI->connect("DBI:mysql:contacts",undef,undef)
or die "Unable to connect to contacts Database: $dbh->errstr\n";
my @tables = $dbh->func('_ListTables');
for(@tables) {
    print;
}
$dbh->disconnect;
exit;
__END__


---

• ListFields
Deprecated, see $sth->{NAME}


---

• ListSelectedFields
Deprecated, see $sth->{NAME}


---

• Database Manipulation
• CreateDB
$drh->func($database, '_CreateDB');
This method allows you to create a database from your perl scripts running on localhost.


---

• DropDB
$drh->func($database, '_DropDB');
This method allows you to delete a database from your perl scripts running on localhost. Deletion is not prompted for and is unrecoverable.
Use this function at your own risk!


---

• Private Statement Handle Attributes
• ChopBlanks
$sth->{ChopBlanks} = 1;

This attribute will determine wether a fetch will chop preceeding/trailing blanks off the field values. This has no impact on the max_length attribute.


---

• mysql_insertid
$insertid = $sth->{mysql_insertid};

This attribute works in conjunction with the autoincrement feature of mySQL. If autoincrement is assigned to a field in the table definition then the value used in this field during an insert will be stored in this attribute.


---

• is_blob
$blobs = $sth->{is_blob};

This attribute is a reference to an array of boolean values associated with the statement handle. True indicates that the associated column is a blob.


---

• is_key
$keys = $sth->{is_key};
 
This attribute is a reference to an array of boolean values associated with the statement handle. True indicates that the associated column is a key. 

---

• is_pri_key
$pri_keys = $sth->{is_pri_key};
 
This attribute is a reference to an array of boolean values associated with the statement handle. True indicates that the associated column is a primary key. 

---

• is_num
$is_num = $sth->{is_num};
 
This attribute is a reference to an array of boolean values associated with the statement handle. True indicates that the associated column contains numeric values


---

• is_not_null
$is_not_null = $sth->{is_not_null};
 
This attribute is a reference to an array of boolean values associated with the statement handle. False indicates that the associated column contains NULL values


---

• length
$length = $sth->{length};

An reference to an array of field lengths (as defined in the table definition) associated with the statement handle.


---

• max_length
$max_length = $sth->{max_length};

An reference to an array of maximum field lengths physically present in the result set associated with the statement handle.


---

• table
$tables = $sth->{table};

A reference to an array of table names associated with the statement handle.


---

• type
$types = $sth->{types};

A reference to an array of field types associated with the statement handle. Values returned is very dependent on the database driver used.


---

• EXAMPLE PROGRAM - contactME

contactME is based on the PHP program written by Michael J. Miller Jr., appearing in his article in the January, 1998 edition of Web Techniques. contactME uses DBI as the API to mysql (of course). It also uses CGI.pm by Lincoln Stein. This perl library uses perl5 objects to make it easy to create Web fill-out forms and parse their contents. This module comes standard with Perl5.004 and above. And Finally, it uses HTML::Table.pm by Stacy Lacy. This module allows you to create dynamically sized tables on the fly. Perfect for parsing the results of a SELECT query. Enjoy :-)

Get the source file.

---