Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

SQL etc

Posted Oct 19, 1998

Introduction to Databases for the Web: Pt. 3 - Page 2

By Selena Sol

Using Perl 5 and the DBI Module to Communicate With Databases

However, for purposes of demonstration, in this part we will show an example of writing a CGI script in Perl 5, using the DBI (Database Independent Interface) module supplied for free with the normal Perl 5 distribution.

As an example, our DBI-aware Perl 5 CGI Script will be used to access an MS Access Database on a Windows 95 system using a local web server called Sambar.

Of course, as we will see in just a bit, the fact that we use Perl 5 and the DBI module means, that the code we develop here will work on UNIX, NT or Macintosh without any modifications.

It will also work "out of the box" for most commercial and public domain databases available for the web including Oracle, Sybase, Informix, Postgres, DB2, etc.

Finally, you should know that you need not use Sambar as your Web Server. Any Web Server that can handle CGI will do. I am just using Sambar because it is free and it is what I happened to download. In retrospect, I would probably recommend Apache since it is available on so many platforms.

In short, the beauty of Perl and of the DBI module is that they are both designed to be highly cross platform and non-proprietary.

(Note that since you are a web developer, I am assuming that you already know Perl 5 for CGI. If you don't, or if you need a refresher, check out Web Ware at WDVL! The e-zine has tutorials on Perl and Perl 5 for web developers.)

The DBI Module

The DBI Module is an incredibly useful tool because it abstracts away the need to learn how to communicate with every type of database out there. Consider what a pain it would be if you had to write a CGI script for every proprietary database engine.

Now imagine how much extra work you would have if the management decided to change database vendors on you after you had already written your code!

[No abstractions to Database]

What the DBI module does for you is to provide a single interface to send SQL commands to any proprietary database. Essentially, you write all your code to a generic standard, and move it from database to database without having to re-code your application.

The secret to the DBI module is the extensive library of DBD (Database Dependent) modules that come with the standard distribution of Perl 5. These DBD modules provide the code that hooks up DBI to a proprietary database driver.

Of course, since these DBD modules are already written for almost any Database you would consider using, you needn't do any of that hard work. All you need to worry about is satisfying the requirements of the DBI interface. Everything else is handled behind the scenes!

[Web Browser-Web Server-CGI Script-Database]

It looks pretty complex but believe me, it isn't. And it makes your life as a programmer incredibly easy. That is, although the communication takes place through so many actors, you, as the programmer, need not bother with the details of that communication. You talk to the DBI module, and the DBI module handles the multitude of proprietary database blue meanies by itself.

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

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 = $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.



SQL etc Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
SQL etc Forum
Topic By Replies Updated
MySQL rollback UAL225 0 August 21st, 09:56 PM
Complex Search Query Galway 0 May 20th, 10:04 PM
change collation at once supercain 2 May 15th, 06:18 AM
SQL Features, tools and utilities question Neomite 1 April 10th, 09:13 AM