Page 244 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Unit 14: Application Development and Administration

Fortunately, dozens of great module writers have done all that hard work for you. You’ll see just Notes
how easy it is when we start going over code!

14.2.5 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’));

LOVELY PROFESSIONAL UNIVERSITY 237