The DBI API
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 disconnect() 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.hermetica.com/. 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 <STDOUT> 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 = /old?u=http%3A%2F%2Fwdvl.com%2FAuthoring%2FDB%2FIntro%2F%24statementHandle&y=1999 -> 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.
The DBI ModuleIntroduction to Databases for the Web | Table of Contents
Getting the Pieces
