The Hypertext Markup Language was a tremendous innovation, but its principle weakness is its static nature. HTML was intended to be a method for sharing documents, not an interactive medium.

However, if Information Systems requirements mandate global visibility, the Web is an obvious choice. The Web has achieved such ubiquity that efforts to overcome its limitations are a primary developer concern. Technologies such as Java, ActiveX, and XML represent the recent attempts to extend the power of the Web.

This chapter presents two of the main open-source development environments for the web, PHP and Apache ModPerl. Perl is a text processing environment that has recently developed database extensions. The PHP language is deeply rooted in C and Awk and has always had a primary focus upon efficient use of SQL databases.

The information presented within this chapter is intended for Intel-based systems running Red Hat Linux 6.0. Much of the same information will be applicable, however, to other platforms as all of the software discussed here (Sybase, PostgreSQL, PHP 3.0) has been ported to a wide variety of UNIX implementations.

PHP

PHP (Professional Home Pages), the successor to PHP/FI, is a web utility that has enjoyed a great deal of popularity. It allows a C-like programming language with SQL extensions to be directly embedded within HTML documents, transforming them into active content. The software has recently been through a complete rewrite with a development emphasis upon performance. PHP requires no extensions to the browser as it relies upon the CGI interface.

PHP supports a number of database servers in addition to Sybase and PostgreSQL. These include Adabas, mSQL, MySQL, Oracle, and ODBC. PHP now also supports LDAP directory services and the IMAP mail protocol. More information about PHP is available in the documentation at their site at http://www.php.net.

Installing PHP

PHP is available from the PHP website or in the sybase/php-3.0.11.tar.gz file on the CD-ROM included with this text. You must obtain this file or a later version, if one is available.

There are two ways to configure PHP. It can be used either as a CGI binary, or as a module loaded at run-time for a supported Web server (Apache, the Netscape servers, and Microsoft IIS).

Loading PHP as an Apache module has a number of benefits. With the CGI approach, the entire PHP parser is loaded, executed, then terminated every time a browser accesses PHP on the server. When loaded as a module, the PHP parser becomes an integrated part of the Apache run-time environment. Because the PHP memory image is not loaded and destroyed by each access, the performance of the module configuration is significantly greater.

A module configuration will also permit much greater flexibility in the placement and access of PHP source files on the server. When used as a module, no reference to a cgi-bin directory is required in a URL, meaning that PHP application files can be placed anywhere within the web server document root.

However, there have been a number of security updates to the Apache Web server issued by Red Hat. Each time such an update occurs, the PHP-enabled Web server must be rebuilt from source. Such an arrangement can quickly become tedious. If the system is upgraded at a later date to a new version of Red Hat Linux, a rebuild of the web server will also most probably be necessary.

In compiling PHP as a CGI binary, PHP is built in such a way that it is entirely separate from the Web server installation. Updates to the Web server can be applied without fear of disturbing PHP. This configuration should be appropriate for all but the most high-traffic Web sites.

Running PHP as a CGI binary could create a serious security problem. Apache allows files (named .htaccess by default) to limit access to Web pages to specific IP addresses or to users who supply a valid username-password combination. PHP does not honor .htaccess files, and thus, if installed as a CGI binary, it can be used to read every Web page under the server's document root or users' directories, regardless of the access control that has been specified in .htaccess. Luckily, this is not an issue with the Apache module version of PHP.

Ultimately, users who are not overly concerned with the security of their Web server's directory tree and who do not anticipate high volume usage of PHP-enabled Web pages, should install the CGI version of the parser. All others should take the extra time to install PHP as an Apache module.

This chapter will assume the safer (if more arduous) approach to the preparation of the PHP-enabled web server daemon. The steps required to prepare PHP as an Apache module follow.

The script must be run by root. It assumes that the PHP binary distribution (php-3.0.11.tar.gz) and the source RPM for Apache (apache-1.3.6-7.src.rpm) are in the current directory. A good place to start this build is in root's home directory (/root, also known by BASH as ~root). Both the PHP and Apache source files are included on the attached CD-ROM in the sybase directory. RPM build methods are not used as they are not as convenient. A binary RPM version of mod_php exists, but it lacks Sybase support. The source is built outside of RPM to localize the build area.

There is some importance in the database selection options of the PHP configuration. In the script below, support is included for both Sybase and PostgreSQL. To remove support for PostgreSQL, remove the --with-pgsql directive below. To remove support for Sybase, remove the --with-sybase-ct directive. If Sybase has not been installed in /home/sybase as was suggested in the previous chapter, the installation directory must be specified as an option (i.e., --with-sybase-ct=/opt/sybase).

/etc/rc.d/init.d/httpd stop
mkdir apache
cd apache
rpm2cpio ../apache-1.3.6-7.src.rpm | cpio -i
tar xvzf apache_1.3.6.tar.gz
cd apache_1.3.6
for x in ../*.patch
do
	patch -p1 < $x
done
CFLAGS="-O2" LDFLAGS="-s" ./configure --prefix=/usr \
	--with-layout=RedHat \
	--enable-module=all \
	--disable-rule=WANTHSREGEX \
	--disable-module=auth_dbm \
	--with-perl=/usr/bin/perl
cd ../..
tar xvzf php-3.0.11.tar.gz 
cd php-3.0.11
# Remove the "--with-pgsql" line below to disable PostgreSQL.
# Remove the "--with-sybase-ct" line below to disable Sybase support.
LDFLAGS="-s" CFLAGS="-O3 -I/usr/include/pgsql" ./configure --disable-debug \
	--with-system-regex \
	--with-pgsql \
	--with-sybase-ct \
	--with-apache=../apache/apache_1.3.6
make
make install
cd ../apache/apache_1.3.6
CFLAGS="-O2" LDFLAGS="-s" ./configure --prefix=/usr \
	--with-layout=RedHat \
	--enable-module=all \
	--disable-rule=WANTHSREGEX \
	--disable-module=auth_dbm \
	--with-perl=/usr/bin/perl \
	--activate-module=src/modules/php3/libphp3.a
make
cd src
cp -f httpd /usr/sbin
echo 'AddType application/x-httpd-php3 .php3' >> /etc/httpd/conf/srm.conf
cd /etc/httpd/conf
sed 's/^LoadModule/#LoadModule/
s/^ClearModule/#ClearModule/
s/^AddModule/#AddModule/' httpd.conf > httpd.conf.avecphp
mv httpd.conf httpd.conf.sansphp
mv httpd.conf.avecphp httpd.conf
cd
/etc/rc.d/init.d/httpd start

The above script may take a great deal of time to run (between ten minutes to over an hour, depending upon the speed of the system). A copy of the script has been placed on the CD-ROM included in this text in the file sybase/PREP-APACHE.sh.

If the CGI configuration of PHP is desired, it can be prepared by root with the following commands:

tar xvzf php-3.0.11.tar.gz 
cd php-3.0.11
# Remove the "--with-pgsql" line below to disable PostgreSQL.
# Remove the "--with-sybase-ct" line below to disable Sybase support.
LDFLAGS="-s" CFLAGS="-O3 -I/usr/include/pgsql" ./configure --disable-debug \
	--with-pgsql \
	--with-sybase-ct \
	--with-system-regex
make
cp php /home/httpd/cgi-bin

Using SELECT from the Web

This section assumes that you have a firm working knowledge of HTML. If this is not the case, you might want to review the NCSA Beginner's Guide to HTML at http://www.ncsa.uiuc.edu/General/Internet/WWW/HTMLPrimer.html.

If you have installed a PHP-enabled web server and have entered the database commands described above, you are ready to build Web pages that use SQL.

PHP, and its earlier namesake, PHP/FI, use a C-like structured programming language that is embedded directly within HTML. PHP is used as a document preprocessor (much like the preprocessing stage of a C compiler, except that it is much more powerful).

If you are using PostgreSQL, copy the following HTML into a file on your system named /home/httpd/html/dbprint.php3:

<HTML>
<HEAD>
<TITLE>View Database Records</TITLE>
</HEAD>
<BODY>

<DIV ALIGN="center">

<P>View Database Records</P>

<TABLE BORDER>

<TR>
<TH>item</TH>
<TH>vendorname</TH>
<TH>quantity</TH>
</TR>

<?PHP

        $conn = pg_Connect("", "", "", "", "shopping");

        if (!$conn) {
                echo "</table>An error occurred.\n";
                exit;
        }

        $result = pg_Exec($conn,
                "SELECT list.item, vendors.vendorname, list.quantity
                        FROM list, vendors
                        WHERE list.vendorcode = vendors.vendorcode
                        ORDER BY list.item;");

        if (!$result) {
                echo "</table>An error occurred.\n";
                exit;
        }

        $num = pg_NumRows($result);
        $i = 0;

        while ($i < $num) {
                echo "<TR><TD>";
                echo pg_Result($result, $i, "item");
                echo "</TD><TD>";
                echo pg_Result($result, $i, "vendorname");
                echo "</TD><TD>";
                echo pg_Result($result, $i, "quantity");
                echo "</TD></TR>";
                $i++;
        }

        pg_FreeResult($result);
        pg_Close($conn);
?>

</TABLE>

</BODY>
</HTML>

The above PHP code extracts each vendorname in alphabetical order and prints it to the browser with a prefix of OPTION (as is required by the SELECT tag).

Noticing the ORDER BY clause in the above SELECT statement, an index on “vendors.vendorname” might be useful if the “vendors” table grows large.

The SQL-enabled document can be viewed with the URL http://127.0.0.1/dbform.php3 (the URL for a CGI implementation of PHP would be http://127.0.0.1/cgi-bin/php/dbform.php3). The Fully Qualified Domain Name (FQDN) can be substituted for localhost to view the page from browsers that are running on different hosts.

Assuming that everything runs smoothly, the following HTML should be presented:

View Database Records

item vendorname quantity
African Violet ACME Plant Store 1
Ice Cream Super Grocer 1
Napkins General Department Store 50
Root Beer Super Grocer 3
Spark Plugs General Auto Parts 4

PHP also has a directive that is similar to Server Side Includes (SSI) supported by most popular Web servers. If the command include("/some/path/to/a/file.html") is encountered in a PHP block, the specified file will be inserted and parsed by PHP. There is even a phpIncludePath variable that can be set that will allow the Web developer to dispense with path names. Conventional SSI directives will not be processed with the CGI version of PHP. URL locations on different hosts can also be included, such as include("http://www.redhat.com").

The CGI version of PHP can load files in users' home directories with URLs of the form: http://127.0.0.1/cgi-bin/php/~luser/dbprint.php3

The algorithm and syntax of the above HTML example are relatively simple. They are taken almost directly from the PHP/FI documentation.

Notice first the enclosing <?PHP and ?> tags that surround the non-HTML language. These mark the beginning and end respectively of PHP language statements. You may insert PHP statements delimited by these markers as many times as you like in your HTML file.

Here, these PHP statements do the following:

If the CGI version of PHP is prepared, the binary can be used as a script interpreter that can be called from the shell. Consider this variant of the previous script:

#!/usr/local/bin/php -f
<?PHP

        $conn = pg_Connect("", "", "", "", "shopping");

        if (!$conn) {
                echo "An error occurred.\n";
                exit;
        }

        $result = pg_Exec($conn,
                "SELECT list.item, vendors.vendorname, list.quantity
                        FROM list, vendors
                        WHERE list.vendorcode = vendors.vendorcode
                        ORDER BY list.item;");

        if (!$result) {
                echo "An error occurred.\n";
                exit;
        }

        $num = pg_NumRows($result);
        $i = 0;

        while ($i < $num) {
                echo pg_Result($result, $i, "item");
                echo "\t";
                echo pg_Result($result, $i, "vendorname");
                echo "\t";
                echo pg_Result($result, $i, "quantity");
                echo "\n";
                $i++;
        }

        pg_FreeResult($result);
        pg_Close($conn);
?>

If the CGI version of PHP is placed in /usr/local/bin, this script can be executed directly from the shell. For this reason, administrators often build PHP both as an Apache module and as a CGI. Notice specifically that /usr/local/bin cannot be accessed by the web server, so the CGI security issues do not apply. If installing the CGI version of PHP for shell scripts, ensure that a copy is not placed in /home/httpd/cgi-bin, or in any other position which would allow execution by the web server.

The following example shows how calls to a Sybase database can be executed:

<HTML>
<HEAD>
<TITLE>View Database Records</TITLE>
</HEAD>
<BODY>

<DIV ALIGN="center">

<P>View Database Records</P>

<TABLE BORDER>

<TR>
<TH>item</TH>
<TH>vendorname</TH>
<TH>quantity</TH>
</TR>

<?PHP
        error_reporting(1);

        $conn = sybase_connect("SYBASE", "nobody", "nobodypass");

        sybase_select_db("shopping",$conn);

        if (!$conn) {
                echo "</table>An error occurred.\n";
                exit;
        }

        $rc = sybase_query(
                "SELECT list.item, vendors.vendorname, list.quantity
                        FROM list, vendors
                        WHERE list.vendorcode = vendors.vendorcode
                        ORDER BY list.item",$conn);

        if (!$rc) {
                echo "</table>An error occurred.\n";
                exit;
        }

        while ($result = sybase_fetch_array($rc)) {

// The alternate syntaxes of the following two lines are both legal.

                echo "<TR><TD>$result[item]";
                echo "</TD><TD>" . $result["vendorname"];

                echo "</TD><TD>$result[quantity]";
                echo "</TD></TR>";
        }

        sybase_free_result($rc);
        sybase_close($conn);

?>

</TABLE>

</BODY>
</HTML>

The syntax used for the above Sybase example relies upon a PHP fetch_array() function. The fetch_array() functions return associative arrays, which allow the $result[] array elements to be addressed directly by column name, in addition to the numeric offsets. The only weakness to this approach is that if two tables have columns of the same name; the second such column cannot be addressed by the associative array. Sybase does have a method to rename a column, however:

SELECT "newname" = list.item, vendors.vendorname, list.quantity
	FROM list, vendors
	WHERE list.vendorcode = vendors.vendorcode
       	ORDER BY list.item

Using the preceding Sybase SQL syntax, the “item” column could be addressed within PHP as $results["newname"].

Sybase also generates warning messages within PHP when the active database is changed (with the call to sybase_select_db("shopping",$conn) above). The call to error_reporting(1) suppresses these messages. A Sybase login can be set to use a specific database by default by calling the sp_modifylogin stored procedure as was discussed in the previous chapter. If the login is set appropriately, the calls to sybase_select_db() and error_reporting(1) can be omitted.

Using INSERT from the Web

This section assumes that you have a firm working knowledge of HTML forms. If this is not the case, you might want to review the NCSA Guide to Fill-Out Forms at http://www.ncsa.uiuc.edu/SDG/Software/Mosaic/Docs/fill-out-forms/overview.html.

There are two main sections of HTML that will be used to add records to the database. First, an HTML form must be constructed so that the information can be easily entered by the browser. Second, the information must be returned and processed by PHP in order to add it to the table.

The implementation of the form, and specifically the vendor field, presents a design dilemma. The SELECT form tag is the most obvious HTML form element to use to present a pre-defined vendor list.

Should this list be hard-coded in the HTML? If another vendor is added to the “vendors” table, the HTML will not be automatically updated, and users will not be able to enter data against the new vendor.

The form elements for the vendor SELECT tag could alternately be generated by PHP each time the form is accessed. The drawback to this method is that the server load will be increased.

Another approach would be to generate the HTML for the form whenever the “vendors” table is updated. This could be accomplished in a variety of ways, some of which stem from the fact that the CGI version of PHP can be called from a Unix shell prompt as well as from a CGI environment.

The second alternative is presented below; each time the page for the data-entry form is accessed, PHP uses a SQL SELECT to obtain the data from the “vendors” table to build the HTML SELECT list.

Copy the following HTML into a file on your system named /home/httpd/html/dbform.php3:

<HTML>
<HEAD>
<TITLE>Insert Database Record</TITLE>
</HEAD>
<BODY>

<DIV ALIGN="center">

<H1>Add Database Record</H1>

<FORM METHOD="post" ACTION="dbinsert.php3">

<TABLE BORDER="0">

<TR>
<TD>New Item:</TD>
<TD><INPUT NAME="item"></TD>
</TR>

<TR>
<TD>Vendor:</TD>
<TD><SELECT NAME="vendor" SIZE="1">

<?PHP

        $conn = pg_Connect("", "", "", "", "grocery");

        if (!$conn) {
                echo "An error occurred.\n";
                exit;
        }

        $rc = pg_Exec($conn,
                "SELECT vendorcode, vendorname
                        FROM vendors
                        ORDER BY vendorname;");

        if (!$rc) {
                echo "An error occurred.\n";
                exit;
        }

	$num = pg_Numrows($rc);
	$i = 0;

	while ($i < $num) {
		echo "\n<OPTION VALUE=\"" .
			pg_Result($rc, $i, "vendorcode") .
			"\">" .
			pg_Result($rc, $i, "vendorname");
		$i++;
	}

        pg_FreeResult($result);
        pg_Close($conn);
?>

</SELECT></TD>
</TR>

<TR>
<TD>Quantity:</TD>
<TD><INPUT NAME="quantity"></TD>
</TR>


</TABLE>

<INPUT TYPE="submit">

</FORM>

</BODY>
</HTML>

Noticing the ORDER BY clause in the above SELECT statement, an index on “vendors.vendorname” might be useful if the “vendors” table grows large.

The above PHP code extracts each vendorname in alphabetical order and prints it to the browser with an OPTION prefix (as is required by the HTML SELECT tag). The numerical vendor code is recorded as the value to be passed if the respective vendor name is passed.

The SQL-enabled document can be viewed with the URL http://127.0.0.1/dbform.php3 (the URL for a CGI implementation of PHP would be http://127.0.0.1/cgi-bin/php/dbform.php3).

Assuming that everything runs smoothly, an HTML form such as this should be presented:

New Item:
Vendor:
Quantity:

The Sybase version of dbform.php3 might be:

<HTML>
<HEAD>
<TITLE>Insert Database Record</TITLE>
</HEAD>
<BODY>

<DIV ALIGN="center">

<H1>Add Database Record</H1>

<FORM METHOD="post" ACTION="dbinsert.php3">

<TABLE BORDER>

<TR>
<TD>New Item:</TD>
<TD><INPUT NAME="item">
</TR>

<TR>
<TD>Vendor:</TD>
<TD><SELECT NAME="vendor" SIZE="1">

<?PHP
        error_reporting(1);

        $conn = sybase_connect("SYBASE", "nobody", "nobodypass");

        sybase_select_db("shopping",$conn);

        if (!$conn) {
                echo "An error occurred.\n";
                exit;
        }

        $rc = sybase_query(
                "SELECT vendorcode, vendorname
                        FROM vendors
                        ORDER BY vendorname", $conn);

        if (!$rc) {
                echo "An error occurred.\n";
                exit;
        }

        while ($result = sybase_fetch_array($rc)) {
                echo "<OPTION VALUE=\"$result[vendorcode]\">$result[vendorname]";
        }

        sybase_free_result($rc);
        sybase_close($conn);
?>

</SELECT></TD>
</TR>

<TR>
<TD>Quantity:</TD>
<TD><INPUT NAME="quantity"></TD>
</TR>


</TABLE>

<INPUT TYPE="submit">

</FORM>

</BODY>
</HTML>

Now that the “front end” is complete, a “back end” must be implemented.

The following HTML should be copied into a file on the system named /home/httpd/html/dbinsert.php3:

<HTML>
<HEAD>
<TITLE>Confirm Database Insert</TITLE>
</HEAD>
<BODY>

<DIV ALIGN="center">

<?PHP

        $conn = pg_Connect("", "", "", "", "shopping");

        if (!$conn) {
                echo "An error occurred.\n";
                exit;
        }

        pg_Exec($conn,
                "INSERT INTO list (item, vendorcode, quantity)
                        VALUES ('$item', $vendor, $quantity);");

        pg_Close($conn);
?>

Database Updated

</BODY>
</HTML>

With complete information for the item, vendorcode and quantity fields of the “list” table, the data can then be inserted by the pg_Exec() call.

Notice that the “vendor” variable name given to the HTML SELECT tag in dbform.php3 is available to PHP in dbinsert.php3. The same is true of the “item” and “quantity” form elements that are used in the pg_Exec(). PHP carries variable information into the FORM ACTION without any intervention from the developer.

Notice the format of the SQL INSERT statement, in that it lists the field names that are respectively associated with the VALUES clause. This type type of SQL INSERT should always be used in development; it will allow greater changes to the table structure without the necessity of recoding. Any fields not referenced in the INSERT will be populated with NULL values if the table format will allow it.

The above PHP performs the SQL insert transactions and send a confirmation message to the browser. If these .php3 files were describing a high-production data entry interface, it might be useful to copy (or include()) dbform.php3 onto the end of dbinsert.php3 so that the form is continually presented.

The Sybase version of dbinsert.php is presented below:

<HTML>
<HEAD>
<TITLE>Confirm Database Insert</TITLE>
</HEAD>
<BODY>

<DIV ALIGN="center">

<?PHP
        error_reporting(1);

        $conn = sybase_connect("SYBASE", "nobody", "nobodypass");

        sybase_select_db("shopping",$conn);

        if (!$conn) {
                echo "An error occurred.\n";
                exit;
        }

        sybase_query(
                "INSERT INTO list (item, vendorcode, quantity)
                        VALUES ('$item', $vendor, $quantity)", $conn);

        sybase_close($conn);
?>

Database Updated

</BODY>
</HTML>

As a final coding example for the PHP section of this text, an authentication mechanism similar to an .htaccess file will be demonstrated that authenticates against a Sybase database.

Place the following code in /home/httpd/html/auth.php3:

<?php

	error_reporting(1);

	if(!isset($PHP_AUTH_USER))
	{
		Header("WWW-Authenticate: Basic realm=\"PHP Auth\"");
		Header("HTTP/1.0 401 Unauthorized");
		echo "You must log in to access these pages.\n";
		exit;
	}
	else
	{
		$link=sybase_connect("SYBASE", "nobody", "nobodypass");

		sybase_select_db("security",$link);

		$rc = sybase_query("SELECT password
					FROM auth
					WHERE name='$PHP_AUTH_USER'", $link);

		/* should be 1 row */
		if(sybase_num_rows($rc) != 1)
		{
			Header("WWW-Authenticate: Basic realm=\"PHP Auth\"");
			Header("HTTP/1.0 401 Unauthorized");
			echo "Attempt to log in as $PHP_AUTH_USER failed.\n";
			exit;
		}

		$row = sybase_fetch_array($rc);

		if($PHP_AUTH_PW != $row[password])
		{
			Header("WWW-Authenticate: Basic realm=\"PHP Auth\"");
			Header("HTTP/1.0 401 Unauthorized");
			echo "Attempt to log in as $PHP_AUTH_USER failed.\n";
			exit;
		}

		sybase_close($link);

		// $PHP_AUTH_USER and $PHP_AUTH_PW are global and authenticated.
	}
?>

The above example relies upon a database named “security” which contains a table named “auth” (although another database and/or table name could be substituted if they had a similar format). The “auth” table must contain name and password entries in clear-text.

Another PHP document could make use of the authentication engine by using the include() directive to read it. For example, in the following PHP document:

<?php

include("auth.php3");

?>

<HTML>
<HEAD>
<TITLE>Authenticated Document</TITLE>
</HEAD>
<BODY>

<?php

echo "some sensitive data";

?>

</BODY>
</HTML>

Any attempt to load this document would cause a password prompt to be presented.

Apache mod_perl

mod_perl is a special version of the perl interpreter that can be included within the Apache web server. mod_perl enables dramatic speed improvements on the execution of perl scripts from a web environment.

The RPM version of mod_perl documented here will not work with the Apache-PHP configuration documented earlier in this chapter; it must be installed on the stock Apache RPM distribution. It would be highly unwise to run both PHP and mod_perl at the same time, as the binary image of the web server would be huge. If the functionality of both packages is required, it might be perferable to configure two separate web servers, running at different ports, with the appropriate modules installed in each.

A mod_perl RPM image is included with Red Hat Linux, but it is not installed by default. It can be found in the RedHat/RPMS/mod_perl-1.19-2.i386.rpm file on the CD-ROM included with this text. Enter the following commands to install it:

/etc/rc.d/init.d/httpd stop

rpm -Uvh mod_perl-1.19-2.i386.rpm

echo 'LoadModule perl_module        modules/libperl.so
AddModule mod_perl.c

<Files ~ "\.pl$">
SetHandler perl-script
PerlHandler Apache::Registry
Options ExecCGI
</Files>

PerlSendHeader On' >> /etc/httpd/conf/httpd.conf

/etc/rc.d/init.d/httpd start

At this point, perl scripts that are placed in the document root will take advantage of mod_perl. To test mod_perl, create an example perl script:

echo '#!/usr/bin/perl

print "Content-type: text/html\n\n";

print $ENV{"GATEWAY_INTERFACE"};
print "<br>";
print $ENV{"MOD_PERL"};' > /home/httpd/html/ptest.pl

Then load this script with a URL of http://127.0.0.1/ptest.pl in a web browser. The output should be:

CGI-Perl/1.1
mod_perl/1.19

mod_perl can be much more destructive than PHP with improper perl source code. Read the documentation produced by the following commands to learn about programming structure limitations imposed by mod_perl:

perldoc cgi_to_mod_perl
perldoc mod_perl_traps

mod_perl is not for novice perl programmers - such developers will be much better advised to pursue PHP.

Pg Perl

The PostgreSQL interface to perl is included in the PostgreSQL packages on Red Hat Linux 6.0 (it is actually in postgresql-clients-6.4.2-3.i386.rpm).

The perl interface is relatively straightforward and is completely documented in the online manual page (use the command man Pg to access it).

Following is a script to access the contents of the shopping database from within perl:

#!/usr/bin/perl

use Pg;

$conn = Pg::connectdb("dbname = shopping");

$result = $conn->exec("SELECT list.item, vendors.vendorname, list.quantity
                        FROM list, vendors
                        WHERE list.vendorcode = vendors.vendorcode
                        ORDER BY list.item;");

$ntuples = $result->ntuples;

for($i=0; $i < $ntuples; $i++)
{
	$item = $result->getvalue($i, 0);
	$vendorname = $result->getvalue($i, 1);
	$quantity = $result->getvalue($i, 2);
	print "$quantity\t$item\t$vendorname\n";
}

Please notice that the user running the script is the PostgreSQL user who will attempt to access the database. In the examples in the last two chapters, only luser and nobody have such permissions, so the script will fail for everyone else.

sybperl

The preeminent perl connection tool for Sybase databases is the sybperl distribution written by Michael Peppler. The sybperl-2.10_02.tar.gz home page can be found at http://www.mbay.net/~mpeppler/ (which doubles as the author's personal home page). Additionally, Michael Peppler has written a DBI/DBD Perl interface for Sybase, but these interfaces are still in an alpha release, so they are not included here.

The script below builds only Sybase CT-Lib support for perl. Support for the older Sybase DB-Lib, and the utilities based upon it (such as the BCP routines) are not built as there are conflicts with various DBM packages. Resolving these conflicts is beyond the scope of this text.

Following is a script to build sybperl-2.10_02. The source package can be obtained from the CD-ROM included with this text in sybase/sybperl-2.10_02.tar.gz or from Michael Peppler's web site. Please note that if Sybase was not installed in /home/sybase as server name SYBASE with an administrator's password of “sapass”, then the script below must be modified to reflect these changes. To build sybperl, enter the following commands as root (the versions documented below take a long time to compile - over 17 minutes on a P166; do not interrupt):

tar xvzf sybperl-2.10_02.tar.gz
cd sybperl-2.10_02
sed 's/^SYBASE=.*$/SYBASE=\/home\/sybase/
s/^DBLIBVS=.*$/DBLIBVS=0/' CONFIG > CONFIG.NEW
mv -f CONFIG.NEW CONFIG
sed 's/PWD=/PWD=sapass/
s/SRV=.*$/SRV=SYBASE/' PWD > PWD.NEW
mv -f PWD.NEW PWD
perl Makefile.PL
make
make test
make install

Assuming that perl is enabled with the CT-Lib functions of Sybperl, the following example, when run from a shell, will fetch a SQL query from Sybase:

#!/usr/bin/perl

# Load the Sybase::CTlib module:
use Sybase::CTlib;

# Allocate a new Database 'handle':
$dbh = new Sybase::CTlib 'luser', 'luserpass', 'SYBASE';

# Select the target database:
$dbh->ct_sql("use shopping");

# Send the query to the server:
$dbh->ct_execute("SELECT list.item, vendors.vendorname, list.quantity
                        FROM list, vendors
                        WHERE list.vendorcode = vendors.vendorcode
                        ORDER BY list.item");

# Retrieve the result sets
while($dbh->ct_results($restype) == CS_SUCCEED) {

    # Skip non-fetchable results:
    next unless $dbh->ct_fetchable($restype);

    # Retrieve actual data rows:
    while(($item, $vendorname, $quantity) = $dbh->ct_fetch) {
        print "$quantity\t$item\t$vendorname\n";
    }
}

The script could easily be modified to run from an HTML context.