So how do you use the DBI module?
Well, as with any Perl 5 module, you
simply use the "USE" keyword.
Once, the DBI module is loaded,
you can then instantiate a database connection. Establishing
a connection involves creating a "database handle" object by
using DBI to connect to a database given a data
source name, database user, database password, and a database
driver.
Consider the following example in which
we connect to a Database with the data source name of
"MyCompany" using the username
"selena", password "12mw_l", and the ODBC driver. Notice that
the ODBC driver name is tagged to the beginning of the Database name.
use DBI;
$dbHandle = (DBI->connect('DBI:ODBC:MyCompany',
'selena",
'12mw_l'));
We'll show you how to setup a database
name etc when we cover Access later. However, you can
imagine that regardless of what database you use on what system,
you will be able to define these values and use them.
Once you have created a database handle
object, you can do things with the object. Most likely, you will
be sending SQL statements to the database via the database handle.
To do so, you create a statement handle object by calling the
prepare() method on the database handle object and then call the
execute() method on the statement handle object. Consider the
following code:
use DBI;
$dbHandle = (DBI->connect('DBI:ODBC:MyCompany',
'selena",
'12mw_l'));
$sql = "SELECT * FROM Employees";
$statementHandle = $dbHandle->prepare($sql);
$statementHandle->execute() ||
die $statementHandle->errstr;
Once the sql has been sent to the database,
the DBI module will store the results. To get to the results, you
can use any number of useful statement handle methods. One
of the most common methods is the fetchall_arrayref() method that
returns all the returned database rows in a reference to an
array of references to rows. Consider the following:
use DBI;
$dbHandle = (DBI->connect('DBI:ODBC:MyCompany',
'selena",
'12mw_l'));
$sql = "SELECT * FROM Employees";
$statementHandle = $dbHandle->prepare($sql);
$statementHandle->execute() ||
die $statementHandle->errstr;
$arrayRef = $statementHandle->fetchall_arrayref;
Of course, once you have the reference to
the array, you can dereference everything to access the data.
Finally, when you have massaged all the data,
you close the database connection with the database handle object's
diconnect() method:
$dbh->disconnect();
At the end of this part, I have included several
examples of CGI script using DBI so you can see how you might build
an application around the methods. Further, fantastic
documentation for DBI can be found at http://www.symbolstone.org/.
However, I will also include a cursory API reference summary (based upon
the DBI documentation by Tim Bunce) of the most used methods
here so you can get a feel for what is available.
General DBI Class Methods |
Name |
Usage |
Description |
connect() |
$dbHandle = DBI-> connect (DBI:$driverName:$dataSource, $usrName, $passwrd);
$dbHandle = DBI-> connect ($dataSource, $usrName, $passwrd, $driverName); |
This method establishes a connection to the specified Data Source
and returns a database handle object.
Note that if you define the driver name as the fourth parameter,
you need not tag the DBI:$driverName along with the $dataSource name.
|
available_drivers() |
@drivers = DBI-> available_drivers();
@drivers = DBI-> available_drivers ($warnFlag); |
As you can see, this method returns an array
of drivers that the module can see. It gets the list by searching
through the @INC array for DBD modules. If you pass a true value as
a parameter, warnings about hidden drivers will be quelled.
|
data_sources() |
@dataSources = DBI-> data_sources($driver); |
Returns a list of the data sources available for a given
driver if the driver supports the method.
|
trace() |
DBI->trace ($level);
DBI->trace ($level, $file);
|
Provides tracing functionality such that the $file
is appended to with trace information. A Level 0 rating disables tracing
and a level 2 tracing provides a detailed trace. |
neat() |
$neatvalue = DBI::neat($value, $maxLength); |
Formats the specified value such that strings
are quoted, undefined values are replaced with "undef", unprintable
characters are replaced with a ".", and string will be truncated
and ended with "..." if longer than $maxLength |
neat_list() |
$delimitedList = DBI::neatList(\@listRef, $maxlength, $delimiter); |
Formats an array by calling neat() on each element and
forming a string by joining the elements with the specified delimiter. |
dump_results() |
$dbRows = DBI::dump_results ($statementHandle, $maxLength, $lineSeparator, $fieldSeparator, $fileHandle); |
Gets all the rows from the statement handle object,
calls neat_list() on each row and prints the results to $filehandle
that is by default. |
General Handle Methods |
Name |
Usage |
Description |
err() |
$result = $handle->err(); |
Gets the error code returned from the database. |
errstr() |
$result = $handle->errstr(); |
Gets the error message returned from the database. |
state() |
$state = $handle->state(); |
Gets the SQLSTATE error code. |
trace() |
$handle->trace ($level);
$handle->trace ($level, $file);
|
Provides tracing functionality such that the $file
is appended to with trace information. A Level 0 rating disables tracing
and a level 2 tracing provides a detailed trace. |
Database Handle Methods |
Name |
Usage |
Description |
prepare() |
$statementHandle = dataSourceHandle -> prepare($sql); |
Prepares a statement for execution. |
do() |
$rowCount = $databaseHandle -> do($sql); |
Prepares and executes an SQL statement and
returns the number of rows returned. |
commit() |
$rowCount = $databaseHandle -> commit(); |
Makes permanent the last set of database changes if
supported. |
rollback() |
$rowCount = $databaseHandle -> rollback() |
Undoes uncommitted changes if supported. |
disconnect() |
$rowCount = $databaseHandle ->disconnect() |
Disconnects from the database. |
ping() |
$rowCount = $databaseHandle - > ping() |
Tries to figure out if the database
server is still available |
quote() |
$sql = $databaseHandle - > quote($string); |
Spiffys up a string for an SQL statement |
Statement Handle Methods |
Name |
Usage |
Description |
execute() |
$value = $statementHandle -> execute(); |
Executes a prepared statement |
fetchrow_arrayRef() |
$arrayRef = $statementHandle -> fetchrow_arrayref(); |
Gets the next row of data as a reference to an array
holding the column values. |
fetchrow_array() |
@array = $statementHandle -> fetchrow_array(); |
Gets the next row of data as an array. |
fetchrow_hashref() |
$hashRef = $statementHandle -> fetchrow_hashRef(); |
Gets the next row of data in which the keys to the
hash reference are the column names and the values are the column values. |
fetchall_arrayref() |
$reference = $statementHandle -> fetchall_arrayref() |
Gets all the rows as references in a referenced array. |
Note that there are all sorts of other more complex methods
such as binding and error handling, but you should consult the documentation
and the DBI tutorial referenced above. These topics are a bit beyond the
scope of this tutorial.