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. keywords:perl,html,template,mvc,example,how,to,howto,mini,postgres,mysql,php,alternative,bioinformatics 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 module. http://defindit.com/readme_files/perl_sql_example.tar Download the tar file which will unarchive into a directory "example". 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 session_lib.pm 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 Windows. 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: http://defindit.com/readme_files/html-template.html 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: http://xi00.achs.virginia.edu/~twl8n/ 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: http://cowpeagenomics.med.virginia.edu/CGKB/