Perl Tutorial Part 4 - Put Your Data On The Web
Last Changed $Date: 2005-02-28 15:50:28 +0000 (Mon, 28 Feb 2005) $ Publisher: Easysoft Limited Copyright © 1993-2005 by Easysoft Limited. All rights reserved. You may not reverse engineer, decompile or disassemble this document. Information in this document is subject to change without notice. Companies, names, and data used in examples are fictitious unless otherwise noted. The names of companies referred to herein, their corporate logos, the names of their hardware and software may be trade names, trademarks or registered trademarks of their respective owners. Easysoft and the Easysoft logo are registered trademarks of Easysoft Limited.
This is part 4 of a series of Easysoft tutorials on using Perl DBI with DBD::ODBC. This tutorial will provide basic details for creating web pages in HTML including data from your database via the Common Gateway Interface (CGI).
There are many methods to put your data on the web. This is an introductory totorial describing the basics of CGI, Perl and HTML::Template. There are many methods you can use including mod_perl (for optimisation, connection caching etc) but we start with the basics here.
We used Perl 5.8 but you only need the minimum required by the DBI and DBD::ODBC modules which is currently 5.6. Use perl --version to see what version of Perl you have installed.
We used DBI 1.45 but this tutorial should work with anything after
1.40. To see if you have a recent enough version of DBI installed run:
perl -e 'use DBI 1.40;'
If you get an error like "DBI version 1.40 required--this is only version 1.30 at -e line 1." you need to upgrade DBI.
If you get an error saying DBI cannot be found in @INC you've probably not got DBI installed.
Go to CPAN to get an up to date version of the DBI module.
We used DBD::ODBC 1.11. You can use similar methods as above to determine if DBD::ODBC is installed and to see what version you have:
To check you have the DBD::ODBC module installed:
perl -e 'use DBD::ODBC;'If you have not got DBD::ODBC installed you should see Applications and Interfaces: Perl DBI and DBD::ODBC for instructions.
To show the DBD::ODBC version:
perl -MDBD::ODBC -e 'print $DBD::ODBC::VERSION;'
To show all drivers DBI knows about and their versions:
perl -MDBI -e 'DBI->installed_versions;'
Go to CPAN to get an up to date version of the DBD::ODBC module.
You do not have to use DBD::ODBC for this tutorial. Any other DBD under Perl should work.
If you choose to use DBD::ODBC you will need an ODBC driver for most of this tutorial and we recommend you use an ODBC driver manager under DBD::ODBC (see Applications and Interfaces: Perl DBI and DBD::ODBC). Easysoft can supply ODBC drivers for many databases and operating systems and all our ODBC drivers come with the unixODBC driver manager.
You probably have the unixODBC driver manager installed if you have the odbcinst command (for ODBC drivers from Easysoft, the unixODBC driver manager is located in /usr/local/easysoft/unixODBC and the odbcinst command in the bin sub directory of that path.
We used unixODBC 2.2.9 in this tutorial. You can find out the unixODBC version you are using with:odbcinst --version
You will also need an ODBC driver for UNIX which can communicate with MS SQL Server. We used the Easysoft ODBC-ODBC Bridge as the ODBC driver to access a remote MS SQL Server database from UNIX.
To follow this tutorial properly you will really need a web server (like Apache) but it is possible to to do without by redirecting the Perl script output to a file and viewing the file in a web browser.
If you use a web server, you will need to enable CGI and tell the web server that files ending in the .pl extension are CGI scripts. Consult your web server documentation for details.
This tutorial assumes you have read or understand all the concepts in most of the previous tutorials DBD::ODBC Tutorial Part 1 - Drivers, Data Sources and Connection and DBD::ODBC Tutorial Part 2 - Introduction to retrieving data from your database. There are other tutorials in this series but part 1 and 2 are the only ones you need to understand.
Operating SystemThis tutorial was designed on UNIX and we have assumed you are using UNIX too. However, all the Perl examples should work equally well on Windows so long as minor alterations for the command line are made.
ODBC Driver ManagerIf you are using DBD::ODBC we have assumed you are using the unixODBC driver manager. All discussion in this document relating to the location and definition of ODBC data sources is for unixODBC.
The Common Gateway Interface (CGI) is a method by which a web server runs programs on the server returning HTML output to the web server which is supplied to the browser. i.e. a browser accessing a URL on the server which is actually the name of a program and that program outputs HTML which is sent back to the browser. The power of CGI is hence obvious since each time the program is run it can produce different results and hence differs from a static HTML page.
CGI also defines a method by which a browser can supply information to the program to be run. This can be in the form of GETDATA (i.e. elements in the URL which are interpreted as variable declarations) or submitted form data (more of this later).
In Perl the most basic program is:
#!/usr/bin/perl -w print "content-type: text/html\n\n"; print "Hello\n";
The first thing to note here is the content-type line which tells the web server the content returned by this CGI script is of mime type text/html and this is followed by two newlines to split the header information from the HTML output i.e. The first thing a CGI program must do is provide the content-type to the web server and the web server recognises the end of the HTTP header information by the double newline. In fact this is probably one of the most common mistakes in CGI i.e. not outputting the content-type first. e.g.
#!/usr/bin/perl -w print "Hello\n";
generally outputs a server error like:
Error message: malformed header from script. Bad header=<html><body>Hello</body></html: /http/htdocs/tut4_2.pl
The second thing to note is that the CGI program (xxx.pl) should be executable and contain the '#!' syntax to indicate it is perl to be passed to the Perl interpreter or you must associate the .pl extension with CGI in your web server. Generally, if you don't do this your web server will treat the document as static content and display the content of your perl script instead of the result of running it.
So now you understand the basics of CGI we combine a small CGI script with some Perl to display the contents of a database table from your web server. Since the content-type is text/html all the Perl output must be HTML. Suppose you have the table TEST containing 2 columns, 'productcode' integer and 'b' char(32) product:
#!/usr/bin/perl -w use DBI; # output the content-type so the web server knows print "content-type: text/html\n\n"; print '<html><head><title>Basic CGI</title><head><body>'; # # you should change the connect method call to use the DBD you are # using. The following examples all use DBD::ODBC and hence the # first argument to the connect method is 'dbi:ODBC:DSN'. # my $dbh = DBI->connect('dbi:ODBC:test', 'dbuser', 'dbauth'); my $sql = q/select productcode, product from TEST/; my $sth = $dbh->prepare($sql); $sth->execute; print '<table border="1">\n'; # table headings are SQL column names print "<tr><th>$sth->{NAME}->[0]</th><th>$sth->{NAME}->[1]</th></tr>"; while (my @row = $sth->fetchrow_array) { print "<tr><td>$row[0]</td><td>$row[1]</td></tr>\n"; } print "</table>\n"; print "</body></html>\n";
This does the job and you can write all your Perl CGI like this but:
The CGI module you can find on cpan can help with alot of the CGI processing. Here we introduce it rather simply to take care of the mime-type output, the <HTML>, <BODY> tags, the title and the tags to end the HTML.
Out simple program becomes:
#!/usr/bin/perl -w use DBI; use CGI; my $cgi = new CGI; # output the content-type so the web server knows print $cgi->header; # output the start HTML sequence with a title print $cgi->start_html(-title=>'Basic CGI'); # # you should change the connect method call to use the DBD you are # using. The following examples all use DBD::ODBC and hence the # first argument to the connect method is 'dbi:ODBC:DSN'. # my $dbh = DBI->connect('dbi:ODBC:test', 'dbuser', 'dbauth'); my $sql = q/select productcode, product from TEST/; my $sth = $dbh->prepare($sql); $sth->execute; print $cgi->table({border=1}); # table headings are SQL column names print "<tr><th>$sth->{NAME}->[0]</th><th>$sth->{NAME}->[1]</th></tr>"; while (my @row = $sth->fetchrow_array) { print "<tr><td>$row[0]</td><td>$row[1]</td></tr>\n"; } print $cgi->end_table; print $cgi->end_html;
Here we have used the CGI as follows :
In fact the CGI module is a lot more powerful than this and you could output all the table in one go with a combination of the CGI Tr method and DBI's fetchrow_xxx method but the next section shows a totally differnet method which allows you to separate your HTML entirely from the data you extracted from the database.
The HTML::Template module (find it on cpan) allows you to separate the HTML from the data and metadata you extract from your database. This has the advantage that you can have multiple people working indepentently on the Perl script and the HTML
First you create a template which is basically your HTML with markers showing where HTML::Template should substitute the data and metadata you extract from your database. Then you load the template and set the variables you want to substitute in the template and finally you output the combination of your template and the substituted variables.
Our template for the basic program we started with is:
<html> <title><TMPL_VAR name=TITLE></title> <body> <table border="1"> <tr> <TMPL_LOOP NAME=HEADINGS> <th><TMPL_VAR NAME=HEADINGS></th> </TMPL_LOOP> </tr> <TMPL_LOOP NAME=ROWS> <tr><td><TMPL_VAR NAME=PRODUCTCODE></td><td><TMPL_VAR NAME=PRODUCT></td></tr> </TMPL_LOOP> </table> </body> </html>
We save this template as tut4_5.tpl.
TMPL_VAR NAME=xxx names the variables we are going to substitute. When we want to do a substitution in the template we pass the name of the TMPL_VAR and its value to HTML::Template. For loops (TMPL_LOOP) we need to pass the name of the loop (e.g. HEADINGS) and then an array reference where each element of the array is a reference to a hash where the keys are the names of the TMPL_VAR variables in the loop.
Out program now becomes:
#!/usr/bin/perl -w use strict; use DBI; use HTML::Template; # output the content-type so the web server knows my $template = HTML::Template->new(filename => 'tut4_5.tpl'); $template->param(TITLE=>'Basic CGI'); # # you should change the connect method call to use the DBD you are # using. The following examples all use DBD::ODBC and hence the # first argument to the connect method is 'dbi:ODBC:DSN'. # my $dbh = DBI->connect('dbi:ODBC:test', db_user', 'db_pass'); my $sql = q/select productcode, product from TEST/; my $sth = $dbh->prepare($sql); $sth->execute; # # create an array where each element is a reference to a hash # where the keys are the name of the TMPL_VAR we want to substitute. # In this case the TMPL_LOOP is HEADINGS and the name of the hash key # is HEADING. i.e. we have # $headings[0] = {HEADING=>'value'} # $headings[1] = {HEADING=>'value'} # my @headings; foreach (@{$sth->{NAME}}) { my %rowh; $rowh{HEADING} = $_; push @headings, \%rowh; } $template->param(HEADINGS=>\@headings); # # For the rows we know the TMPL_VAR names are PRODUCT and PRODUCTCODE. # The loop TMPL_LOOP is called ROWS. # For each ROW we need a hash where the keys are PRODUCTCODE and PRODUCT. # @rows is an array with each element a reference to a ROW and it has as # many elements as there are rows in the quert result. # my @rows; while (my @data_row = $sth->fetchrow_array) { my %row; $row{PRODUCTCODE} = $data_row[0]; $row{PRODUCT} = $data_row[1]; push @rows, \%row; } $template->param(ROWS=>\@rows); print "Content-Type: text/html\n\n", $template->output;
Although, initially the perl looks more complex we have managed complete separation of the HTML from the data. So long as the SQL query does not change (or the names of the TMPL_LOOP or TMPL_VAR names does not change) one person can edit the style in the template as much as they want without needing to reference the Perl script.
As in many cases in Perl this script can be simplified even further. If the names of the TMPL_VAR variables matches the column names in the query then we can use the fetchrow_arrayref method but we have avoided this here to illustrate the basic principle.
Modules exist like HTML::Template which are even more flexible but this method can be used to put up basic data from your web pages very quickly.
See the next section for details on modifying this basic script to return diffferent results depending on the passed URL.
So far we have examined a basic CGI program which displays HTML output based on results from an SQL query but what if we want to use the same script to produce different results depending on the URL?
CGI supports what is called GETDATA arguments. This is where the URL includes the names of CGI variables and their values as in http://myserver/myscript.pl?var=value. In this example the Perl script can obtain the var=value and use it in the SQL query to produce a result dependent on the URL. The CGI module can automatically handle the GETDATA arguments such that you can either have the variable "var" created automatically for you (we advise against this) or such that you can ask the CGI module the value of the "var" GETDATA argument. In this way you can tailor you database query to the requested URL. e.g. a user enters http://myserver/myscript.pl?productcode=fred to get a list of the productcodes called "fred". Extending the HTML::Template example from the previous section we need to add code to query the value of the productcode GETDATA argument then substitute this into our query.
#!/usr/bin/perl -w use strict; use DBI; use HTML::Template; use CGI; # we add CGI to get hold of the GETDATA arguments my $cgi = new CGI; # output the content-type so the web server knows my $template = HTML::Template->new(filename => 'tut4_6.tpl'); $template->param(TITLE=>'Basic CGI'); # # Get productcode GETDATA argument # What happens here is if the URL ends in "?productcode=xxx" then # $cgi->param('productcode') returns "xxx" # my $productcode = $cgi->param('productcode'); $productcode = '%' if (!defined($productcode)); my $dbh = DBI->connect('dbi:ODBC:test', 'dbuser', 'dbpass'); # change the SQL to select the productcode = the value of the GETDATA # argument productcode my $sql = q/select productcode, product from TEST/; $sql .= " where f1 like '$productcode'"; # # the rest of this script is the same as the previous example # my $sth = $dbh->prepare($sql); $sth->execute; my @headings; foreach (@{$sth->{NAME}}) { my %rowh; $rowh{HEADING} = $_; push @headings, \%rowh; } $template->param(HEADINGS=>\@headings); my @rows; while (my @data_row = $sth->fetchrow_array) { my %row; $row{PRODUCTCODE} = $data_row[0]; $row{PRODUCT} = $data_row[1]; push @rows, \%row; } $template->param(ROWS=>\@rows); print "Content-Type: text/html\n\n", $template->output;
What is form data? An HTML form defines fields which the user can enter text, click on check boxes or select from a list and then submit the form by clicking on the form submit button. The form submit button is linked to a URL to which the browser sends a HTTP GET request and the web server supplies the form data on stdin (standard input) for the CGI program. The CGI program can read stdin, parse the form data and act on it or you can use the CGI module (recommended) to do this for you.
If you use the CGI module then form data is available via the param method just as the example above. If you choose not to use the CGI module then the form data is supplied to stdin in the form:
fieldname=value&field2name=value
Why we recommend you use the CGI module to handle form data is that you will find parsing form data on stdin more complex then you might think given this description. e.g. certain characters in form data are encoded in a hex format like a ' ' is encoded as %20.
Suppose you have the simple HTML form in your HTML:
<form method='post' action='/cgi/myperl.pl'> Product Code<input type='text' name='productcode' maxlength='64' size='32'> <input type='image' name='btnsubmit' src='/images/btn_submit.gif' border='0'> </form>
The form defines the method 'post' which causes the form data to be supplied on stdin for the CGI script and an action which is the URL to submit the form to (this is the address of your perl CGI script). The 'input' types define the 'name' of the form field (productcode in this case and this is the name you pass to CGI's param method) and various arguments like 'maxlength' and 'size' which define the maximum amount of characters that can be input into this field and the 'size' of the field in the form (consult a good HTML reference). The submit button in this case is an image which where the user clicks on issues a HTTP GET request to the URL in the action field.
When a user accesses the web page containing your form he can enter a value for the productcode and when the form is submitted your SQL adds a where clause to return only rows matching that productcode.
Our previous perl CGI script remains the same as the one in Altering the program to produce different results based on the URL because the CGI module transparently handles form data and GETDATA requests.