Page 245 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Database Management Systems/Managing Database

Notes $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.
Table 14.1: General DBI Class Methods

Name Usage Description
connect() $dbHandle = DBI-> connect This method establishes a connection to the
(DBI:$driverName:$dataSourc specified Data Source and returns a database
e, $usrName, $passwrd); handle object.
$dbHandle = DBI-> connect Note that if you define the driver name as the
($dataSource, $usrName, fourth parameter, you need not tag the
$passwrd, $driverName); DBI:$driverName along with the $dataSource
name.
available_drive @drivers = DBI-> As you can see, this method returns an array of
rs available_drivers(); drivers that the module can see. It gets the list by
() @drivers = DBI-> searching through the @INC array for DBD
available_drivers ($warnFlag); modules. If you pass a true value as a parameter,
warnings about hidden drivers will be quelled.
data_sources() @dataSources = DBI-> Returns a list of the data sources available for a
data_sources($driver); given driver if the driver supports the method.
trace() DBI->trace ($level); Provides tracing functionality such that the $file
DBI->trace ($level, $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, Formats the specified value such that strings are
$maxLength); 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 = Formats an array by calling neat() on each
DBI::neatList(\@listRef, element and forming a string by joining the
$maxlength, $delimiter); elements with the specified delimiter.
dump_results() $dbRows = DBI::dump_results Gets all the rows from the statement handle
($statementHandle, object, calls neat_list() on each row and prints the
$maxLength, $lineSeparator, results to $filehandle that is <STDOUT> by
$fieldSeparator, $fileHandle); default.

Contd...

238 LOVELY PROFESSIONAL UNIVERSITY