\documentstyle[eepic,a4]{article}
\title{{\bf mijdbc}: a native-protocol pure Java JDBC driver for Illustra}
\author{Erwin Kalvelagen, TNO-FEL}
\date{}


\newenvironment{changemargin}[2]{%
 \begin{list}{}{%
  \setlength{\topsep}{0pt}%
  \setlength{\leftmargin}{#1}%
  \setlength{\rightmargin}{#2}%
  \setlength{\listparindent}{\parindent}%
  \setlength{\itemindent}{\parindent}% 
  \setlength{\parsep}{\parskip}%
 }%
\item[]}{\end{list}}



\begin{document}

\maketitle

\begin{abstract}
This document describes the JDBC driver for the Illustra database we have
developed for use with the GEOMED GIS system.
\end{abstract}

\newpage

\tableofcontents
\newpage

% empty line between paragraphs.
\setlength{\parskip}{\baselineskip}

\section{Introduction}

JDBC is a standard Java API for relational database connectivity using
SQL queries. We have developed a JDBC driver for Illustra with the following
features:

\begin{itemize}
\item[]{$\bullet$} implements a reasonable subset of JDBC functionality,
\item[]{$\bullet$} uses a socket connection directly with the database server,
\item[]{$\bullet$} aims for high-performance,
\item[]{$\bullet$} is written in pure Java and can be used in an applet,
\item[]{$\bullet$} allows result sets to be transmitted in binary,
\item[]{$\bullet$} can deal with Illustra's user-defined types. 
\end{itemize}

{\bf mijdbc} connects directly with the Illustra database server (with processes {\tt midaemon} and
{\tt miserver} respectively) through a buffered socket connection or through a bridge daemon
at the server. Besides a miraculous improvement in stability, we also incorporated ZIP-like 
compression to minimize network traffic, which is advantageous in cases where relative
low-speed connections are being used.

\section{Architectural overview}

\subsection{JDBC driver categories}

Basically, our JDBC driver provides the following services:

\begin{itemize}
\item[] Make a connection with the Illustra database,
\item[] Send SQL statements (queries, updates) to this database,
\item[] Receive results back in a form that is convenient in a Java enviroment.
\end{itemize}

For the Java programmer the JDBC driver per se is quite invisible: one cannot use the driver
classes and methods directly. After loading the driver, just the normal JDBC API is available.
JDBC will invoke the driver for you.

The {\bf mijdbc} driver is completely written in Java and does not call any client side
drivers or other middleware. JDBC drivers are often divided into four categories. 
See figure \ref{DRIVERTYPES}.

\begin{figure}[hbtp]
\begin{changemargin}{+0cm}{-1cm}
\begin{tabular}{|l|c|l|}
\cline{1-3}
Driver Type & Pure Java & Net Protocol \\
\cline{1-3}
1. JDBC-ODBC Bridge  & No & ODBC \\
2. Native Client API & No & Native Protocol \\
                     &    & through proprietary driver \\
3. JDBC-Net          & Yes & JDBC Middleware Protocol \\
4. Native protocol, pure Java  & Yes & Native Protocol  \\
\cline{1-3}
\end{tabular}
\end{changemargin}
\caption{JDBC driver types}
\label{DRIVERTYPES}
\end{figure}

We have implemented a type 4 driver. Types 1 and 2 require additional
drivers on the client and as this violates security rules for applets
such drivers can only be used in applications (standalone Java programs) and 
trusted applets (applets that are digitally signed and can by authenticated
by the browser; trusted applets can do many things untrusted applets
can't). A type 3 driver uses a standard protocol which must be handled
directly by the database or must be translated by a server side process. 
Type 4 drivers usually talk directly to the DBMS in a native protocol.
Types 3 and 4 are pure Java drivers and thus can run in 
an applet with the usual restrictions. One of these security related
restrictions is that an applet can only open network connections
to the host that served the applets. I.e. the database server and the 
web server should be identical machines.

Although type 1 and 2 drivers are considered to be interim solution,
especially JDBC-ODBC bridges seem very popular. Most databases have 
ODBC drivers and the JDBC-ODBC bridge is freely available. This solution
does not require any programming. A type 4 driver is in many respects
the most desirable driver, but a substantial effort is needed to build
such a driver, and intimate knowledge of the DBMS at hand. 

\subsection{Development of the driver}


To jump start our development we took an existing type 4 driver that
was simple enough, and resembled our DBMS: the JavaPostgres95 driver
version 0.4. After all, Postgres95 and Illustra have the same ancestor:
the Postgres system developed at the U.C. Berkeley by the group of 
Michael Stonebraker. Source of the JavaPostgres95 driver was readily available
on the net.

Most Illustra users will be familiar with the client tool {\tt msql}, which
connects to the database and allows to interactively send SQL statements.
Usually one invokes {\tt msql} as follows:

\begin{verbatim}
     msql mydatabase
\end{verbatim}

or

\begin{verbatim}
     msql mydatabase -U username -P password
\end{verbatim}


The client has several ways to find out what the server is: e.g. through the environment
variable {\tt MI\_SERVER} or the default section in the configuration file
{\tt MiParams}. {\tt msql} then sets up a connection to the server, where a
process called {\tt midaemon} is continously listening to TCP port 7531. As soon
as a client connects to this port, {\tt midaemon} spawns a process {\tt miserver}
that further takes care of this particular client. As a result, the server
always has one {\tt midaemon} process running, and as many {\tt miserver}
processes as there are clients active (there may be zero of them). For other
clients than {\tt msql} Illustra provides a library LIBMI that clients written
in C can link. This library hides the details of the underlying protocol.

When implementing the Java driver we had to find out what the underlying
protocol was. We used here the availability of some source of pieces of MILIB and {\tt msql}
and after turning on some extensive tracing, and even using {\tt snoop} (a utility
to capture and inspect network packets) we could re-enigineer the basics of the
protocol. I am sure that some of the finer details and exotic parts have missed
our attention, but we understood enough of what is happening between client
and server to try a Java implementation.


\begin{figure}[hbtp]
\setlength{\unitlength}{1cm}
\begin{center}
\begin{picture}(11,5)(0,0)
\put(1.5,4.5){\hbox{Client}}
\put(2,2){\oval(3,3)}
\put(8,4.5){\hbox{Server}}
\put(9,2){\oval(4,3)}
\put(1.8,2.3){$msql$}
\put(1.8,2){$or$}
\put(1.8,1.7){$applet$}
\put(7.6,2){$midaemon/miserver$}
\put(3,2.2){\vector(1,0){4.5}}
\put(7.5,2.0){\vector(-1,0){4.5}}
\put(5.5,2.3){\hbox{Port 7531}}
\end{picture}
\end{center}
\caption{Direct connection}
\end{figure}


The main problem we encountered with our Java driver in this setup was 
that for no obvious reason data may stop flowing. This happens often 
in the middle of large result sets. This behavior was earlier observed 
with {\tt msql} itself. This was reported to Illustra, but their support
personnel was not able to reproduce our problem. The problem did not 
seem to exist when {\tt msql} or Java client was executed on the same machine 
as the database. In order to work around this problem we developed a
bridge daemon called {\tt bridged} that is located on the server
and listens to port 7532. As soon as a client makes contact to
this port a server is forked off that further deals with this connection.
The daemon can then continue to listen for incoming connections.
This spawned server sets up a connection to port 7531 on the local host
and using the {\tt select} system call waits for incoming traffic
either from the remote client or from Illustra. The incoming data 
is then forwarded to Illustra or the remote client. The {\tt select}
call will block until there is traffic, or until a socket connection
is closed, or (optionally) until a time limit expires. {\tt Select} is in general
preferable to polling for efficiency reasons.


\begin{figure}[hbt]
\setlength{\unitlength}{1cm}
\begin{center}
\begin{picture}(11,8)(0,-3)
\put(1.5,4.5){\hbox{Client}}
\put(2,2){\oval(3,3)}
\put(8,4.5){\hbox{Server}}
\put(9,0.5){\oval(4,6)}
\put(1.8,2){$applet$}
\put(7.6,2){$bridged$}
\put(3,2.2){\vector(1,0){4.5}}
\put(7.5,2.0){\vector(-1,0){4.5}}
\put(5.5,2.3){\hbox{Port 7532}}

\spline(7.5,2.2)(5,0.45)(7.5,-1.3)
\spline(7.5,2.0)(5,0.25)(7.5,-1.5)

\put(7.5,-1.3){\vector(1,-1){0}}
\put(7.5,2.0){\vector(1,1){0}}

\put(4,1.75){\hbox{compression}}

\put(7.6,-1.5){$midaemon/miserver$}
\put(5.5,-1.3){\hbox{Port 7531}}
\end{picture}
\end{center}
\caption{Connection through {\tt bridged}}
\end{figure}

Once we had this bridge process in place it seemed natural
to give it some real functionality instead of just being
a band aid for an Illustra network problem. As in our
GIS application relatively simple selects are used but
with rather large result sets often containing large
user defined types such as polygons, we incorporated
a ZIP like compression on the results (the left arrow
from {\tt bridged} to the client in figure 3). In the
new JDK 1.1 Java provides support for compressing
and uncompressing using the GZIP standard in the
package {\tt java.util.zip}. For {\tt bridged} we used
the library {\tt zlib 1.0.4}. 


\section{Installation}

\subsection{Installation of {\bf mijdbc}}


To install the {\bf mijdbc} driver, perform the following steps.

\begin{list}{$\bullet$}{\setlength{\itemsep}{0cm}}
\item unpack the archive, e.g. by {\tt tar xvf mijdbc}.
\item move the mijdbc directory into your classpath or add it's current location
                 to the classpath
\item try the example program {\tt test.java}, by editing the connection URL,
                 compiling and running it.
\end{list}                   


\subsection{Installation of {\tt bridged}}

After unpacking the {\bf mijdbc} archive a directory {\tt bridge} is created.
This directory contains compiled binary {\tt bridged} for the Solaris SPARC environment.
For other environments you will need to recompile the source. When doing this,
first make sure to create a {\tt libz.a} library in the {\tt zlib-1.0.4}
directory. Consult the {\tt README} file in this directory. Then compile
{\tt bridged.c} after adapting the {\tt Makefile}.

{\tt bridged} can be installed in two ways: as a daemon or through {\tt inetd}.
When installed as a daemon, a small program will run continuously. To install
{\tt bridged} as a daemon just type {\tt bridged} as in:

\begin{verbatim}
   => bridged
   bridged Version 0.1
   Erwin Kalvelagen, TNO-FEL, <kalvelagen@fel.tno.nl>
   =>
\end{verbatim}

{\tt bridged} will return you almost immediately to the system prompt, as the
real workhorse is spawned off to do its work in the background. You can detect
whether {\tt bridged} is running by typing:


\begin{verbatim}
    => ps -e | grep bridged
        4778 ?        0:00 bridged
    => 
\end{verbatim}

When running as a daemon, at least one {\tt bridged} will be running. If there
are $n$ clients running, you will see $n+1$ {\tt bridged} processes. E.g.:

\begin{verbatim}
     => ps -e | grep bridged
         4778 ?        0:00 bridged
         6058 ?        0:00 bridged
         6060 ?        0:00 bridged
     => 
\end{verbatim}

indicates that two clients are active.

When you want to make sure {\tt bridged} is started whenever the system boots,
let your system administrator add an entry in the appropriate {\tt rc} file.

{\tt bridged} can also run through the super daemon {\tt inetd}. In this case
{\tt bridged} is only started when needed, i.e. when a client makes a connection. 
This requires that a new service {\tt bridged} is registered in {\tt /etc/services},
which is accomplished by adding a line:

\begin{verbatim}
   bridged 7532/tcp # MIJDBC/Illustra bridge
\end{verbatim}

Then an entry in {\tt /etc/inetd.conf} must be added:

\begin{verbatim}
bridged stream tcp nowait someuser 
             /usr/local/bin/bridged bridged inetd
\end{verbatim}


Check with your system administrator for details. Important is that the program
should be invoked as ``{\tt bridged inetd}'' as you can see at the end of the
line.



\section{A complete example}

The standard way to get a connection to the database is through 
a procedure as depicted in the code fragment in figure 4.

\begin{figure}[hbt]
\begin{verbatim}
import java.sql.*;
 
class test {
  public static void main(String argv[]) {
    try {
      String url = "jdbc:mijdbc://134.203.33.12:7531/erwin"; 
      Class.forName( "mijdbc.MIDriver" );      
      Connection conn = 
        DriverManager.getConnection(url,"emfh7","");      
      Statement stat = conn.createStatement();      
      ResultSet rs = stat.executeQuery("return Release();");      
      if ( rs.next() ) {
        String s = rs.getString(1);
        System.out.println("release ="+s);
      }      
      conn.close();      
    } catch ( Exception e ) {
      System.err.println("Exception: "+e);
      e.printStackTrace();
    }
  }
}
 \end{verbatim}
\caption{A Simple Query}
\end{figure}

\subsection{URL encoding}

The address of the server consists of several parts and is encoded in a URL as
follows:

\begin{verbatim}
           protocol:subprotocol://host:port/database
\end{verbatim}

The individual pieces are:

\begin{itemize}
\item[]{$\bullet$} protocol. This is always {\tt jdbc}. 
\item[]{$\bullet$} subprotocol. This is our {\tt mijdbc} protocol.
\item[]{$\bullet$} host. For example {\tt myserver.mycompany.com}.
                   It can also be a dotted decimal number, i.e. {\tt 101.13.128.3}. If you
	           don't specify a host, {\tt localhost} is inserted and you will be connected
                   to the client-machine you are working on.
\item[]{$\bullet$} port. This is the TCP/IP port number the database is listening to.
                   For normal Illustra installations this is 7531. If you want to talk
	           via the bridge use port number 7532.
\item[]{$\bullet$} database. The name of the database to connect to.
\end{itemize}

\subsection{Loading the driver}

The driver has to be loaded before it can be used. There are several
ways of doing this. The first is to instantiate an MIDriver object,
as in:

\begin{verbatim}
      Driver mid = (Driver) new mijdbc.MIDriver();
\end{verbatim}

This one is handy when working on the driver code itself. The compiler
knows the dependency on the {\tt mijdbc} package, and can recompile
files if needed.

A second way is to load the class through:

\begin{verbatim}
      Class.forName( "mijdbc.MIDriver" );      
\end{verbatim}

This is what we used in this example. A third way is to use the system properties. 
The ``sql.drivers'' property -- if exists -- should contain a semi-colon separated 
list of driver class names.

\subsection{Establishing a connection}

The {\tt java.sql.DriverManager.getConnection} method comes in several flavors.
In the simplest form just an URL, a username and a password is provided. This is
the form we have used in the example:

\begin{verbatim}
      Connection conn = 
        DriverManager.getConnection(url,"emfh7","");      
\end{verbatim}

Another way is to use properties. The following fragment shows 
how:

\begin{verbatim}
      java.util.Properties info = new java.util.Properties();
      info.put("user","emfh7");      
      info.put("password","");
      Connection conn = 
        DriverManager.getConnection(url,info);      
\end{verbatim}

A complete list of the recognized properties is listed in figure 5.

\begin{figure}[hbt]
\begin{center}
\begin{tabular}{|l|l|}
\cline{1-2}
Property key & description \\
\cline{1-2}
MIHOST & Default host name, URL overrides \\
MIPORT & Default port number, URL overrides \\
MIOPTIONS & Not used \\
MITTY & TTY for Illustra \\
user & Illustra user name\\
password & Illustra password \\
\cline{1-2}
\end{tabular}
\end{center}
\caption{Properties}
\end{figure}

\subsection{Creating a Statement object}

SQL statements without parameters are normally
executed using Statement objects. If the same SQL statement 
is executed many times, it is more efficient to use a 
PreparedStatement. To create a Statement object simply invoke:

\begin{verbatim}
      Statement stat = conn.createStatement();      
\end{verbatim}
 
\subsection{Submitting a query}

The standard way of submitting a query (e.g. a SELECT statement) is
through {\tt java.sql.Statement.executeQuery}. Note that SQL UPDATE, 
INSERT and DELETE statements are more appropriately submitted via
the {\tt java.sql.Statement.executeUpdate} method.

\begin{verbatim}
      ResultSet rs = stat.executeQuery("return Release();");      
\end{verbatim}

Note from the example that Illustra's RETURN statement can just as wel be used
through {\tt executeQuery}. The semi-colon that terminates the statement
is required by Illustra: an error will occur if this is not present.

\subsection{Extracting results from the ResultSet}

Resultsets are accessed row by row. Within a row you can freely ask
for any column, accessing rows is strictly sequential. Initially
the result set is positioned just before the first row. After a 
call to {\tt next()} the row positioning is advanced by one. {\tt next()}
returns {\tt false} as soon as no more rows are available. I.e.
to count rows one could do:

\begin{verbatim}
      int count = 0;
      while (rs.next())
         ++count;
\end{verbatim}

To extract data within a row the JDBC API provides a plephora of methods:
getByte, getShort, getInt, getLong, getFloat, getDouble, getBigBecimal, getBoolean,
getString, getBytes, getDate, getTime, getTimestamp, getAsciiStream, getUnicodeStream, 
getBinaryStream, getObject. All these methods have two forms: they can access 
columns by number (starting at one) or by name. We will return to these
java.sql.ResultSet.getXXX methods in another section.

In the example, we just receive a scalar back, a $1 \times 1$ table which is 
a string. So the following fragment will correctly extract this value:

\begin{verbatim}
      if ( rs.next() ) {
        String s = rs.getString(1);
        System.out.println("release ="+s);
      }      
\end{verbatim}


\subsection{Closing the connection}

The connection can be closed by invoking {\tt java.sql.Connection.close()}.

\section{execute and executeUpdate}

Some statements don't return a ResultSet, e.g. updates, inserts, deletes, 
and others like {\tt create table} and {\tt drop table}.
In some cases an update count is
returned: the number of records updated, inserted or deleted.
For such cases we have the {\tt java.sql.Statement.execute} and {\tt java.sql.executeUpdate} methods.

The {\tt execute} method is useful in cases where the SQL statement can be either
a statement that returns a ResultSet (e.g. select), a statement that returns
an update count (e.g. an update) or a Data Definition Language (DDL) statement (e.g.
create table).  The code snippet in figure \ref{EXECUTE} shows its use:

\begin{figure}[hbtp]
\begin{changemargin}{-1cm}{0cm}
\begin{verbatim}
  if (statmt.execute(SQLString)) {
    // true means we have a result set
    rs = statmt.getResultSet();
    // extract here you data...
  } 
  else {
    // no result set
    int updatecount = statmt.getUpdateCount();
    switch (updatecount) {
      case -1: 
         System.err.println("Oops, a result set should be there");
         break;
      case 0:  
         System.err.println("Either a DDL or 0 records were affected");
         break;
      default: 
         System.err.println("Rows affected:"+updatecount);
    }
  }
\end{verbatim}
\end{changemargin}
\caption{statement.execute}
\label{EXECUTE}
\end{figure}

	 
When calling {\tt executeUpdate()} the UpdateCount is returned.


\section{Types}

In this section we will discuss how Illustra standard types are mapped
to SQL types and to Java types. User defined database types are discussed in
a separate section.

\subsection{Illustra types}

Illustra provides slightly different types than SQL. 
Somehow we needed to map the Illustra types into the SQL types as defined
in {\tt java.sql.Types}. For instance the method  {\tt java.sql.ResultSetMetaData.getColumnType()}
needs to return one of these SQL types.

As an example, Illustra has the type {\tt boolean}, while the SQL
definition talks about {\tt BIT}. 

We have created a mapping as is presented in figure 6.


\begin{figure}[htb]
\begin{center}
\begin{tabular}{|l|l|l|}
\cline{1-3}
Illustra Type & SQL Type & Remarks \\
\cline{1-3}
boolean   & BIT        &  \\
bytea     & VARBINARY  &  \\ 
character & CHAR       &  \\
char      & CHAR       & \\
char1     & TINYINT    & \\
date      & DATE       & \\
decimal   & DECIMAL    & \\
double precision & DOUBLE & \\
int1      & TINYINT    & \\
integer   & INTEGER    & \\
large\_text & LONGVARCHAR  & not implemented correctly\\
numeric    & NUMERIC   & \\
real       & REAL & \\
reltime    &  & not implemented \\
smallint   & SMALLINT & \\
text       & VARCHAR  & \\
time       & TIME     & time zone not implemented \\
time\_tz    & TIME &  \\
timestamp & TIMESTAMP & time zone not implemented \\
timestamp\_tz & TIMESTAMP & \\
varchar    & VARCHAR  & \\
\cline{1-3}
\end{tabular}
\end{center}
\caption{Mapping Illustra to SQL types}
\end{figure}

It should be noted that when we call the method {\tt java.\-sql.\-Result\-Set\-Meta\-Data.\-get\-Column\-Name()} the
name received from Illustra is returned and not the mapped SQL type name 
(e.g. {\tt boolean} instead of {\tt BIT}).

\subsection{Java types}


Eventually we have to map the Illustra types into Java types.
For this we used the mapping as is shown in figure \ref{MAP1}.

\begin{figure}[htb]
\begin{center}
\begin{tabular}{|l|l|l|}
\cline{1-3}
Illustra Type & Java Type & Remarks \\
\cline{1-3}
boolean   & java.lang.Boolean       &      \\
bytea     & byte[] &  \\ 
character & String       &  \\
char      & String       & \\
char1     & java.lang.Character    & \\
date      & java.sql.Date         & \\
decimal   & java.math.BigDecimal    & \\
double precision & java.lang.Double & \\
int1      & java.lang.Byte    & \\
integer   & java.lang.Integer    & \\
large\_text & String  & not implemented correctly \\
numeric    & java.math.BigDecimal   & \\
real       & java.lang.Float & \\
reltime    &  & not implemented \\
smallint   & java.lang.Short & \\
text       & String  & \\
time       & java.sql.Time  & time zones not implemented \\
time\_tz    & java.sql.Time &  \\
timestamp & java.sql.Timestamp  & time zones not implemented \\
timestamp\_tz & java.sql.Timestamp & \\
varchar    & String  & \\
\cline{1-3}
\end{tabular}
\end{center}
\caption{Mapping Illustra types to Java objects}
\label{MAP1}
\end{figure}


Notice that not the standard types as {\tt int}, {\tt double} etc. are
used but the object versions of them, as can be found in in {\tt java.lang}. 
The reason for this is a technical one. In our implementation the values have to be objects
instead of primitive types 
as they are casted into a generic type {\tt Object}. While it is perfectly
legal to cast a {\tt java.lang.Boolean} into an {\tt Object}, this is not
possible with a {\tt boolean}. 

JDBC prescribes which {\tt Result\-Set.\-getXXX} methods can be used on which SQL data
types. This table is reproduced in figure \ref{MAP2}.


\begin{figure}[htb]
\begin{changemargin}{-2cm}{0cm}
\begin{tabular}{|l|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|}
\cline{1-20}
\cline{1-20}
                &T&S&I&B&R&F&D&D&N&B&C&V&L&B&V&L&D&T&T\\
                &Y&M&N&I&E&L&O&E&U&I&H&A&O&I&A&O&A&I&I\\
                &I&A&T&G&A&O&U&C&M&T&A&R&N&N&R&N&T&M&M\\
                &N&L&E&I&L&A&B&I&E& &R&C&G&A&B&G&E&E&E\\
                &Y&L&G&N& &T&L&M&R& & &H&V&R&I&V& & &S\\
                &I&I&E&T& & &E&A&I& & &A&A&Y&N&A& & &T\\
                &N&N&R& & & & &L&C& & &R&R& &A&R& & &A\\
                &T&T& & & & & & & & & & &C& &R&B& & &M\\
                & & & & & & & & & & & & &H& &Y&I& & &P\\
                & & & & & & & & & & & & &A& & &N& & & \\
                & & & & & & & & & & & & &R& & &A& & & \\
                & & & & & & & & & & & & & & & &R& & & \\
                & & & & & & & & & & & & & & & &Y& & & \\
\cline{1-20}
getByte        &{\bf X}& x & x & x & x & x & x & x & x & x & x & x & x &  &  &  &  &  & \\
getShort       & x &{\bf X}& x & x & x & x & x & x & x & x & x & x & x &  &  &  &  &  & \\
getInt         & x & x &{\bf X}& x & x & x & x & x & x & x & x & x & x &  &  &  &  &  & \\
getLong        & x & x & x &{\bf X}& x & x & x & x & x & x & x & x & x &  &  &  &  &  & \\
getFloat       & x & x & x & x &{\bf X}& x & x & x & x & x & x & x & x &  &  &  &  &  & \\
getDouble      & x & x & x & x & x &{\bf X}&{\bf X}& x & x & x & x & x & x &  &  &  &  &  & \\
getBigDecimal  & x & x & x & x & x & x & x &{\bf X}&{\bf X}& x & x & x & x &  &  &  &  &  & \\
getBoolean     & x & x & x & x & x & x & x & x & x &{\bf X}& x & x & x &  &  &  &  &  & \\
getString      & x & x & x & x & x & x & x & x & x & x &{\bf X}& x & x & x & x & x & x & x & x\\
getBytes       & &  &  &  &  &  &  &  &  &  &  &  &  &{\bf X}&{\bf X}& x &  &  & \\
getDate        & &  &  &  &  &  &  &  &  &  & x & x & x &  &  &  &{\bf X}&  & x \\
getTime        & &  &  &  &  &  &  &  &  &  & x & x & x &  &  &  &  &{\bf X}& x \\
getTimestamp   & &  &  &  &  &  &  &  &  &  & x & x & x &  &  &  & x &  &{\bf X}\\
getAsciiStream & &  &  &  &  &  &  &  &  &  & x & x &{\bf X}& x & x & x &  &  & \\
getUnicodeStream & &  &  &  &  &  &  &  &  &  & x & x &{\bf X}& x & x & x &  &  & \\
getBinaryStream & &  &  &  &  &  &  &  &  &  &  &  &  & x & x &{\bf X} &  &  & \\
getObject       & x & x & x & x & x & x & x & x & x & x & x & x & x & x & x & x & x & x & x \\
\cline{1-20}
\end{tabular}
\end{changemargin}
\caption{JDBC getXXX usage, {\bf X} means recommended}
\label{MAP2}
\end{figure}

In our implementation we use the Java type conversion mechanism to find
out if we can map a certain type to a Java type. This is more restrictive.
For instance, an Illustra double precision can only be accessed by {\tt getString} and
{\tt getDouble}. All other getXXX methods will generate an exception. 

The table in figure \ref{GETXXX} shows all allowed getXXX calls.


\begin{figure}[htbp]
\begin{changemargin}{-2cm}{0cm}
\begin{tabular}{|l|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|}
\cline{1-20}
\cline{1-20}
                 &b&c&c&d&d&n&d&i&i&t&l&r&s&v&t&t&t&t&b\\
                 &o&h&h&a&e&u&o&n&n&e&a&e&m&a&i&i&i&i&y\\
                 &o&a&a&t&c&m&u&t&t&x&r&a&a&r&m&m&m&m&t\\
                 &l&r&r&e&i&e&b&1&e&t&g&l&l&c&e&e&e&e&e\\
                 &e&a&1& &m&r&l& &g& &e& &l&h& &\_&s&s&a\\
                 &a&c& & &a&i&e& &e& &\_& &i&a& &t&t&t& \\
                 &n&t& & &l&c& & &r& &t& &n&r& &z&a&a& \\
                 & &e& & & & & & & & &e& &t& & & &m&m& \\
                 & &r& & & & &p& & & &x& & & & & &p&p& \\
                 & & & & & & &r& & & &t& & & & & & &\_& \\
                 & & & & & & &e& & & & & & & & & & &t& \\
                 & & & & & & &c& & & & & & & & & & &z& \\
                 & & & & & & &i& & & & & & & & & & & & \\
                 & & & & & & &s& & & & & & & & & & & & \\
                 & & & & & & &i& & & & & & & & & & & & \\
                 & & & & & & &o& & & & & & & & & & & & \\
                 & & & & & & &n& & & & & & & & & & & & \\
\cline{1-20}
getByte          & & & & & & & &x& & & & & & & & & & & \\
getShort         & & & & & & & & & & & & &x& & & & & & \\
getInt           & & & & & & & & &x& & & & & & & & & & \\
getLong          & & & & & & & & & & & & & & & & & & & \\
getFloat         & & & & & & & & & & & &x& & & & & & & \\
getDouble        & & & & & & &x& & & & & & & & & & & & \\
getBigDecimal    & & & & &x&x& & & & & & & & & & & & & \\
getBoolean       &x& & & & & & & & & & & & & & & & & & \\
getString        &x&x&x&x&x&x&x&x&x&x&x&x&x&x&x&x&x&x&x\\
getBytes         &x&x&x&x&x&x&x&x&x&x&x&x&x&x&x&x&x&x&x\\
getDate          & & & &x& & & & & & & & & & & & & & & \\
getTime          & & & & & & & & & & & & & & &x&x& & & \\
getTimestamp     & & & & & & & & & & & & & & & & &x&x& \\
getAsciiStream   &x&x&x&x&x&x&x&x&x&x&x&x&x&x&x&x&x&x&x\\
getUnicodeStream &x&x&x&x&x&x&x&x&x&x&x&x&x&x&x&x&x&x&x\\
getBinaryStream  &x&x&x&x&x&x&x&x&x&x&x&x&x&x&x&x&x&x&x\\
getObject        &x&x&x&x&x&x&x&x&x&x&x&x&x&x&x&x&x&x&x\\
\cline{1-20}
\end{tabular}
\end{changemargin}
\caption{mijdbc getXXX usage}
\label{GETXXX}
\end{figure}



\subsection{Implementation issues}

\subsubsection{Byte ordering}

Different machine architectures use different byte orderings. This is important
when moving binary data fom one machine to another. For instance the Intel
80x86 architecture uses Least Significant Byte first or little endian byte ordering.
Other machines like the SPARC architecture use a big endian representation.

Java uses a well defined network byte ordering, which is big endian. Our JDBC driver
instructs the Illustra server to send data in this order, and this a user does not
have to convert data.

\subsubsection{large text}

The Illustra type {\tt large\_text} does not return the text itself, but
its handle, e.g. ``I010982029384''. Right now we don't have any provisions
to retreive the text itself.

\subsubsection{getBytes}

Note that {\tt getBytes} returns data in a format that is used by Illustra.
E.g. when returning a string like value (text, character, varchar) the first
four bytes indicate the total length (including the four bytes for itself)
and then the characters follow. Thus for a text containing ``hello world''
we receive the following byte array:  
00  00  00  0f  68  65  6c  6c  6f  20  77  6f  72  6c  64.  The Illustra User's
Guide, appendix A and B give more information about how Illustra is laying out
memory for different data types.

Byte ordering is in the network byte order that Java is using, so you
don't have to convert integers etc. to correct for a particular byte ordering.

\subsubsection{Time zones}

Time and timestamp variables are returned without respect of the local time zone. This is the
way we get it from Illustra. I.e. the following is possible. If we give the
following SQL statements,

\begin{verbatim}
	create table mijdbctest (t time);
	insert into mijdbctest values ('16:15:00');
        select * from mijdbctest;
\end{verbatim}

we may get back as time: ``14:15:00'', as in our case
we had when we did this experiment a 2 hour displacement in
our time zone from GMT. Converting correcty the time
depends on the date (think of daylight savings). Which
date to be used, date of the query (current date) or
date of entering the data (not readily available) is not
clear. As in our applications we don't
return times, we did not investigate this further.

When using the {\tt time\_tz} or {\tt timestamp\_tz} type, the correct time is
returned when queried, as the time zone displacement
is carried along with the time itself.

\subsubsection{Streams}

In our implementation the retrieval methods {\tt get\-Ascii\-Stream()}, {\tt get\-Uni\-code\-Stream()}, and {\tt get\-Binary\-Stream()}
are identical. 

Hint: for more flexible reading you can use {\tt java.\-io.\-Data\-Input\-Stream\-(get\-Ascii\-Stream())}. As all
data in the network byte order that Java is using, you can read integers, floating point
numbers etc. directly from the stream without converting them.


\subsubsection{NULL values}

If a value is NULL all non-object numeric getXXX methods (getByte, getShort, getLong, getFloat, getDouble)
will return 0. All getXXX methods that return objects (getString, getBigDecimal, getBytes, time, date and stream related methods)
will return {\bf null} and getBoolean will return {\tt false}. This is true even if otherwise an 
cast exception would result. In the implementation, the test for being NULL is performed before the cast.

\subsection{User defined Illustra types}

One of the most interesting parts of Illustra is of course its
capability to deal with user defined types.

The minimum one needs to implement when creating a new Illustra type
is an ascii input and output function. When working with {\bf mijdbc}
one needs to add at least a binary output function to this.

For more information see the Illustra User's Guide. The ASCII input functions
are often of the form

\begin{verbatim}
    mytype *mytype_in(mi_text *str, MI_FPARAM *fparam);
\end{verbatim}

while the ASCII output function often has the form:

\begin{verbatim}
    mi_text *mytype_out(mytype *x, MI_FPARAM *fparam);
\end{verbatim}

In the {\tt create type} statement these routines are 
registered as the {\tt input} and {\tt output} routine.

As we use in our JDBC driver binary results for the select
statements, one needs to provide routines for this. 
The binary output routine is often called the {\tt send} routine,
with a signature like:

\begin{verbatim}
    mi_verlena *mytype_send(mytype *x, MI_FPARAM *fparam);
\end{verbatim}

Using helper functions like {\tt mi\_\-put\_\-smallint},
{\tt mi\_\-put\_\-integer}, {\tt mi\_\-put\_\-double\_\-precision} that
shield you from details like byte ordering, writing
such a function is not much more complicated than writing an
ASCII output function. 
In the {\tt create type} statement this routine is registered
as the {\tt send} routine.

You can check the functioning of the {\tt send} routine by
doing a binary copy of a table, using the SQL statement:

\begin{verbatim}
    copy binary mytable to /tmp/myfile;
\end{verbatim}

Binary input routines are called {\tt receive} functions,
and they often have a signature like:

\begin{verbatim}
    mytype *mytype_receive(mi_varlena *a, MI_FPARAM *fparam);
\end{verbatim}

Once one has a binary output function, we can call JDBC to do selects
and retrieve the results. For instance, suppose we have a type
BOX consisting of four double precision numbers $(x1,y1,x2,y2)$.
Then the Java fragment in figure \ref{JAVAOBJECT} would create a Java object
that stores this information:

\begin{figure}[hbtp]
\begin{changemargin}{-1cm}{0cm}
\begin{verbatim}
if (!resultsetmetadata.getColumnTypeName(thiscolumn).equals("BOX")) 
   throw new SQLException("BOX type expected");

DataInputStream is = 
     new DataInputStream(resultset.getBinaryStream(thiscolumn));

double x1 = is.readDouble();
double y1 = is.readDouble();
double x2 = is.readDouble();
double y2 = is.readDouble();

BOX b = new BOX(x1,y1,x2,y2);
\end{verbatim}
\end{changemargin}
\caption{Reading an object}
\label{JAVAOBJECT}
\end{figure}

\section{Not implemented}

A number of JDBC features is not implemented in this version. Here follows
a list.

\subsection{Semicolon in query is required}

We pass on the query string as is to Illustra. This has as consequence that
the terminating semicolon (``;'') is required. Failure to do so will raise
an exception and an error message from Illustra.

\subsection{Prepared statements}

As Postgres does not provide facilities for prepared statements in the server,
prepared statements were completed at the
client in the JavaPostgres95 driver, and then transmitted as self contained SQL 
queries. Their is no efficiency gain in just creating the complete SQL
statement by yourself. For Illustra this situation is the same.
As we also have some conceptual problem here with mapping of 
types (TINYINT corresponds to more than one Illustra type), we
have not touched the original JavaPostgres95 code in this
respect. Although some simple queries work (see below) we did
not test this facility extensively.

\begin{figure}[hbt]
\begin{changemargin}{-0cm}{0cm}
\begin{verbatim}
Statement stat = conn.createStatement();
stat.executeUpdate("create table mijdbctest (i integer, t text);");

PreparedStatement pstat = 
   conn.prepareStatement("insert into mijdbctest values(?,?);");

int[] iarray = {1,2,3};
String[] sarray = {"one","two","three"};
for (int i=0; i< 3; ++i) {
  pstat.setInt(1,iarray[i]);
  pstat.setString(2,sarray[i]);
  int rows = pstat.executeUpdate();
  System.out.println("rows inserted:"+rows);
}
\end{verbatim}
\end{changemargin}
\caption{Example of a prepared statement}
\end{figure}

\subsection{Cursors}

Not supported by this driver, nor are positioned
updates and deletes. Of course you can create
query strings that do fetches etc. Keeping track
of the logic involved is however the task of the
programmer.

\subsection{Whole table vs. row by row}

Right now after a select we fetch the whole
table. For very large tables it may be wise
to fetch a new row as soon as {\tt java.sql.ResultSet.next()} is called.

\subsection{SQL Escapes}

The SQL Escapes syntax for stored procedures, scalar functions etc.
is not supported. However you can call Illustra's ``return'' statements
(they return a $1 \times 1$ table), and user defined functions.

\section{Benchmarks}

In this section we use some simple Java applications to benchmark
our implementation against Illustra's solution called {\bf Wedji}.

First of all it should be noted that Wedji is more complex to manage due to
its architecture. It contains a class library that should be used
at the client side, and a server part (a CGI program). The server 
configuration specifies the user, password and database that is used,
while in JDBC this is configurable at the client side. When using
more databases, Wedji requires more copies of the CGI program to be
present on the server, each in its own directory.

As Wedji is based on HTTP, no connection is maintained. This means
poor performance when many SQL statements are issued, as a new 
connection has to be established for each statement. An advantage is 
that the system can be used through firewalls and routers. Our
JDBC driver uses a socket connection that is maintained for the duration
of a session. This implies fast response in case many (cheap) SQL statements
are issued. In case a firewall or a minimally configured router is
used between client and server, this method may not work without
reconfiguring them (letting through traffic on ports 7531 and/or 7532).

In the first experiment we issue the SQL statements as depected in
figure xxx.


\begin{figure}[htbp]
\begin{changemargin}{-0cm}{0cm}
\begin{tabular}{l}
  {\it \{connect (only for JDBC)\}} \\  

  {\it \{create\}} \\
  {\tt create table bench (t text, i integer, b BOX2, p POLYGON2);}\\
  \\
  {\it \{insert 500 records\}}\\
  for $i := 0$ to $499$ do \\
  {\tt\hspace{1cm}insert into bench values('string',i,'(1,2,3,4)',}\\
  {\tt\hspace{2cm}'(10:1,1,2,2,3,3,4,4,5,5,6,6,7,7,8,8,9,9,10,10)');}\\
   \\
  {\it \{select1: all in one\}}\\
  {\tt select * from bench;}\\
   \\
  {\it \{select2: one by one\}}\\
  for $i := 0$ to $499$ do \\
  {\tt\hspace{1cm}select * from bench where i = $i$;}\\
  \\
  {\it \{drop\}}\\
  {\tt drop table bench;}\\ 
\end{tabular}
\end{changemargin}
\end{figure}

These are all quite cheap SQL statements, and the amount of traffic
per statement is limited. One would expect that compression is
not achieving much, while Wedji is hit by its connectionless
protocol (HTTP). Indeed the following timings have been collected
on a standard ethernet LAN (10 Mbit).

\begin{tabular}{|l|r|r|r|}
\cline{1-4}
& \bf{bridged} & \bf{Illustra} & \bf{Wedji} \\
& Port 7532    & Port 7531     & \\
\cline{1-4}
connect & 2371 & 1762 &  \\
create  &  864 &  796 & 5373 \\
insert  & 87155 & 85013 & 1118241 \\
select1 & 1737 & 2187 & 5465 \\
select2 & 54654 & 38012 & 1110110 \\
drop    & 864 & 796 & 5373 \\
total   & 148058 & 129050 & 2242904 \\
\cline{1-4}
\end{tabular}

Typically in our applications we select lots of large polygons
in one swoop.

\end{document}

