Form Access with CGI.pm
Most Perl CGI programs begin with CGI.pm, the Perl module that handles Web server interaction through the CGI. CGI.pm provides a simple interface to headers, form variables, and other architectural elements that comprise the communications layer between a Web server and a CGI application.
With Web programming in mind, CGI.pm offers methods for creating forms and other HTML structures, as well as methods for accessing form variables and modifying page attributes. As Listing 1 shows, HTML forms can be written entirely using method calls to the CGI object.
Listing 1-Database Access Through SQL (sql_query.pl)
001 #!/usr/bin/perl
002
003 #-----------------------------------------
004 #
005 # sql_get.pl - CGI application example
006 #
007 #-----------------------------------------
008
009 # include libraries
010 require 5.6.0;
011 use strict;
012 use warnings;
013 use CGI;
014 use DBI;
015
016 # declare some variables
017 my ($q, $dbh, $sth, $query, $datasource, $user, $password, $error,
018 $field, $result, $results);
019 my (@datasources);
020
021 # initiate CGI parser object
022 $q = CGI->new;
023
024 # begin the page
025 print $q->header,
026 $q->start_html('SQL Database Viewer'),
027 $q->h2('SQL Database Viewer');
028
029 # build a (safe) list of data sources
030 foreach (DBI->available_drivers)
031 {
032 eval {
033 foreach (DBI->data_sources($_))
034 {
035 push @datasources, $_;
036 }
037 };
038 }
039
040
041 # display the entry form
042 print $q->start_form;
043
044 print qq{<p>Choose a datasource:</p>\n};
045 print $q->popup_menu(-name => 'datasource',
046 -values => \@datasources);
047
048 print qq{<p>Specify username/password:</p>\n};
049 print $q->textfield(-name => 'user',
050 -size => 10);
051 print $q->password_field(-name => 'password',
052 -size => 10);
053
054 print qq{<p>Enter a SELECT query:</p>\n};
055 print $q->textarea(-name => 'query',
056 -rows => '5',
057 -cols => '40',
058 -wrap => 'virtual');
059
060 print $q->p, $q->submit;
061 print $q->end_form;
062
063 # get form variables
064 $datasource = $q->param('datasource');
065 $user = $q->param('user');
066 $password = $q->param('password');
067 $query = $q->param('query');
068
069 # check form variables
070 if ($query)
071 {
072 $error = "Improper datasource specified" unless ($datasource =~ /^dbi/i);
073 $error = "Query should start with SELECT" unless ($query =~ /^select/i);
074 }
075
076 # if a query is specified and form variables are OK,
077 if ($query and !$error)
078 {
079 # connect to the database
080 $dbh = DBI->connect($datasource, $user, $password)
081 or $error = "Connection failed: $DBI::errstr";
082
083 # if the database connection worked, send the query
084 unless ($error)
085 {
086 $sth = $dbh->prepare($query)
087 or $error = "Query failed: $DBI::errstr";
088 $sth->execute or $error = "Query failed: $DBI::errstr";
089 }
090 }
091
092 # if any errors are present, display the error and exit
093 if ($error) {print $q->p("Error: $error"), $q->end_html and exit;}
094
095 # if the query produced an output,
096 if ($query and $sth->{NAME})
097 {
098 # start a data table
099 print qq{<table border="1">\n};
100 print qq{<tr>\n};
101
102 # display the fields as table headers
103 foreach $field (@{$sth->{NAME}})
104 {
105 print qq{<th>$field</th>\n};
106 }
107 print qq{</tr>\n};
108
109 # display the results in a table
110 while ($results = $sth->fetchrow_arrayref)
111 {
112 print qq{<tr>\n};
113 foreach $result (@$results)
114 {
115 print qq{<td>$result</td>\n};
116 }
117 print qq{</tr>\n};
118 }
119
120 # finish the data table
121 print qq{</table>\n};
122 }
123
124 # finish the page
125 print $q->end_html;
126
127 # disconnect from the database
128 $dbh->disconnect if $dbh;
After including the necessary libraries and setting up some variables for use in the program, line 021 of the sql_query.pl program creates a new CGI query object, $q. The $q object provides a unified interface to all CGI methods, including form creation and query variable access.
After setting up the environment, lines 029 and 032 call methods off the DBI object to build a list of available data sources. Then, lines 041 through 060 create a basic HTML form with a drop-down box for choosing the DBI data source, text boxes for username and password entry, a textarea box for the Structured Query Language (SQL) query, and a Submit button. (DBI access methods are covered in more detail in the "Accessing a Database with DBI.pm" section of this chapter.)
The program is designed to be accessed multiple times with different information. Forms generated by the program submit information back to it with more information added each time the user submits. This style of programming is common with CGI, but it is much less common in more modern Web programming styles because it leads to monolithic code bases as programs get larger. Large, single programs become difficult to develop in a Web environment, especially on the time scales that Web programming requires. For this example, however, the multiple-access style is useful in keeping the program portable and compact.