Defindit Docs and Howto Home

This page last modified: Feb 02 2009
title:Perl SQL example using DBI/DBD and HTML::Template
description:Working examples of Perl DBI code reading and updating a PostgreSQL database and displaying the results in a web page.

This is a working example of Perl code capable of reading CGI input,
using a local config file, reading and updating a SQL database, and
the using an HTML template file rendered via the HTML::Template Perl

Download the tar file which will unarchive into a directory

cd ~/public_html
tar -xvf perl_sql_example.tar

As a whole, this forms an example of a well organized and complete
example of a web-enabled database application. The only missing piece
is some explicit use of CGI. CGI is covered elsewhere.

Enclosed is a readme and several source files. Included is
a Perl module with an extensive set of support routines. This API
called makes working with SQL, CGI, and local
configuration far easier than building these apps from scratch.

This example is available as a tar archive because the intended audience
is Linux developers. However, with minor changes this same code should
work fine under Windows. Windows users will need to install Active
State's Perl distribution. This code has not been tested under

This code has not been tested on Apple Mac OSX, but since I develop
and deploy on OSX all the time, this code should work with OSX
(perhaps with very minor changes). 

This example does not cover the more complex issues of database
security and web CGI security. I will warn you not to allow anything
entered on a web page (even in hidden fields) which will be exposed to
the Linux command line (via the system() command or via backticks), or
to a SQL command. In other words, do not allow unmodified CGI input in
any executable command.

More notes about HTML::Template are available at:

The example code in perl_sql_example.tar is part of the basic
underpinnings of bioinformatics code. It is necessary to integrate
this example with XML parsing, and the ability to execute external
programs. These aspects are covered in the API included with the UVa
OMSSA Web package at:

There are many non-trivial problems involved in bioinformatics, but we
have code which solves many of those issues. For example, how do you
keep track of the results files from the BLAST annotation of 1.25
million genespace sequences? How do you efficiently rerun this
annotation against 6 (or more) search libraries? How do you generate
reports from the 20 to 30 million BLAST hits that end up in the SQL
database as a result of parsing the BLAST output? We have projects
that answer all these questions. For a sample, please see the Cowpea
Genomics Knowledge Base: