The source code of DBviaProxy.pl :
<![CDATA[
#!/usr/bin/perl
# For use as a CGI script:
# As target of a link or form GET action to a URL like
# http://shark..../~username/.../DBviaProxy.pl
# or http://www..../cgi-bin/username/.../DBviaProxy.pl
# etc (the precise form depends on the server set-up).
# It returns an HTML document containing information fetched from
# an Oracle database. The queried results are formatted "nicely"
# as an HTML table. [The HTML produced is very crude.]
# The line at the top is for CGI use on a Unix system.
# Connects to a nominated Oracle database via the Proxy Server
# at oracle.cs.stir.ac.uk and then does a straightforward retrieval
# (an SQL SELECT query) of data from the table Panto.
# The proxy server relays requests and results between the machine
# that this script executes on and the machine that the Oracle
# database is actually on.
#
# Customizable in various ways:
# o The definitions below of variables
# $dbname, $username and $password
# should be altered to use suitable strings to identify the actual
# database and to give your authorization info.
# o Could SELECT from any tables.
# o Could CREATE, INSERT, etc instead or as well.
# o Could use CGI parameters:
# typically embedding them the SQL query string(s).
# o Could generate more/different HTML.
# Author: SB Jones June 2002, based on R Bland
use strict ;
use DBI ; # Perl module for batabase access
my ($dbname, # the database name
$username,# for accessing the database
$password,# for accessing the database
$dbh, # database handle
$sth, # statement handle
@row, # row (record)
%attr, # error-checking attributes for DBI
$ProxyID # machine, port, database
) ;
my ($season, $title) ;
# Configuration data
$dbname = "<dbname>" # Replace with, eg, "ora1a11" or "CS"
$username = "<username>" # Replace with, eg, "abc001"
$password = "<password>" # Replace with, eg, "all4s9n"
# Start outputting the response document
print "Content-Type: text/html\n";
print "\n";
%attr = (
PrintError => 0, # don't report errors through warn()
RaiseError => 1) ; # do report errors through die()
# Build description of the proxy location and database to connect to
$ProxyID="hostname=oracle.cs.stir.ac.uk;port=57005;dsn=dbi:Oracle:$dbname";
# Now actually open connection to the database
# (stays open until disconnect, below)
$dbh = DBI->connect("dbi:Proxy:$ProxyID", $username, $password, \%attr);
# Basic technique is to do the following for each query (or other action)
# to be made on the database:
# "Prepare" an SQL statement (the prepared statement is held in object $sth)
$sth = $dbh->prepare("SELECT DISTINCT Season, Title FROM Panto") ;
# Now execute the prepared statement: the query is carried out and the
# "result set" (a table as described by the SELECT) is held in $sth
$sth->execute ;
print "<TITLE>Retrieving information from Oracle via proxy server</TITLE>\n";
# Start outputting the HTML table
print "<TABLE BORDER=2>\n";
# Now here is the "idiom" for stepping through the result set in $sth,
# one row at a time, splitting each row into its two data items (a season
# and a title, as specified in the SELECT), placing the items into variables
# $season and $title. The loop body outputs one row of the HTML table,
# incorporating the season and title strings from the row at that repetition.
# The while loop test is quite elaborate, perhaps strange, certainly subtle,
# but quite typical!
while (($season, $title) = $sth->fetchrow_array()) {
print "<TR><TD>$season</TD><TD>$title</TD></TR>\n" ; }
# Finished outputting the rows, now finish the HTML table
print "</TABLE>\n";
# And finally disconnect from the database
$dbh->disconnect ;
print "<HR>\n" ;
]]>