Originally Published: Monday, 19 March 2001 Author: Marcelo Pham
Published to: featured_articles/Featured Articles Page: 1/1 - [Printable]

Writing Database Oriented Web-based Applications with Perl - Part III

In the latest segment of his continuing series, Marcelo explains how to interface Perl with an actual database.

   Page 1 of 1  

Interfacing Perl with the database (I):

12. Overview of DBI-DBD structure
13. Getting and installing DBI
14. Getting and installing DBD
15. Connecting to the database from Perl
16. Basic code
17. Links and books
18. Next issue

Now that you have already chosen your preferred database engine, let's see how to connect to the database from a Perl script.

12. Overview of DBI-DBD structure:

Perl was widely enhanced with database connectivity capability by Tim Bunce. Tim created the DBI interface, which allows programmers to have four totally independent layers when designing applications. These layers protect hackers from picky and tricky coding details when connecting to databases from different vendors, and also helps them achieve code reusability when switching databases.

Each layer is independent and has its own functionality:

+--------+   +--------+       +------------+    +-----------+
| Script +-->+ DBI    +---+-->+ DBD Oracle +--->+ Oracle DB |
+--------+   +--------+   |   +------------+    +-----------+
                          |
                          |   +------------+    +-----------+
                          +-->| DBD Postgre|--->| Postgre DB|
                          |   +------------+    +-----------+
                          |
                          |   +------------+    +-----------+
                          +-->| DBD mySQL  |--->| mySQL DB  |
                              +------------+    +-----------+

the Perl script layer: this is the program from which you connect to the database to inquire, update or delete records in your database tables

the DBI (DataBase Independent) layer: this is the Perl module that allows you to use objects (called handles) to connect, prepare and execute SQL commands from your Perl script to your database

the DBD (DataBase Dependent) layer: this is the Perl module specific for each database vendor. For example, you have DBD for Oracle, DBD for Postgres, etc. Usually the notation is DBD::<dbtype> like DBD::Pg for Postgres

the Database layer: the database you chose in the second installment, for example PostgreSQL

The Perl script is the front-end program where you will draw the data from the database, and in which you will declare the mid two layers.

This script will use the DBI module to create objects to connect, retrieve data and execute database commands or statements, called handles. There are three types of objects, sorry, handles , for DBI: the driver handle, the database handle and the statement handle.

The driver handle is the one you usually use to connect to the database, through the connect() method. This handle is a DBI object method.

The database handle is the one responsible for actually connecting to the database, through the driver handle. In other words, the database handle is a child of the driver handle and allows you to specify the DBD driver, the host name, username and password.

Finally, the statement handle is in charge of the execute database command and, as you might have already suspected, is a child of the database handle.

Let me give you an example so you can get the whole picture. Do not mind the code details, we will go over that in Section 15 , just abstract the object scheme:

use DBI;

$dbh=DBI->connect("dbi:Pg:dbname=taz:localhost","user","password");
$sth=$dbh->prepare("select * from tax_table");
$sth->execute;

+---------+        +---------+       +---------+
| Drv Hnd +------->| DB Hnd  +------>| Stm Hnd |
+---------+        +---------+       +---------+
(=DBI->) ($dbh) ($sth)

$dbh is the database handle created based on the DBI instance (the driver handle), which is created with the DBD driver, the database name, host name, user and password. Then, $sth is the statement handle instance created as a child of the database handle, which contains the statement to be executed. The statement handle prepares the query to be executed with the method prepare(). Finally, the statement handle invokes the method execute to run the query. Still confused? Section 15 may make it clearer.
After you do it all, the statement handler usually returns rows of data with the method fetchrow_array(). You'll see it in action in Section 16.

One interesting point to mention is that if you have several databases and you installed all the corresponding DBD drivers, you can connect to several databases at the same time from the same script. For example, you can connect to your Oracle database where you store all your local customers, connect to your mySQL database where you store all your web-site customers, and make a valuable comparative report (people from sales and marketing depts. will love you!).

Another interesting point to mention is that when you create the database handle, you can specify the database driver, in this example, Postgres. Now, if in your application you encapsulate the routine to connect to the database in such a way that all your scripts will call that same routine to connect to the database, DBI gives you the ability to switch databases, just by changing the mentioned function code to connect to the database.

Now, knowing that you can connect to virtually any database, you may ask "why did I do an exhaustive database analysis and design to carefully choose a database, if now I can switch databases in a blink of an eye if I want to?" Technically, yes, you can switch databases seamlessly with DBI. However, you will have some work to do. You will have to check all your database related code (for differences in SQL syntax for both databases), and also the data migration will keep you entertained for a while.
Let me use a very common situation as an example:

Let's say you originally chose mySQL for your project -because you were looking for good performance, and then 6 months later after your application went live, you realized that you're frequently getting data corruption due to connection issues through the Internet. Now you decide the best solution is to use transactions: mySQL does not support transactions. Now you want to migrate to PostgreSQL, because it does have a transaction handler and other features that you want to use.

First problem: you must review all the code to find differences in SQL syntax. For example:

In mySQL you created text fields using longtext mySQL column type. Postgres does not have that column type, you must use text instead.

In mySQL, you created several unsigned integers columns. Postgres does not understand the syntax age int unsigned (it'll give you an error).

Second problem: you must migrate your existing data from mySQL to Postgres. This means you should study how the mySQL export process works, and how the Postgres import routines behave.

So, back to what I stated in the second part, you should carefully choose the database to try to avoid all these headaches. Yes, DBI is powerful enough to give you the ability to change databases without losing the logic of your current application, but that does not mean that you can avoid or put off the decision of choosing the database.

You won't have this problem if you do a good design of your application. DBI architecture is solid and robust, gives you a lot of flexibility, the programming is easy to understand and remember, it is very popular amongst developers, and the error trapping is simple and good at tracing bugs. You'll also have a lot of support from Web sites and user groups.

I highly recommend this book:
"Programming the Perl DBI" (Alligator Descartes-Tim Bunce) - O'Reilly and Assoc.
if you want to have a deep knowledge when programming the DBI.

Or, you can go to the egroups web site and join the DBI group. Tim Bunce is an active collaborator in this user group -he helped me a couple of times when I was having problems.

13. Getting and installing DBI:

DBI is a Perl module that you install on your Linux box. Of course, it requires Perl to be properly installed. You can get the latest (as of today) version (1.14), which requires Perl 5.004 or higher, from the Comprehensive Perl Archive Network (CPAN). Download the file called DNI-1.14.tar.gz and move it to your   /usr/src directory.

To install DBI, you must first unpack the compressed file by typing:

# cd /usr/src
# gunzip DBI-1.14.tar.gz

After the compressed file is unpacked, you must unpack the installation file, by typing:

# tar xvf DBI-1.14.tar

(You can now delete the compressed and installation files if you want, they're not going to be needed anymore).

This will create a directory called DBI-1.14 with all the installation files under an /usr/src directory. You should now prepare and install the files as with any other Perl module, by typing:

# cd /usr/src/DBI-1.14
# perl Makefile.PL
# make
# make test
# make install

When building the test file (make test), you should see lines telling you what the compiler is doing, and the result at the very end of each line. These results must be all OK. When it finishes, the last line will tell you whether the test was successful or not. If it was not, please read the readme.html file that comes with the distribution file, or post a complete log of the building process and the output of perl -v, to the DBI mailing list at dbi-users@isc.org.

If everything went fine, you should have the DBI module correctly installed. A file called DBI.pm contains useful information, or you can type perldoc DBI for additional documentation.

To use the DBI module from your Perl script, you declare it as any other external Perl module

#!/usr/bin/perl
use DBI;

at the beginning of your program.

After installing DBI, you need to install the DBD driver for your specific database.

14. Getting and installing DBD:

You can download the DBD for your specific database vendor from the CPAN ftp site.
Browse the directory, and download the latest DBD version for your database. For example with DBD::mySQL the latest version is 1.2215, the DBD::Oracle latest is 1.06, and the DBD::Pg (PostgreSQL) latest release is 0.95.

After downloading the compressed installation file, again, move it to /usr/src and unpack the compressed file. For example, if you downloaded DBD-Pg-0.95 (PostgreSQL DBD driver) you must type:

# cd /usr/src
# gunzip DBD-Pg-0.95.tar.gz

And then unpack the installation file, by typing:

# tar xvf DBD-Pg-0.95.tar

This will create a directory under /usr/src called DBD-Pg-0.95 . To prepare and install the DBD module, first login as the PostgreSQL (or your specific database) superuser account, and then type:

$ cd /usr/src/DBD-Pg-0.95
$ perl Makefile.PL
$ make
$ make test

You cannot do this as root, since the Makefile script checks environment variables (path for database libraries, database files, etc.) and connects to the database when testing the installation. If the regression test was successful, you can do the final installation of DBD by typing:

$ make install

You now have DBD installed in your Linux box. If you had problems during the installation, you can post a message to the DBI users mailing list at dbi-users@isc.org , or also dbi-users@fugue.com , along with the version of DBI, DBD, database version and output of Perl -v.

* Note: DBD drivers require minimum database and DBI versions. For example, the DBD driver for PostgreSQL v0.95 requires at least Perl 5.005, PostgreSQL 6.5 and DBI 1.0. Please refer to the README file included in the installation files.

15. Connecting to the database from Perl :

Assuming that everything is fine, we will now proceed with the basic coding to connect to a database. Usually the structure of your script should be something like this:

#!/usr/bin/perl
# Inquiry of customer balances

use DBI;

# Connect to database:
$dbh=
DBI->connect("dbi:Pg:dbname=taz:localhost","user","password");

# All your SQL statements go here

# Disconnect
$dbh->disconnect;

# The rest of your script

Let's go over the connect syntax. Basically connect has three parameters: the connection string, the username and the password.

The connection string contains three declaration sections: the DBD driver, the database name and the hostname, but the format may vary for different database drivers.
The connection string always begin with dbi followed by a colon and the DBD driver name. For PostgreSQL, Pg is the DBD driver name; for mySQL: mysql; and for Oracle, of course, Oracle.
For example, the connection string for PostgreSQL would be like:

DBI->connect("dbi:Pg:dbname=taz:localhost","user","password");

(where taz is your database name, and localhost is the name of the host where Postgres is installed) while mySQL would go like:

DBI->connect("dbi:mySQL:taz:localhost","user","password");

(mySQL does not use the tag dbname) and Oracle would be like

DBI->connect("dbi:Oracle:LISTENER","user","password");

(Oracle knows the database and host name through the Listener, so make sure the listener for the database you want to connect is running).
Then the username and password are the username and password created for the database superuser account.

Usually you can omit the hostname and DBI will connect to the local host, but if you want to connect to a remote database, you must specify the remote host name, along with the username and password of the database supersuser account of the remote database.
* Note: you can -and should- have your application server separated from your database server, but if you do be careful when designing your security scheme.

After doing all the required database operations, you should disconnect the database handle by including this statement in your script:

# Disconnect from database:
$dbh->disconnect;

Let's make a simple connection script. Create a text file called testdb.cgi containing the following lines (this example is for PostgreSQL, please change the connection string to your specific database needs based on what you read above):

#!/usr/bin/perl

# Testing DBD-DBI drivers
use DBI;

$dbh=
DBI->connect("dbi:Pg:dbname=taz:localhost","user","password");

$dbh->disconnect;
print "Connection successful !\n";

Where dbname (in this example taz) is your database name, and user and password are the username and password for the database superuser account.
When finished, save the file, make it executable (chmod a+x testdb.cgi ) and type:

# perl testdb.cgi

You should see the message Connection successful! If you get an error message, the connection could not be made (no kidding! ). DBI gives good informative message errors so you should   not have any problem figuring out what went wrong. But usually, you will get errors if:

The database daemon is not running (for Oracle, make sure both Server Manager and Listener are running).

The database does not exist in the specified hostname. You can try letting DBI choose the default host by eliminating the hostname, for example connect("dbi:Pg:dbname=taz","user","password").

The database superuser account username and/or password are incorrect (check from the console -- logging in as the database superuser,   using that same username and password).

The database name is incorrect. (Check the database name case. Remember that almost everything is case-sensitive in Linux.)

The DBI or DBD driver was not correctly installed and/or configured (refer to the DBI or DBD readme files for trobleshooting).

If you cannot understand what is wrong,   post a help message to the DBI users mailing list .

And, if you didn't get any error message, good job!

* Tip: you can create a Perl library script with the connection string, username and password as functions, and invoke these functions from each script that uses a database connection. For example, a library called "dbconn.lib" could contain lines like these:

#!/usr/bin/perl
# Database library sample:
sub connectionstring {
my $dbtype="mySQL";
my $dbname="taz";
my $dbhost="localhost";
my $connstr="dbi:".$dbtype.":".$dbname.":".$dbhost

return $connstr;

} #End connectionstring

sub dbusername {
my $username="username";

return $username;
} #End dbusername

sub dbpassword {

my $password="password";

return $password;
} #End dbpassword
1; # End of library script

And, from your script:

#!/usr/bin/perl
# Script to inquiry customer status
use DBI;
require "dbconn.lib";
my $dbh=DBI->connect(&connectionstring;&dbuser;&dbpassword);
# (inquiry code)
# Disconnect from database:
$dbh->disconnect;
# (rest of your script)

This is an example of how you can encapsulate these variables. If you decide to, for example, change the database superuser password, you just have to modify one script instead of all of them. Always try to create functions that return desired values, never use global variables unless absolutely necessary.

Also, always try to connect only when you have to, and disconnect as soon as you finish with all your database duties, as this will improve performance and save server resources for other users. In other words, please do not open a database connection at the very beginning of your script and not disconnect until the very end.

16. Basic code:

We'll start this section with an overview of SQL commands. If you are an expert in SQL scripting, I recommend you to skip this paragraph or you may get really bored!

For those new to SQL (you can spell it, or you can say 'sequel' ), SQL stands for Structured Query Language and is nothing more and nothing less than a standard language for database queries (SQL is not a product). SQL is a high-level language that allows programmers and database administrators to easily and efficiently update and extract information from different relational database management systems or RDBMS. There are standards for SQL and the most utilized one is ANSI 92 (because it was established in 1992). RDBMS's like PostgreSQL and Oracle use this standard.

The five basic SQL commands are: create, insert, select, update and delete.

Create is used along with the database element name, table being the most used. So, to create a table, you should type in your database shell:

testdb=# create table test (code int, name char(25));

Syntax for column types, referential integrity and other issues will not be discussed here (they would take lots of pages). Please refer to your database documentation, in the Developer section, for further information.

You can also create indexes (create index), sequences (create sequence), views, users, and other database elements.

Insert is invoked when you want to insert a new record or row to the specified table:

testdb=# insert into test values (100, "Taz");

Select (perhaps the most famous command in SQL) is called to inquiry rows from one or many tables or views:

testdb=# select code,name from test;

This will return:

code  | name
------+-------------------------
100   | Taz
1 row(s) returned

Update
is called whenever you want to update information from a row or a range of records:

testdb=# update test set name="Taz Mania" where code=100;

If you inquiry the table again, you will see:

testdb=# select code,name from test;

code  | name
------+-------------------------
100   | Taz Mania
1 row(s) returned

You use delete to delete row(s) from a specific table:

testdb=# delete from test where code=100;

There are of course much more SQL commands but for now with these five you'll have more than enough to play around with. Please refer to any of the database books I recommended in the last installment\\ toget a more detailed knowledge of SQL commands.

  Ok! Let's go back to Perl. After connecting to the database, you can create a statement handle to prepare and execute a SQL statement. For example:

#!/usr/bin/perl
# Script to inquiry contact name and phone number

use DBI;
require "dbconn.lib";
my $dbh=DBI->connect(&connectionstring;&dbuser;&dbpassword);
my $sql="select code,last_name,first_name,phone from contact";
$sth=$dbh->prepare($sql);
$sth->execute();

When you execute a select statement, the statement handle returns an array with the result of the query, which you can retrieve with the fetchrow_array() method:

@rows=$sth->fetchrow_array;

The array @rows will contain in each subindex the correspondent column from the select statement. For example, if your statement is select code, first_name from contact, then @rows will have in $rows[0] the value for the first column (code), and $rows[1] will have the returned value for the second column from your select statement, in this case first_name.

No result is returned when doing update, insert or delete. For example, if you type:

my $sql="update contact set name='Carina L.' where code='CLM'";
$sth=$dbh->prepare($sql);
$sth->execute();

$sth will be null since the operation does not return any value.

Let's make a simple query script to inquiry the table contact we created throughout these articles, and output the results to an HTML page. Your script would go like:

#!/usr/bin/perl
# Script to inquiry contact name and phone number,
# sorted by contact last name

use DBI;
require "dbconn.lib";

my $dbh=DBI->connect(&connectionstring;&dbuser;&dbpassword);

my $sql="select code,last_name,first_name,phone from contact
order by last_name";
$sth=$dbh->prepare($sql);
$sth->execute();
# Disconnect from database
$dbh->disconnect;
# Mime tag
print "Content-type: text/html\n\n";
print "List of my contacts, sorted by last name:\n";
my @rows;
my total=0;
while (@rows=$sth->fetchrow_array)
{
print "Contact Name: $rows[0],$rows[1]<br>\n";
print "Contact Phone: $rows[2] <br><br>\n";
total++;
}
print "Total listed: $total\n";
# End of script

If you are only inserting, updating or deleting rows, you can use the method do instead of preparing and executing the command. This will speed up a little bit the process. For example:

$cmd="delete from test where code='CLM'";
$sth=do->($sql);

Let's go over a very basic example of an entry screen. First, I will list the HTML code of the entry page (add.html), and then the Perl script (add.cgi).

<html>
<title>Contact Data Entry (neotaz.com)</title>
<br>
<form method='post' action='cgi-bin/add.cgi'>
Contact Data Entry:<br><br>
Enter Contact Code: <input type='text' name='code'><br>
Enter First Name: <input type='text' name='first'><br>
Enter Last name: <input type='text' name='last'><br>
Enter Phone number: <input type='text' name='phone'><br>
<br>
<input type='submit' value=' Add '>
<input type='reset' value='Reset Form'>
</form>
</html>

(Please do no complain about the 'beauty' of the screens. This is just a sample to show you how it works!)

Now the Perl script (add.cgi):

#!/usr/bin/perl
# Add.cgi: script that adds a new contact (from add.html)
# Required libraries


use DBI;
require "parseform.lib";
require "dbconn.lib";
# Parse fields from form
&Parse_Form;
# Pass into local variables:
my $code=$formdata{'code'};
my $first=$formdata{'first'};
my $last=$formdata{'last'};
my $phone=$formdata{'phone'};
# Mime tag
print "Content-type: text/html\n\n";
# Simple validation code:
if ($code eq '')
{ print "Contact code cannot be empty!\n"; exit(0); }
# Save to database
# Connect to the database
my $dbh=DBI->connect(&connectionstring;&dbuser;&dbpassword);
my $sql="insert into contact values ('$code','$last','$first','phone'";
$sth=$dbh->do($sql);
# Disconnect from database
$dbh->disconnect;
# Inform user
print "Contact added successfully!\n";
print "<a href='add.html'>New entry</a>\n";
# End of script

Brief description of add.cgi:

#!/usr/bin/perl: this is the shebang line and indicates where to look for the Perl interpreter

use DBI: to use the DBI module

require "parseform.lib": this is a routine to parse the form. There is not enough space to show the code, but you can see it in detail here. Basically by calling the function &Parse_Form, you will have the value of the input fields of the HTML form, in the format $formdata{'<field name>'}, where <field name> is the input field name from the HTML form (in Perl, this type of array with string indexes is often called a hash). You don't have to know this program by heart or even understand it, , all you have to know is how to call the function and the resulting hash. Feel free to download and use this example.

require "dbconn.lib": the example that I mentioned above, about encapsulating the connection string, username and password.

&Parse_Form: function to parse the form and retrieve the values (from parseform.lib).

# Pass into local variables: passing of form values to local variables.

# Mime tag: prepares the script to output (with print) to an HTML page

# Simple validation code: validates if the contact code is empty

# Save to the database: connects, updates and disconnects the database

# Inform the user: let the user know that the operation was successful

Remember to always disconnect the database after you're done. Even though DBI implicitly closes the connection after you leave the script, it is recommended to explicitly disconnect from the database. First, to make sure the database handle is disconnected -freeing server resources-, and second, it's a good programming ethic (it's like shutting off the lights when leaving a room).

I hope this example has given you a good overview of how to organize and implement your web-based database application using Perl. Remember that these are just samples and you have to adjust the coding of your application to your design.

17. Links and books:

There are good reference books for both Perl and DBI:
"The Perl Cookbook"
(Tom Christiansen-Nathan Torkington) - O'Reilly and Assoc.
"Programming the Perl DBI" (Alligator Descartes-Tim Bunce) - O'Reilly and Assoc.

You can also visit our Develop section for more Perl documentation, or the Perl official website for more Perl database programming information.


Summary:

  • DBI architecture allows you to not worry about database connectivity details
  • Download DBI and DBD Perl modules from the CPAN website
  • Use DBI object instances (or handles) to connect to a database through the method connect()
  • Use DBI statement handles to execute queries and database updates

18. Next issue:

The next issue will be 'Interfacing Perl with a Database (II)' and the last part of this series of articles. We will go over more script examples (editing and deleting records), programming standards, some tips and tricks, and a little bit about security.

Marcelo Pham is a writer for the Linux.com Develop Section. Marcelo is a hardcore developer and systems consultant for BCA Inc. , and specializes in System Design, Business Applications and Cross-platform Integration.





   Page 1 of 1