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", $user, $password); | |
available_drivers | @drivers = DBI->available_drivers; | |
data_sources | @sources = DBI->data_sources($driver); | |
trace | DBI->trace($trace_level); | |
DBI Utility Functions |
||
neat | $neat_value = DBI::neat($value, $maxlen); | |
neat_list | $neat_list = DBI::neat_list($listref, $maxlen, $field_sep); | |
dump_results | $rows = DBI::dump_results($sth, $maxlen, $lsep, $fsep, $fh); | |
DBI Dynamic Attributes |
||
$DBI::err | see $dbh->err | |
$DBI::errstr | see $dbh->errstr | |
$DBI::state | see $dbh->state | |
$DBI::rows | ? is this associated with $sth->rows ? | |
Methods Common to All Handles |
||
err | $err_code = $dbh->err; | |
errstr | $err_string = $dbh->errstr; | |
state | $state = $dbh->state; | |
trace | $trace = $dbh->trace; | |
func | $result = $dbh->func(@func_args, $func_name); | |
Attributes Common to all Handles |
||
Warn | $dbh->{Warn} = 1; | |
CompatMode | $dbh->{CompatMode} = 1; | |
InactiveDestroy | $dbh->{InactiveDestroy} = 1; | |
PrintError | $dbh->{PrintError} = 1; | |
RaiseError | $dbh->{RaiseError} = 1; | |
ChopBlanks | $dbh->{ChopBlanks} = 1; | |
LongReadLen | $dbh->{LongReadLen} = 1; | |
LongTruncOk | $dbh->{LongTruncOk} = 1; | |
Database Handle Methods |
||
prepare | $sth = $dbh->prepare($statement); | |
do | $sth->$dbh->do($statement); | |
commit | $dbh->commit; (Not supported by mySQL) | |
rollback | $dbh->rollback; (Not supported by mySQL) | |
disconnect | $dbh->disconnect; | |
ping | $return = $dbh->ping; | |
quote | $sql = $dbh->quote($string); | |
Statement Handle Methods |
||
bind_param | $sth->bind_param($param_num, $bind_value, $bind_type); | |
bind_param_inout | $rv = $sth->bind_param_inout($param_num, \$bind_value, $max_len);
(Not supported by mySQL) |
|
execute | $sth->execute; | |
fetchrow_arrayref | $row_array = $sth->fetchrow_arrayref | |
fetchrow_array | @row_array = $sth->fetchrow_array; | |
fetchrow_hashref | $row_hash = $sth->fetchrow_hashref; | |
fetchall_arrayref | $row_all = $sth->fetchall_arrayref; | |
finish | $sth->finish; | |
rows | $rc = $sth->rows; | |
bind_col | $sth->bind_col($column_number, \$bind_var); | |
bind_columns | $sth->bind_columns(\%attr, @bind_var_refs); | |
Statement Handle Attributes |
||
NUM_OF_FIELDS | $num_fields = $sth->{NUM_OF_FIELDS}; | |
NUM_OF_PARAMS | $num_params = $sth->{NUM_OF_PARAMS}; | |
NAME | $names = $sth->{NAME}; | |
NULLABLE | $nullables = $sth->{NULLABLE}; | |
CursorName | $cursor_name = $sth->{CursorName}; (?Not supported by mySQL?) | |
Debugging |
||
DBI_TRACE | DBI_TRACE = 2 perl test_script.pl | |
THE DBD CLASS |
||
Private MetaData Methods |
||
ListDBs | @databases = $dbh->func("$hostname:$port", '_ListDBs);
(see also data_sources) |
|
ListTables | @tables = $dbh->func('_ListTables'); | |
ListFields | see NAME | |
ListSelectedFields | see NAME | |
Database Manipulation |
||
CreateDB | $dbh->func($database, '_CreateDB'); | |
DropDB | $dbh->func($database, '_DropDB'); | |
Private Statement Attributes |
||
ChopBlanks | $sth->{ChopBlanks} = 1; | |
mysql_insertid | $id = $sth->{mysql_insertid}; | |
is_blob | $blobs = $sth->{is_blob}; | |
is_key | $keys = $sth->{is_key}; | |
is_pri_key | $prim_key = $sth->{is_pri_key}; | |
is_num | $num_fields = $sth->{is_num}; | |
is_not_null | $not_nulls = $sth->{is_not_null}; | |
length | $length = $sth->{length}; | |
max_length | $max_lengths = $sth->{max_length}; | |
table | $tables = $sth->{table}; | |
type | $types = $sth->{type}; | |
EXAMPLE PROGRAM |
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__
#!/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__
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__
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__
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.
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.
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__
This method returns the native database engine error code from the last
driver function called
This method returns the native database engine error message from the
last driver function called
?Not supported by mySQL?
Very similar to DBI::trace with one exception. The trace is only associated
with the specific handle it is used with.
This method is used to call private methods implemented by the driver
(DBD::*)
#!/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__
$dbh->{AttributeName} = 1;
$value = $dbh->{AttributeName};
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.
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.
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.
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.
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).
#!/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__
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__
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__
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__
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__
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__
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__
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.
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__
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.
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__
This attribute stores the number of fields the prepared statement will
return.
This attribute holds the number of paramaters (placeholders) in the
prepared statement.
This attribute returns a reference to an array of field names for each
column returned.
This attribute returns a reference to an array indicating wether a particular
column returned is a null (true/false).
Returns the name of the cursor associated with the statement handle.
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.
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.
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__
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.
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.
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.
An reference to an array of field lengths (as defined in the table definition)
associated with the statement handle.
An reference to an array of maximum field lengths physically present
in the result set associated with the statement handle.
A reference to an array of table names associated with the statement
handle.
A reference to an array of field types associated with the statement
handle. Values returned is very dependent on the database driver used.