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 (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.
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
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.
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
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:
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>
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.
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.
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.
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.