Bartolomeo Bogliolo

Antonio Cavaliere

Michela Crosetto

Giuseppe Innamorato

The advent of the World Wide Web and of Java has brought new and unpredictable possibilities for applications design and development.

These new possibilities can be adequately exploited to access to relational data bases as network resources.

This abstract presents some possible Java architectures and application examples, with a particular interest upon interfacing Oracle RDBMS..

The present abstract is structured as follows: there is a general introduction that deals with the Internet and Java programming; the remaining section presents the several possible architectures of Java applications accessing Oracle RDBMS. Each solution is analysed in terms of features, advantages and disadvantages.

The rapid and widespread diffusion of the World Wide Web has totally changed the way to communicate. In a few years, in fact, a new Virtual World is grown and become boundless, and it can be visited almost by everyone.

The great diffusion and power reached by Internet permits to gain potentially an unlimited number of users, that is to say potential clients for many companies (services and products providers) that are becoming more and more interested in the internet business. The commercial approach to internet for these companies implies the creation of a web server having embedded data base enabling capabilities but, at the same time, it must be also very well designed from a graphical point of view. It is strategic to use fast and simple tools for accessing data bases whenever the user needs to use the internet (or the intranet) technology to manage orders, to manage clients or to address any other customer care activities, to realise helpdesk functions, etc.

This change has not been fully recognised and accepted by everyone, but in the meanwhile a second revolution has already come, thanks to the Java technology.

In this context the future applications, but also the traditional client-server applications, should be redesigned in order to exploit the huge capabilities offered by the new technology, which allows the user to access transparently either to data and to applications residing in the intranet or in the internet.

The present abstract presents some experiences made using Java for building applications accessing Oracle data bases. In particular, some possible architectures and interfaces between Oracle and the applications are discussed.

It originated by some research projects made at CERN (Switzerland) in 1989 about hypertexts, but soon it became a tool used for communication purposes. The WWW is very simple to use: the user executes a program on his workstation (the browser) by which he can navigate, by simply clicking the mouse buttons, among the hypertext pages offered by the WWW servers.

The simplicity of the user interface and the great availability of the browser upon every platform resulted in a always more and more widespread usage of the WWW and of the network on which this service is offered: Internet.

The technical features of the WWW are very simple. The browser makes a request of a specified page by giving its address in a Universal Resource Locator (URL) format. The browser exchanges information with the server using the HyperText Transfer Protocol (HTTP) based on the TCP-IP stack. On the server side there are the pages, written using a simple TAG-based language, the HyperText Markup Language (HTML), that may contain text, images and URL connections to other pages, while the browser presents only the contents of the page to the user.

The standard mode for running programs on a Web Server is to use the Common Gateway Interface (CGI). In this way, some pages are not static documents but programs, that are executed and produce an HTML page as outputs. These programs can perform every typical computing activity like accessing data bases, make transactions, start searches, and so on.

The advantage of using CGI is that it is a standard and easy method for programming webs.

The disadvantages of using CGI are the following:

• all the processing activity is managed by the server that receives the request, processes it, and sends an answer back (in HTML format). In this way the processing capabilities of the client are not fully exploited and the work load is heavily on the WEB server side.
• the processing is started at the end of the user input. No intermediate control, when the user is still entering data, is possible, because the CGI has a batch approach.
• The exchange of data between client and server can become very complex, because each time all data must be transferred, bringing also impacts on the transfer time if the connection line used is slow.
• The usage of CGI is strictly connected to the presentation mode typical of the WEB. While the typical data entry applications are made of several pages connected one to the other with a precise logic, CGI activation is related to one single page by time; in this case the application should be redesigned in order to apply this constraint.

In order to improve the programming on internet and to solve CGI limitations, we need a universal language which could be used by every client accessing the WEB. Using such a language it is possible to program the client for making local processing and , at the same time, to maintain a live connection to the WEB and a link to its data.

Java is an object-oriented programming language, "simple, distributed, interpreted, portable, robust, architecture neutral, secure, portable, high performance and multithreaded", as the Sun Microsystems’ official definition says.

Let’s see why...

• simple

• distributed

• interpreted, portable, architecture neutral

• robust

• high performance

• multithreaded

In summary Java is a good, modern, highly portable, object oriented language. This makes Java the right choice for developing Internet applications.

Using Java you can write standard applications or applets which are loaded from the WWW and executed by the browser.

The Java language can be used in order to make applications that access Oracle RDBMS using the native access mode. Using this mode the Java runtime is enriched with a set of external functions written using C language. In order to access Oracle you need to develop functions like connect, statement execute, and so on, and to call these from within the Java programs.

For instance, in order to access Oracle using Pro*C, you have to pre-compile the code and obtain a file written in C language:

The result is a C language source that permits to access to Oracle.

To interface C language with Java the functions written are compiled into dynamically loadable object and encapsulated inside a Java method call. At runtime Java invokes these functions as well as it would call any Java method.

The steps to follow in order to interface a C program with Java are:

• Write the Java Code
• Compile the Java code using javac
• Create the .h file using javah, the header file contains a structure that encapsulates the Java class on the C side and a C function definition for the implementation of the native method defined in the java class.
• javah useNative.java
• Create a stubs file using javah, a stubs file contains C code that maps the Java class to its parallel C structure
• javah - stubs useNative.java
• Create a dynamically loadable object module from the .h file, the stubs file, and the .c file that you created.

• Run the program.

// Import section

class OracleInsert {
public static void main (String args[]) {
 try {
accessDB orahndl = new accessDB("Oracle","scott","tiger");
 orahndl.execSQL("insert into emp (empno,deptno,ename) values 
      (2533,32,'HO')");
  } catch (IOException e) { // Do something please  }
 }
}

import java.io.IOException;
/* ... */

class accessDB {

private File logFile; // log/trace file
/* ... */

public accessDB (String typeOfDB,String username,String password){
int success = nativeAccessDB(typeOfDB,username,password);
if (success != 1){
System.out.println("Error in connecting"); }
}

public void execSQL(String statements) {
int success = nativeExecSQL(statements);
if (success != 1) {
System.out.println("Error in SQL execution");
}
}
/* ... */

public native int nativeAccessDB(String typeOfDB2, 
String username,String password);
public native int nativeExecSQL(String statements);
public native int nativeSelectSQL(String stmnts);
public native String nativeFetchSQL();
}

static {
System.loadLibrary("createLibrary");
}

#include <stdio.h>
#include <string.h>
#include <sqlca.h>
#include <oraca.h>
#include "accessDB.h"
#include <StubPreamble.h>
#include <javaString.h>
EXEC SQL INCLUDE sqlca;
EXEC SQL INCLUDE oraca;
EXEC ORACLE OPTION (ORACA=YES);

EXEC SQL BEGIN DECLARE SECTION;
char *C_DBname;
char *C_DBuser;
char *C_DBpass;
char *C_DBvalue;
EXEC SQL END DECLARE SECTION;

/* Handle SQL runtime errors. */
void sql_error();
int success=1;

EXEC SQL DECLARE oracle1 database;
/* ... */

long accessDB_nativeAccessDB(struct HaccessDB *this, 
struct Hjava_lang_String *DBname,struct Hjava_lang_String *DBuser,
struct Hjava_lang_String *DBpass,struct Hjava_lang_String *DBvalue)
{

C_DBname = makeCString(DBname);
C_DBuser = makeCString(DBuser);
C_DBpass = makeCString(DBpass);
C_DBvalue = makeCString(DBvalue);

EXEC SQL WHENEVER SQLERROR DO sql_error("Oracle error");

oraca.orastxtf = ORASTFERR;

if (!strcmp(C_DBvalue,"oracle1")){
EXEC SQL CONNECT :C_DBuser IDENTIFIED BY :C_DBpass AT oracle1;
}
return success;
}
/* ... */

Using the previous exposed technique you can build a first level of Java application that access Oracle RDBMS.

The advantages of using this solution are related to the usage of a portable and object-oriented language.

It must be noticed, anyhow, that the usage of native methods doesn’t permit applications portability: every porting on a different platform implies a recompilation of the C code for accessing Oracle.

If you need to develop applications which are completely portable you need to split the part of the program that access the data from the application itself, building a program working in a distributed architecture.

The complete portability of the applications and the advantages of exploiting client processing can be achieved using a different architecture. The application presented in the following example is realised as an applet and executed by the browser. The applet communicates with an active server on the WEB, which manages the data access. In the application design phase it is necessary to define the protocol between the two applications and the services offered by the server. In the following example it has been used a simple protocol that allows to request the execution of any SQL statement.

The components of this architecture are two: the applet, which sends the SQL statement, and the server, which manages RDBMS connection.

The applet is a simple application executed by the browser that requests services exploiting the network access classes, which are very simple and sophisticated in Java. The server is an application that collects all the requests from the network, using the multithreading capabilities of Java for serving several clients at the same time. The native methods technique used by the server for accessing the Oracle RDBMS is the same described above.

A brief note on Oracle usage: the server application works against several connections. To give a real parallelism to the DB access the 7.3 Oracle multithreaded functionality must be embraced. That means that the thin PRO*C interface code must be carefully written.

<HTML>

<HEAD>
<TITLE>Oracle access applet</TITLE>
<META NAME="GENERATOR" CONTENT="vi">
<META NAME="AUTHOR" CONTENT="aca">
<META NAME="VERSION" CONTENT="1.0">
</HEAD>

<BODY>
<H1>Oracle access</H1>
<HR>
<BR><BR>
<APPLET CODE=access.class WIDTH=500 HEIGHT=400>
<PARAM NAME=stmtSql VALUE="update emp set deptno = 10">
<PARAM NAME=selectSql VALUE="select * from emp">
<PARAM NAME=username VALUE="scott">
<PARAM NAME=pwd VALUE="tiger">
<PARAM NAME=DBase VALUE="Oracle">
</APPLET>
<HR>
</BODY>
 
</HTML>

// Import section
public class access extends Applet {
// Declaration section

public void init() {
// Get applet parameters
stmtSql=getParameter("stmtSql"); 
if (stmtSql == null)
stmtSql = "insert into emp (empno,deptno,ename) values
    (2533,32,'pluto')";
selectSql=getParameter("selectSql"); 
if (selectSql == null)
selectSql = "select ename from emp where deptno=32";
userName=getParameter("username");
if (userName == null)
userName = "scott";
pwd=getParameter("pwd"); 
if (pwd == null)
pwd = "tiger";
typeOfDB=getParameter("DBase"); 
if (typeOfDB == null)
typeOfDB = "Oracle";
 
try {
clientSql dataBase = new clientSql("sunset.acme.it",4455);
    dataBase.connectSql(typeOfDB+"/"username+"/"+pwd);
String s = "insert into emp (empno,deptno,ename) " +
"values (2533,32,'Antony')");
dataBase.execSQL(s);
} catch (IOException e) { // Do something }

// Build graphical interface
    }
}

// Import section

class server {
public static void main (String args[]) {
ServerSocket s=(ServerSocket) null;
try {
s= new ServerSocket(4455,9);
} catch (IOException e) {;}

while (true) {
try {
Socket s1=s.accept();
multiserver serverSock = new multiserver(s1);
serverSock.start();
} catch (IOException e ) {;}
}
}
}

class multiserver extends Thread {
// Declaration section
    accessDB orahnld; 
    
public multiserver (Socket so) {
myso = so;
}

public void run() {
try {
s1out=myso.getOutputStream();
s1inp=myso.getInputStream();
DataOutputStream s2out = new DataOutputStream(s1out);
DataInputStream inut = new DataInputStream(s1inp);

while(true) {
String msg= inut.readLine();
String send = msg.substring(0,1);
msg = msg.substring(1);

if ( "c".equalsIgnoreCase(send)){
StringTokenizer st = new StringTokenizer(msg,"/");
int numberOfElement = st.countTokens() ;
type = st.nextToken();
username = st.nextToken();
passwd = st.nextToken();
if (numberOfElement > 3) 
interete = st.nextToken(); 
if (interete == null)
orahnld = new accessDB(type,username,passwd);
else
orahnld = new accessDB(type,username,passwd,interete);

s2out.writeByte(1);
}
// Other requests
}
} catch (IOException e ) { }
}
}

The above example can be extended in many ways. It is possible, for example, to make the server part of the application more complex, to shift much of the processing load from the client to the server and to reduce communication between them.

Of course, the applet can be very complex: it can offer a sophisticated user interface and perform complex data validation. The data validation can be performed closer to the end user in order to provide a faster response time.

It must be noticed that the applet does not require any installation activity, because it is downloaded from the Web server. This is likely to be the definitive solution to the software distribution problem!

But also in this case we can notice a problem: the communication between client and server is not standard but proprietary. For software reusability purposes it is necessary to use a standard protocol.

As already mentioned, the architecture described in the above example doesn’t guarantee a programming standard able to extend the communication from the client to all the possible RDBMS. The Java Database Connectivity (JDBC) package defines the communication standards, providing classes and interfaces which normalise the access methods, queries and database updates.

The following example is composed by three parts: client, proxy agent and server. The client uses a set of classes that implements JDBC interfaces and translates the methods in standard requests to the server. The proxy agent (written in java) is located on the web server and links the clients to the server program. This is necessary when the server program is resident on a system in the intranet, which is not the web server itself, and at the same time the client is a java applet. This is true because an applet can activate a tcp/ip connection only with the web server from which the applet itself comes. The server has the task of communicating with the database and can make connections directly or via SQL*Net.

<HTML>

<HEAD>
<TITLE>JDBC test applet</TITLE>
<META NAME="GENERATOR" CONTENT="vi">
<META NAME="AUTHOR" CONTENT="mcr">
<META NAME="VERSION" CONTENT="1.0">
</HEAD>

<BODY>
<H1>SQL-Query Wizard</H1>
<HR>
<BR><BR>
<APPLET CODE=test.class WIDTH=500 HEIGHT=400>
<PARAM NAME=driver VALUE="jdbc:acme:oracle://sunset.acme.it:7070/sunset">
</APPLET>
<HR>
</BODY>

</HTML>

// Import section

public class test extends Applet {

// Public declarations

/**
* Build applet user interface and register the Oracle driver.
* @see archeOracleDriver
*/
public void init() {

url = getParameter("driver");
if (url == null) {
System.err.println("Applet parameter <driver> 
      missing");
return;
}

try {
// Get Oracle driver
     
driver = (archeOracleDriver) new archeOracleDriver();
     
// Register Oracle driver to the drivers manager
DriverManager.registerDriver(driver);
// Get username and password to log-in
info = loadProperties();
// Connect to Oracle database
connection = (archeConnection) DriverManager.getConnection(url, 
      info);
// Set automatic commit after each statement to false
connection.setAutoCommit(false);
// Enables only queries
connection.setReadOnly(true);

// Create a generic statement
     
Statement jstmt = connection.createStatement();
     
// Execute the statement and get the result set
ResultSet jset = jstmt.executeQuery("select 
      TABLE_NAME from TABS");
do {
// Get the field by position as s string
String s = jset.getString(1);
// Do something
}
while(jset.next()); // Get the next record from 
      result set
// Close the statement
     
jset.close();
     

catch (SQLException e) { // Do something }

// Build graphic interface
}
}

/* Connection to Oracle database.
* Parameters:
* connstr: connection string for SQL*Net connection
* Format: t:<host>:<sid>
* user : username
* pwd : password
*
* Return codes:
* 0 : OK
* 1 : Generic error
* -1 : Errore during la connessione
*/
int MCRconnect(connstr, user, pwd)
char *connstr;
char *user;
char *pwd;
{
int len;

if (!user || !pwd) {
strcpy(oraerr, "User or password not defined");
return 1;
}
/* … */
return 0;

MCRconnect_error:
/* … */
return -1;
}

/* Execution of a statement
* Parameters:
* sqlstmt: SQL statement
*
* Return codes:
* # : Record processed
* -1 : Error in statement execution
* -2 : Invalid parameter
*/
int MCRexecute(char *sqlstmt)
{
int len;
/* … */
return totRows;

MCRexecute_error:
/* … */
return -1;
}

In the example it is not shown the proxy agent code, because it is not relevant to the description of the communication protocol between client and server.

The following figure shows the Architecture of the example above:

The Oracle commitment to Internet and to Java is very strong.

Oracle already support Java cartridges in Oracle Web Sever 2.1. Moreover, Oracle is working on an Oracle JDBC Server, on J/SQL (the Java precompiler), on the Developer 2000 Web Cartridge, ...

But the most interesting incoming feature is the complete implementation of the Java Virtual Machine.

Using this new tools the effort to develop powerful Java application accessing the Oracle RDBMS will be greatly simplified.

Java allows to create powerful Internet multi-tier applications. Though the recent introduction, the continuos evolution and the lack of mature application development tools, it is already possible to develop robust network applications.

In this paper we presented some multi-tier Internet Java application accessing Oracle in an efficient, standard and flexible way.

In conclusion, we can say that our experience with java applications accessing Oracle is not very dated (a little longer than one year), but the changes in this period were enormous. All these changes are in the direction of empowering the internet basic technology and tools. Java is the most significant example of this trend, and so it rewards us a lot for the continuous effort in challenging this rapid technology evolution.