Applications Programming Considerations

Note architecture in Figure 8.1: Client-Server Software.

User & Application Layer: User interfaces (GUI in Access, VBA), report generators, tools. Clients.
DB Server Layer: (Server) Give DML interface to higher layers, interact with database.
Middleware Layer: "Application Services" -- More trustworthy than user software, easier to use than server layer.

Example of this is a three-tiered web site (Figure 8.2)

Microsoft Access Interaction:

We are seeing some of it in the Headless project.
ODBC -- I've a foil from whatis.com.

Note the object types and methods to be provided by each ODBC package, with a driver and a driver manager that allow a program to become a client of the database system.
The app contacts the driver manager to request access to a specific data source. Once a connection is made, the app interacts with thedriver by using the ODBC client library.

JDBC -- I've a foil again. We look at it for comparison.

Supported by the java.sqlpackage.
Client software suplied as an implementation of the java.sql interfaces.
Client consists of a collection of Java classes.
Driver manager is a part of the Java app -- no installation required.
Connect to database, then create and execute SQL statements.

Embedded SQL

We look at an example of an SQL statement embedded within a C fragment:

void addEmployee(char* ssn, char* lastName, char* firstName)
{
   EXEC SQL
      insert into Customer (ssn, lastName, firstName)
         values (:ssn, :lastName, :firstName);
}

Such code must be handled by a preprocessor.
Advantages, disadvantages (portability, special constructs).

JDBC Specifics

Here are some of the interfaces from jva.sql that provide SQL accessibility:

Driver: supports the creation of a data connection.
Connection: represents the connection between a Java client and an SQL database server.
DatabaseMetaData: contains info about he database server.
Statement: includes methods for executing text queries.
PreparedStatement: represents a precompiled and stored query.
CallableStatement: used to execute SQL stored procedures.
ResultSet: contains the results of the execution of a select query.
ResultSetMetaData: contains information about a ResultSet, including the atribute names and types.

As our example, we look at the "Simple Java SQL Application" of section 8.6 (This is Figure 8.10 on pages 192-3).
We make the following observations relative to this code:

Three "global" variables (object). Two are

db, which is class DBConnect, and
stmt, which is class Statement

This figure does not include the code for the method BigHitConnect(), which is invoked in line 11. That is found in Figure 8.7 on page 188 (students are not responsible for it, but anyone who wants to write applications will need to learn about it.)
In line 12, we see the makeConnection() method return a Connection object, which is named conn.
Lines 10 - 19 initialize the process, so that lines 20 - 31 cab repeatedly read a string with an SQL statement, execute the statement, and format and display the output (mostly within the ExecSqlToString method (lines 33 - 65).
In line 39 we make a call against executeQuery, the principal method in this code segment.
In line 40 we capture the metadata related to this result.
In the remainder of the method we interrogate result and meta, and we format the output.

For reference of those who might care to make use of it, I have copied a folder, RiccardiSW to the T drive.
We look at the documentation in JBuilder for java.sql, especially those parts addressed in the example of Figure 8.10. To get this,

Open JBuilder 3.5 (or whatever, but that is the version in the labs).
Open Help|Java Reference
Scroll down to java.sql and select the interface summary of interest, such as Statement, ResultSet, or ResultSetMetaData (for our inclass example, we have a look at Statement, then the executeQuery() method).

Technologies for Supporting Databases on WWW

We first demonstrate, using amazon.com, and consider the issues from the point of a remote database.

Next we have a look at Figure 9.1, a manifestation of Figure 9.2 and pick out some highlights of HTML.

HTML can be extended -- examples are CFML and ASP -- and JSP.
Currently, HTML has been extended to XML.

Crucial in the operation of a remote database is a specification for communicating between browser and server. The best-known specification is called "Common Gateway Interface" (CGI)

We look at Figures 9.3 and 9.4.

A result of hitting the submit button might be:

http://localhost/cgi-bin/
reservation.cgi?accountId=101&streId=3&action="displayForm

CGI code is trasmitted from browser to server in text form. An example is given at the bottom of page 213

104 x+& </.becomes 104+x%2B%26+%3C%2F.

At the server end, the CGI data are given to the CGI program -- which may be written in Java. In our applications, we can expect that the CGI program executes SQL statements against a database, then returns the results to the client.

Riccardi shows in Section 9.3 that the HTML code for a web page can be written in Java. In Section 9.4 he shows the way CGI programming can be done using Java (the most common language for CGI programming is Perl).

Note that the javax.servlet and javax.servlet.http are available in the JSDK and represent an alternative to CGI.

Section 9.5 introduces servlets -- small servers dedicated to specific CGI requests. A servlet awaits a request. The strategy is to treat each CGI request as an interaction with a running system. Thus, the connection between client and server persists.

This is supported by the server software -- In particular, Apache has an add-on called Apache Jserve for this purpose.