APPENDIX
L Example Web Scripts
Objectives
In this appendix you will learn:
• The use of client-side JavaScript.
• The use of PHP and PostgresQL.
• The use of CGI and Perl.
• The use of JDBC to retrieve data from a database.
• The use of SQLJ to retrieve data from a database.
• The use of (server-side) JavaServer Pages (JSP).
• The use of (server-side) Active Server Pages and ActiveX Data Objects.
• The use of Oracle’s PL/SQL Server Pages (PSPs).
In Chapter 30 we examined in some detail the World Wide Web (Web) and some
of the current approaches to integrating databases into the Web environment. In
this appendix we provide a number of examples that illustrate some of the con-
cepts covered in that chapter. We assume the reader is familiar with the concepts
introduced in Chapter 30. The examples in this appendix are drawn from the
DreamHome case study documented in Section 11.4 and Appendix A.
L.1 JavaScript
In Section 30.3.1 we introduced the scripting language JavaScript. The following
example illustrates the use of client-side JavaScript. We illustrate the use of server-
side JavaScript using Microsoft’s Active Server Pages in Example L.7.
L-1
L-2 | Appendix L Example Web Scripts
EXAMPLE L.1 Use of JavaScript to display book details
Create a JavaScript program to emulate the HTML page shown in Figure 30.2(b).
Figure L.1 illustrates the use of client-side JavaScript to create a Web page corre-
sponding to Figure 30.2(b). In this example, an array is used to hold a list of pages
that the user can access and another array to hold the corresponding URLs for each
page. An HTML form is created with an onChange event specified to call a function
(goPage) when the user selects a page.
(continued)
Figure L.1 Example of client-side JavaScript to display Web page shown in Figure 30.2(b).
L.2 PHP and PostgreSQL | L-3
Figure L.1 (Continued )
L.2 PHP and PostgreSQL
In Section 30.3.3 we introduced the scripting language PHP. The following exam-
ple illustrates the use of server-side PHP.
EXAMPLE L.2 Use of PHP and PostgreSQL
Produce a PHP script to list all records in the Staff table.
The code for the script is shown in Figure L.2. The PHP part of the HTML file is delim-
ited by <?php . . . ?>. The PHP function pg_pconnect is used to connect to the data source,
pg_Exec is used to run an SQL query, pg_NumRows is used to determine how many rows
are in the result set, and pg_result is used to access each field of the result set.
Dynamically generated HTML is created using the echo command.
(continued)
Figure L.2 Sample PHP script to connect to PostgreSQL.
L-4 | Appendix L Example Web Scripts
Figure L.2 (Continued )
L.3 CGI and Perl
In Section 30.3.3 we introduced the high-level interpreted programming language
Perl and in Section 30.4 we examined the Common Gateway Interface (CGI) pro-
tocol. The following example illustrates the use of CGI and Perl.
EXAMPLE L.3 Use of CGI and Perl
Produce a Perl script to take an arbitrary SELECT statement and write out the results.
The code for the script is shown in Figure L.3. The use statement allows a Perl script
to include the contents of predefined libraries. The CGI library is included to pro-
vide functionality that makes it easier to write the HTML to be sent to the browser.
For example, the CGI library contains keywords that represent HTML tags, which
are used in this script to create an HTTP header and footer (print header and print
end_html).
L.4 Java and JDBC | L-5
Figure L.3 Sample CGI/Perl script to run an arbitrary query.
L.4 Java and JDBC
In Section 30.7.1 we introduced JDBC for Java as one way to connect a Java appli-
cation to a DBMS. The following example illustrates the use of JDBC.
L-6 | Appendix L Example Web Scripts
EXAMPLE L.4 Use of JDBC
Produce a Java application to list all records in the Staff table.
This example demonstrates the use of JDBC within a standalone Java application using
the JDBC–ODBC bridge and Microsoft’s 32-bit ODBC driver. The code for the Java
application is shown in Figure L.4.
(continued)
Figure L.4 Sample Java application using JDBC.
L.5 Java and SQLJ | L-7
Figure L.4 (Continued )
L.5 Java and SQLJ
In Section 30.7.2 we briefly introduced SQLJ as an alternative approach to access-
ing databases from a Java application. The following example illustrates the use
of SQLJ.
EXAMPLE L.5 Using SQLJ to retrieve data
Produce an SQLJ program to print out the properties for a particular member of staff.
The template program is shown in Figure L.5. The function of the program is similar
to that of Example I.3, which used (static) embedded SQL. In SQLJ, embedded state-
ments begin with the string “#sql”. The program declares a connection-context class,
DreamHomeDB, for an object representing the database where the SQL statements will
execute. In this example, the SQL query can return multiple rows, and so a form of
cursor is used to allow iteration through the individual records. The method shown
here binds the attributes of the PropertyForRent table to an iterator type called
propertyDetails. With this approach, we can access each attribute as a method of the iter-
ator object, propertylterator.
L-8 | Appendix L Example Web Scripts
Figure L.5 Multirow query using SQLJ.
L.6 JavaServer Pages
In Section 30.7.5 we examined JavaServer Pages (JSP), a Java-based server-side
scripting language that allows static HTML to be mixed with dynamically gener-
ated HTML. The following example illustrates the use of JSP.
EXAMPLE L.6 Use of JSP
Use JSP to list all records in the Staff table.
This example demonstrates the use of JSP based on Example L.2. The code for the JSP
is shown in Figure L.6 and illustrates the some of the JSP constructs that can be embed-
ded in a page. For example, the PAGE directive defines the underlying language as
Java and specifies which classes should be imported for the JSP. Note that in this case,
we could replace the database specific code with a JavaBean component, StaffQuery say,
and use the Bean with the following code:
<jsp:useBean id= “test” class = “[Link]” />
L.7 Active Server Pages and ActiveX Data Objects | L-9
Figure L.6 Sample JavaServer Page ( JSP).
L.7 Active Server Pages and ActiveX Data Objects
In Section 30.8.2 we examined Microsoft’s Active Server Pages (ASP) and ActiveX
Data Objects (ADO), which allow server-side scripting to interface to a DBMS. The
following example illustrates the use of ASP and ADO.
L-10 | Appendix L Example Web Scripts
EXAMPLE L.7 Use of Active Server Pages and ActiveX Data Objects
Create an ASP application that accesses the DreamHome database and returns details about staff
and the properties that they manage.
To answer this query in the DreamHome Microsoft Office Access database, we could use
the following SQL statement:
SELECT [Link], [Link], [Link], [Link], [Link], [Link]
FROM Staff s INNER JOIN PropertyForRent p ON [Link] ⫽ [Link];
The corresponding ASP to return this information to a Web browser is shown in Figure L.7
and the output from the execution of the ASP in Figure L.8.
(continued)
Figure L.7 Sample ASP application using ADO.
L.7 Active Server Pages and ActiveX Data Objects | L-11
Figure L.7 (Continued )
L-12 | Appendix L Example Web Scripts
Figure L.8 Output from the Active Server Page shown in Figure L.7.
L.8 [Link]
In Section 30.8.5 we examined [Link], a component of the .NET Framework
class library. [Link] provides a disconnected data access model that is required
in the Web environment and at the same time provides extensive support for
XML. The following example illustrates the use of [Link].
EXAMPLE L.8 Use of [Link]
Create a Windows application that displays staff at branch B003 along with the associated properties
and viewings. Also, save the data to XML.
The [Link] code for this application is shown in Figure L.9. The Windows output is
shown in Figure L.10 and part of the XML file output in Figure L.11. Note that in
Figure L.10 that each row in this DataGrid has an expanded “—” icon. The reason is that
the DataGrid object has detected the relation between the Staff table and the
PropertyForRent table. Clicking on the icon reveals all of the relations for which the Staff
table is the parent (in this case, the Viewing table appears).
In this example, we have chosen to connect to a Microsoft Office Access database
using OLEDB. The connection is achieved using OleDbConnection() and the DataAdapter
L.8 [Link] | L-13
used is OleDbDataAdapter. To connect to an SQL Server database, the connection string
would be changed to:
Dim strConnection As String = “Data Source=localhost;lnitial Catalog=DreamHome;” _
& “Integrated Security=True”
and we would use SQLConnection() and SQLDataAdapter in place of the OLEDB ones.
The output is achieved by placing a DataGrid called myGrid on the form. Generating the
XML file is achieved using the DataSet method WriteXML(). We could read this XML file
back into a DataSet using the following statement:
Dim ds As New DataSet()
[Link](“c:\[Link]”)
The application also demonstrates how to access individual tables and rows in the DataSet
at the end.
Imports [Link]
Imports [Link]
Public Class Form1
Inherits [Link]
Private Sub Form1_Load(ByVal sender As Object, ByVal e As [Link]) Handles [Link]
' Open a database connection.
Dim strConnection As String = _
"Provider=[Link].4.0;Data Source=" _
& "C:\Data\database\[Link]"
Dim cn As OleDbConnection = New OleDbConnection(strConnection)
[Link]()
' Set up a data adapter object.
Dim strSql As String = "SELECT staffNo, fName, lName, branchNo FROM Staff" _
& " WHERE branchNo = 'B003'"
Dim da As OleDbDataAdapter = New OleDbDataAdapter(strSql, cn)
' Load a data set.
Dim ds As DataSet = New DataSet("MyDataSetName")
[Link](ds, "StaffB003")
' Set up a new data adapter object.
strSql = "SELECT propertyNo, street, city, [Link]" _
& " FROM Staff s, PropertyForRent p" _
& " WHERE ([Link] = [Link])" _
& " AND ([Link] = 'B003')"
da = New OleDbDataAdapter(strSql, cn)
' Load the data set.
[Link](ds, "PropertyForRentB003")
' Set up a new data adapter object.
strSql = "SELECT Viewing.*" _
& " FROM Viewing, Staff, PropertyForRent" _
& " WHERE ([Link] = [Link])" _
& " AND ([Link] = [Link])" _
& " AND ([Link] = 'B003')"
da = New OleDbDataAdapter(strSql, cn) (continued)
Figure L.9 Sample application using [Link].
L-14 | Appendix L Example Web Scripts
' Load the data set.
[Link](ds, "ViewingB003")
' Close the database connection.
[Link]()
' Create a relation.
[Link]("PropertyForRent", _
[Link]("StaffB003").Columns("staffNo"), _
[Link]("PropertyForRentB003").Columns("staffNo"))
[Link]("Viewing", _
[Link]("PropertyForRentB003").Columns("propertyNo"), _
[Link]("ViewingB003").Columns("propertyNo"))
' Bind the data set to a grid.
[Link](ds, "StaffB003")
' Save as XML.
[Link]("c:\[Link]")
' Access the data set as tables and rows.
Dim dt As DataTable = [Link]("PropertyForRentB003")
Dim rowCustomer As DataRow
For Each rowCustomer In [Link]
[Link]([Link]("propertyNo"))
[Link]([Link]("street"))
[Link]([Link]("city"))
Next
End Sub
End Class
Figure L.9 (Continued )
Figure L.10 Output from [Link] shown in Figure L.9.
L.9 Oracle’s PL/SQL Server Pages | L-15
Figure L.11 XML output from [Link] shown in Figure L.9.
L.9 Oracle’s PL/SQL Server Pages
In Section 30.9 we discussed Oracle’s Internet Platform and its support for
PL/SQL Server Pages (PSPs), which are analogous to ASPs and JSPs. We illustrate
the use of PSPs in the following example.
EXAMPLE L.9 Use of PL/SQL Server Pages
Produce a PL/SQL Server Page to display the names of staff with a salary above a threshold specified
by the user.
Assume we have created a Web page with the POST method shown in Figure L.12(a).
We can then create the PSP shown in Figure L.12(b) to display the records that satisfy
the user-specified minimum salary.
The file for a PL/SQL Server Page must have the extension “.psp” and can contain
text and tags interspersed with PSP directives, declarations, and scriptlets. To identify
a file as a PL/SQL Server Page, we include a <%@ page language=“PL/SQL” %> direc-
tive at the beginning of the file. By default, the PL/SQL gateway transmits files as
L-16 | Appendix L Example Web Scripts
Figure L.12(a) POST METHOD to invoke PL/SQL Server Page (PSP).
Figure L.12(b) Sample PL/SQL Server Page.
L.9 Oracle’s PL/SQL Server Pages | L-17
HTML documents, so that the browser formats them according to the HTML tags. To
interpret the document as XML, plain text, or some other document type, a <%@
page contentType⫽“...” %> directive can be used, specifying text/html, text/xml, text/plain,
or some other MIME type. We have included a directive to indicate that it is HTML.
Each PSP corresponds to a stored procedure within the server. By default, the pro-
cedure is given the same name as the original file, with the “.psp” extension removed.
The procedure can be given a different name using the directive <%@ page
procedure⫽“...” %>. To set up parameter passing for a PSP, we have included a <%@
plsql parameter⫽“...” %> directive. By default, parameters are of type VARCHAR2 (a dif-
ferent type can used by specifying a type⫽“...” attribute within the directive). To set a
default value, so that the parameter becomes optional, we have included a
default⫽“15000” attribute in the directive.
Compare this script with the ASP and JSP created in earlier examples.