MySQL Perl DBI/DBD Manual Version 0.45

MySQL Version: 3.22.X
Author: Jay Flaherty (fty@utk.edu)
Update: Mike Miller (mke@netcom.com)


Acknowledgments

I want to thank Tim Bunce for writing the DBI module. Much of the technical information, definitions, and section breakup in this chapter was taken from the very nice POD file imbedded in the module. I would also like to thank Alligator Descartes who is the author of  the DBD::mSQL module of which the DBD::mysql module is based on. Finally, I would like to thank  Jochen Wiedmann who has graciously agreed to take over the development of both DBD::mSQL and DBD::mysql  and incorporated (for backward compatibility) both mSQL.pm and mysql.pm, the precursor to the DBI API, all in one module (whew!).
 

Caveat

This document is a work in progress. Any and all comments, corrections, suggestions, kudos, etc... are very welcome. Please send all messages concerning this document to Jay Flaherty (fty@utk.edu).

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.


 

Overview

DBI is a database Application Programming Interface (API) written in the form of a Perl5 module. This module defines a set of "methods" and "attributes" to use that defines a consistent interface to any database that has a DataBase Driver (DBD) defined. This driver also takes the form of a Perl5 module. The DBI acts as a "switch " between a programmer's application and one or more database drivers. It is the drivers that actually talk to the database. For MySQL, this driver is called DBD::mysql. These methods and attributes are devided into database methods and attributes and statement methods and attributes. For more information on the Perl5 DBI, please visit http://www.symbolstone.org/technology/perl/DBI and read the documentation. For more information on Object Oriented Programming (OOP) as defined in Perl5, go to http://www.perl.com/pub/q/documentation . Your best bet as always is to read the documentation that comes with the modules! This documentation is embedded in the modules themselves using POD (Plain Old Documentation) syntax. You can read this format several ways.


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


Table of Contents

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


These method are private to the mysql.pm module