Teaching CGI and JDBC Database Web Applications with Templates
Ming Wang, Ph. D.
Department of Computing and Mathematics
Embry-Riddle Aeronautical University
600 S. Clyde Morris Blvd.
Daytona Beach, FL 32114
E-mail: wangm@db.erau.edu
Phone: (904) 226-6681
 

ABSTRACT

The paper describes the author's experience teaching database web application programming with templates. The templates are each individual complete database programming examples. Presenting them one by one in class, letting students compile and run by themselves, and then modifying them to their homework assignments. Based on the templates they were able to accomplish their homework and finally database application projects. Database web application programming was the last topic of our database course. It was a fun part of the term project.
 

1. Introduction

Due to the vast popularity development of the Internet applications, web database application programming has become one of the topics in the undergraduate database course. How to enhance it into the already crowded traditional database course is a raised question. After trial and errors, the author found an effective way to teach the emerged technology. The solution is to use the designed programming templates and . Given the appropriate connectivity resources and programming templates, students can learn both non-web and web programming and create their own projects using CGI or JDC in two weeks. The prerequisite is that students should have the knowledge of the database design, SQL language, and at least one programming language background

During the four semesters of academic year of 1998-1999 the author taught web application programming with CGI (Common Gateway Interface) programming in Pre-compiler C in Oracle7. With the release of Oracle 8i, the author started teaching JDBC (Java Database Connectivity) in the fall of 1999. Both CGI and JDBC were implemented within the Oracle DBMs environment. The focus of the paper is to describe how to teach CGI and JDBC with programming templates.

2. Teaching CGI (Common Gateway Interface) with Proc

CGI is a programming method that lets the Form on Web pages communicate with programs on an HTTP server [1]. The interface is precisely called: a gateway. It receives the information from the server and creates a child process that handles or stores this information for the CGI program. CGI enables you to add applications to your web pages, retrieve real time data, and communicate with other individuals. With CGI, you can provide a method by which visitors to your site can access your databases, store information, and execute external programs. In CGI programming, a request is made for a URL from a location served by the HTTP server. CGI is a method that lets Web pages communicate with programs on an HTTP server. Prior to the advent of Java, this was the only way to get web pages to interact with [2].

When using HTML for the interface, the author can define various fields that enable the user to enter data. This data is sent to a CGI program when the user clicks a submit button. Once the data sent, the CGI program generates a new HTML page describing the result and returns it to the user’s browser to be displayed. You can use any language to write CGI scripts. Perl (Practical Extraction and Report Language) is best overall to use for creating CGI program with C and Visual BASIC. HTML form provides the only method by which a visitor can interact with your CGI scripts.

CGI Program was written with Pro-c and compiled with proc-compiler in Oracle7 on Salaries system. The generated c code was compiled with GCC compiler to generate executable cig code. The executable cgi runs on the web server when URL in the Action in the form is called.
 

3. Teaching JDBC with Java
 

JDBC (Java database Connectivity) contains Java classes and interfaces that provide low-level access to databases. The most common task of an applet using the JDBC driver to connect to and query a database. The most prominent and mature approach for accessing relational DBMSs from Java appears to be JDBC [1].

With JDBC Java can be used as the host language for writing database applications. JDBC is a standard Java classes library, to query and modify relational data. The JDBC driver acts like a translator. It receives the client applications request in Java methods, translates it into a format that the database can understand, then presents the request to the database using the database native protocol. The response is received by the JDBC driver, translated back into Java data format, and presented to the client application. JDBC defines a set of interfaces and classes to be used for communicating with a database. This set of interfaces and classes are all contained in the java.sql package. The entire java.sql package is included in the Java core just as the java.awt or java.lang packages are.

All databases speak SQL. The goal of JDBC API is to give the Java programmer the ability to write applets and applications to access any SQL database servers. The JDBC API consists of a set of classes and interfaces written in the Java programming language that provide a standard API for tool/database developers.

JDBC driver API defines two things. On one side, the JDBC API specifies how information is to be presented to your application. It tells your application what can be expected from the database. Conversely the JDBC API also define what the database can expect from your application. Essentially, it defines the common ground between the database and application. It defines what commands can be executed, how to execute them, and how data will be formatted.

Oracle8 provides native support for Java in the DBMS. That is, Oracle has developed its own Java VM that integrates with the database mechanisms closely high performance. In addition, the database system natively supports JDBC and SQLJ. The oracle 8i comes with a web server; so the database system can be an http listener.

The JDBC API makes it easy to send SQL statements to a relational database systems and supports all dialects of SQL. The value of the JDBC API is that an application can access any source and run on any platform with API (Application Programming Interface) is added to Java 1.1. Using the JDBC API you can access a wide variety of different SQL database using exactly the same syntax in an application and applets.

Seven basic steps to querying databases are: Import the java.sql package, Load and register the Driver, Establish connection, Create a statement, Execute a statement: statement executes SQL statements, Retrieve the results, Close the statement and connection

4. Programming Templates

Programming templates are typical database programming examples prepared by the instructor. Teaching with templates are listed as following steps:
 

Demonstrate non-web application programming examples and explain how they work in class.

Explain the syntax of database connectivity and embedded SQL.

Let students run the templates before they start their homework assignments.

Encourage students to do the homework by following the given examples in the classroom as templates.
 

Repeat Step 1 to Step 4 to teach web application programming by providing web application programs.

Assign students to a project to write a complete database application program.
 

  The typical templates are listed as follows:  
None web application
Web applications
Select one row
Select one row
Select multiple rows
Select multiple rows
Delete
Delete
Insert
Insert
Update
Update
Create subprograms
Create subprograms
Call subprograms
Call subprograms
   

The following JDBC file shows how to list all the names from the EMP table.

import java.sql.*;
class Employee {
public static void main (String args [ ] ) throws SQLException {

// Load the Oracle JDBC driver
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

// Connect to the database

Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@minna:1521:minna",
"shuz", "shuz");

// Create a Statement
Statement stmt = conn.createStatement ();

// Select the ENAME column from the EMP table
ResultSet rset = stmt.executeQuery ("select ENAME from EMP");

// Iterate through the result and print the employee names
while (rset.next ())
System.out.println (rset.getString (1));

}

} The following CGI program is a template written with Proc for the select statement for a non-web application. The purpose is to display part numbers from Table Part.

#include <stdio.h >
#include <stdlib.h >
#include <string.h >
EXEC SQL INCLUDE sqlca;
void sqlerror(void);

int main(void)
{

EXEC SQL BEGIN DECLARE SECTION;
varchar username[40];
varchar password[40];
char supnum_local[60];

EXEC SQL END DECLARE SECTION;
EXEC SQL WHENEVER SQLERROR DO sqlerror();
EXEC SQL WHENEVER NOT FOUND GOTO end;

/* Connect to the SQL database */
strcpy((char*)username.arr,"kelly");
username.len=strlen((char*)username.arr);
strcpy((char*)password.arr,"kelly");
password.len=strlen((char*)password.arr);

EXEC SQL CONNECT :username IDENTIFIED BY :password;
EXEC SQL DECLARE supnum_query CURSOR FOR

SELECT supnum FROM supplier;
EXEC SQL OPEN supnum_query;

for ( ;;)
{

EXEC SQL FETCH supnum_query INTO :supnum_local;
printf("<supnum = %s\n", supnum_local);
}
end:     EXEC SQL CLOSE supnum_query;
    EXEC SQL COMMIT WORK RELEASE;
    return(0);
}

void sqlerror(void)
{ EXEC SQL WHENEVER SQLERROR CONTINUE;
printf("\n Oracle Error Detcted \n");
printf("\n %70s", sqlca.sqlerrm.sqlerrmc);
exit(1);
}

  5. Summary
 Web user's view
CGI
JDBC
Process on the server
X
 
Process on the client
 
X
Need the Java enabled web browser
 
X
   

Table 1 Web user' point of view

 

 
 Programmer's comments
CGI
JDBC
Language independent
X
 
Web server independent
X
X
Error message at run time
 
X
Easiness to create interface
X
 
  Table 2 Programmer's point of view

Two disadvantages of Proc CGI programs are:

HTML form has to be defined and wrapped in the proc code, which makes code more complicated.
The size of the executable is very large. One executable cgi program with one query has the file size as large as 1 megabytes because the calls to Oracle library were embedded in the file.
 

Although CGI program has a lot of disadvantages, it is still a popular and general way to create web applications. Once students learned how to create using one language, they will be able to pick up any other language to write a CGI program. Some of my students learned using pro-c writing a CGI program in the course. They picked up Perl or and Ada to write a CGI program by themselves.

JDBC

It is more complicated to define GUI interface with JDBC unless using predefined Java beans or Java Swing.

Java program is machine independent. It can be compiled with any Java compiler as long as it follows JDK standard. Driver can be changed. The compiled output Java class saved on the disk can be called by an applet which is embedded into a page of HTML. When a web browser reads the page with the embedded applet, it downloads the applet over the network to the local system, and runs the applet in a Java VM which is built into the browser. Therefore the output of the Java program is displayed on the Internet.

Java replaces some uses of CGI by allowing truly dynamic web pages doing the process on the client instead of server. Java has built-in capabilities to prevent memory corruption, program crashes, and viruses. Thus it is robust and secure. Java contains multiple threads to carry out many tasks in parallel. On the whole, JDBC application programming has the bright future. The Java Server Page (JSP) is the newly developed technology, which will help computer professionals to develop more standard web applications more efficiently in the real world.

 
References:

[1] Connolly, T & Begg, C.& Strachan A. Database systems: A practical Approach to design, Implementation, and management, Addison Wesley, 1999

[2] Catlell R. & Hamilton G., JDBC database access with Java, Addison Wesley 1997.
 

[2] Hall Marty, Core Web Programming, Prentice Hall, 1997