mijdbc: a native-protocol pure Java JDBC driver for Illustra

Erwin Kalvelagen, TNO-FEL

Abstract

This document describes the JDBC driver for the Illustra database we have developed for use with the GEOMED GIS system.

Contents

1 Introduction 3

2 Architectural overview 3 2.1 JDBC driver categories . . . . . . . . . . . . . . . . . . . . . 3 2.2 Development of the driver . . . . . . . . . . . . . . . . . . . . 4

3 Installation 7 3.1 Installation of mijdbc . . . . . . . . . . . . . . . . . . . . . . 7 3.2 Installation of bridged . . . . . . . . . . . . . . . . . . . . . . 7

4 A complete example 8 4.1 URL encoding . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 4.2 Loading the driver . . . . . . . . . . . . . . . . . . . . . . . . 10 4.3 Establishing a connection . . . . . . . . . . . . . . . . . . . . 10 4.4 Creating a Statement object . . . . . . . . . . . . . . . . . . . 11 4.5 Submitting a query . . . . . . . . . . . . . . . . . . . . . . . . 11 4.6 Extracting results from the ResultSet . . . . . . . . . . . . . 11 4.7 Closing the connection . . . . . . . . . . . . . . . . . . . . . . 12

5 execute and executeUpdate 12

6 Types 13 6.1 Illustra types . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 6.2 Java types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 6.3 Implementation issues . . . . . . . . . . . . . . . . . . . . . . 15

6.3.1 Byte ordering . . . . . . . . . . . . . . . . . . . . . . . 15 6.3.2 large text . . . . . . . . . . . . . . . . . . . . . . . . . 16 6.3.3 getBytes . . . . . . . . . . . . . . . . . . . . . . . . . . 16 6.3.4 Time zones . . . . . . . . . . . . . . . . . . . . . . . . 18 6.3.5 Streams . . . . . . . . . . . . . . . . . . . . . . . . . . 18 6.3.6 NULL values . . . . . . . . . . . . . . . . . . . . . . . 18

6.4 User defined Illustra types . . . . . . . . . . . . . . . . . . . . 19

7 Not implemented 20 7.1 Semicolon in query is required . . . . . . . . . . . . . . . . . . 20 7.2 Prepared statements . . . . . . . . . . . . . . . . . . . . . . . 20 7.3 Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 7.4 Whole table vs. row by row . . . . . . . . . . . . . . . . . . . 21 7.5 SQL Escapes . . . . . . . . . . . . . . . . . . . . . . . . . . . 21

8 Benchmarks 22

1 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:

• implements a reasonable subset of JDBC functionality,

• uses a socket connection directly with the database server,

• aims for high-performance,

• is written in pure Java and can be used in an applet,

• allows result sets to be transmitted in binary,

• can deal with Illustra’s user-defined types.

mijdbc connects directly with the Illustra database server (with processes midaemon and 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.

2 Architectural overview

2.1 JDBC driver categories

Basically, our JDBC driver provides the following services:

Make a connection with the Illustra database,

Send SQL statements (queries, updates) to this database,

Receive results back in a form that is convenient in a Java enviroment.

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 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 1.

Driver Type  Pure Java Net Protocol
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

Figure 1: JDBC driver types

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.

2.2 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 msql, which connects to the database and allows to interactively send SQL statements. Usually one invokes msql as follows:

     msql mydatabase

or

     msql mydatabase -U username -P password

The client has several ways to find out what the server is: e.g. through the environment variable MI SERVER or the default section in the configuration file MiParams. msql then sets up a connection to the server, where a process called midaemon is continously listening to TCP port 7531. As soon as a client connects to this port, midaemon spawns a process miserver that further takes care of this particular client. As a result, the server always has one midaemon process running, and as many miserver processes as there are clients active (there may be zero of them). For other clients than 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 msql and after turning on some extensive tracing, and even using snoop (a utility to capture and inspect network packets) we could reenigineer 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.

[figure: Client; Server; msql; or; applet; midaemon/miserver; Port 7531]

Figure 2: Direct connection

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 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 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 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 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 select call will block until there is traffic, or until a socket connection is closed, or (optionally) until a time limit expires. Select is in general preferable to polling for efficiency reasons.

Client Server

applet bridged

Port 7532

compression

midaemon/miserver Port 7531

Figure 3: Connection through bridged

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 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 java.util.zip. For bridged we used the library

zlib 1.0.4.

3 Installation

3.1 Installation of mijdbc

To install the mijdbc driver, perform the following steps.

• unpack the archive, e.g. by tar xvf mijdbc.

• move the mijdbc directory into your classpath or add it’s current location to the classpath

• try the example program test.java, by editing the connection URL, compiling and running it.

3.2 Installation of bridged

After unpacking the mijdbc archive a directory bridge is created. This directory contains compiled binary 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 libz.a library in the zlib-1.0.4 directory. Consult the README file in this directory. Then compile bridged.c after adapting the Makefile.

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

   => bridged
   bridged Version 0.1
   Erwin Kalvelagen, TNO-FEL, <kalvelagen@fel.tno.nl>
   =>

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 bridged is running by typing:

    => ps -e | grep bridged
        4778 ?        0:00 bridged
    =>

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

     => ps -e | grep bridged
         4778 ?        0:00 bridged
         6058 ?        0:00 bridged
         6060 ?        0:00 bridged
     =>

indicates that two clients are active.

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

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

   bridged 7532/tcp # MIJDBC/Illustra bridge

Then an entry in /etc/inetd.conf must be added:

bridged stream tcp nowait someuser
             /usr/local/bin/bridged bridged inetd

Check with your system administrator for details. Important is that the program should be invoked as “bridged inetd” as you can see at the end of the line.

4 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.

4.1 URL encoding

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

           protocol:subprotocol://host:port/database

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();
}
}
}

Figure 4: A Simple Query

The individual pieces are:

• protocol. This is always jdbc.

• subprotocol. This is our mijdbc protocol.

• host. For example myserver.mycompany.com. It can also be a dotted decimal number, i.e. 101.13.128.3. If you don’t specify a host, localhost is inserted and you will be connected to the clientmachine you are working on.

• 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.

• database. The name of the database to connect to.

4.2 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:

      Driver mid = (Driver) new mijdbc.MIDriver();

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

A second way is to load the class through:

      Class.forName( "mijdbc.MIDriver" );

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 semicolon separated list of driver class names.

4.3 Establishing a connection

The 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:

      Connection conn =
        DriverManager.getConnection(url,"emfh7","");

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

      java.util.Properties info = new java.util.Properties();
      info.put("user","emfh7");
      info.put("password","");
      Connection conn =
        DriverManager.getConnection(url,info);

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

Property key description
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

Figure 5: Properties

4.4 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:

      Statement stat = conn.createStatement();

4.5 Submitting a query

The standard way of submitting a query (e.g. a SELECT statement) is

through java.sql.Statement.executeQuery. Note that SQL UPDATE,

INSERT and DELETE statements are more appropriately submitted via

the java.sql.Statement.executeUpdate method.

      ResultSet rs = stat.executeQuery("return Release();");

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

4.6 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 next() the row positioning is advanced by one. next() returns false as soon as no more rows are available. I.e. to count rows one could do:

      int count = 0;
      while (rs.next())
         ++count;

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 × 1 table which is a string. So the following fragment will correctly extract this value:

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

4.7 Closing the connection

The connection can be closed by invoking java.sql.Connection.close().

5 execute and executeUpdate

Some statements don’t return a ResultSet, e.g. updates, inserts, deletes, and others like create table and drop table. In some cases an update count is returned: the number of records updated, inserted or deleted. For such cases

we have the java.sql.Statement.execute and java.sql.executeUpdate

methods.

The 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 6 shows its use:

When calling executeUpdate() the UpdateCount is returned.

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);
}
}

Figure 6: statement.execute

6 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.

6.1 Illustra types

Illustra provides slightly different types than SQL. Somehow we needed to map the Illustra types into the SQL types as defined in java.sql.Types.

For instance the method java.sql.ResultSetMetaData.getColumnType()

needs to return one of these SQL types.

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

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

It should be noted that when we call the method java.sql.ResultSet-MetaData.getColumnName() the name received from Illustra is returned and not the mapped SQL type name (e.g. boolean instead of BIT).

Illustra Type  SQL Type  Remarks
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

Figure 7: Mapping Illustra to SQL types

6.2 Java types

Eventually we have to map the Illustra types into Java types. For this we used the mapping as is shown in figure 8.

Notice that not the standard types as int, double etc. are used but the object versions of them, as can be found in in 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 Object. While it is perfectly legal to cast a java.lang.Boolean into an Object, this is not possible with a boolean.

JDBC prescribes which ResultSet.getXXX methods can be used on which SQL data types. This table is reproduced in figure 9.

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 getString and getDouble. All other getXXX methods will generate an exception.

Illustra Type  Java Type  Remarks
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

Figure 8: Mapping Illustra types to Java objects

The table in figure 10 shows all allowed getXXX calls.

6.3 Implementation issues

6.3.1 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.

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

getByte  X x x x x x x x x x x x x
getShort  x X x x x x x x x x x x x
getInt  x x X x x x x x x x x x x
getLong  x x x X x x x x x x x x x
getFloat  x x x x X x x x x x x x x
getDouble  x x x x x X X x x x x x x
getBigDecimal  x x x x x x x X X x x x x
getBoolean  x x x x x x x x x X x x 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
getDate  x x x  X  x
getTime  x x x  X x
getTimestamp  x x x  x  X
getAsciiStream  x x X x x x
getUnicodeStream  x x X x x x
getBinaryStream  x x X
getObject  x x x x x x x x x x x x x x x x x x x

Figure 9: JDBC getXXX usage, X means recommended

6.3.2 large text

The Illustra type 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.

6.3.3 getBytes

Note that 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

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

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

Figure 10: mijdbc getXXX usage 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.

6.3.4 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,

create table mijdbctest (t time);
insert into mijdbctest values ('16:15:00');
        select * from mijdbctest;

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 time tz or timestamp tz type, the correct time is returned when queried, as the time zone displacement is carried along with the time itself.

6.3.5 Streams

In our implementation the retrieval methods getAsciiStream(), getUni-

codeStream(), and getBinaryStream() are identical.

Hint: for more flexible reading you can use java.io.DataInputStream- (getAsciiStream()). 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.

6.3.6 NULL values

If a value is NULL all non-object numeric getXXX methods (getByte, get-Short, getLong, getFloat, getDouble) will return 0. All getXXX methods that return objects (getString, getBigDecimal, getBytes, time, date and stream related methods) will return null and getBoolean will return 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.

6.4 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 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

    mytype *mytype_in(mi_text *str, MI_FPARAM *fparam);

while the ASCII output function often has the form:

    mi_text *mytype_out(mytype *x, MI_FPARAM *fparam);

In the create type statement these routines are registered as the input and 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 send routine, with a signature like:

    mi_verlena *mytype_send(mytype *x, MI_FPARAM *fparam);

   Using helper functions like mi put smallint, mi put integer, 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 create type statement this routine is registered as the send routine.

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

    copy binary mytable to /tmp/myfile;

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

    mytype *mytype_receive(mi_varlena *a, MI_FPARAM *fparam);

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 11 would create a Java object that stores this information:

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);

Figure 11: Reading an object

7 Not implemented

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

7.1 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.

7.2 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.

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);
}

Figure 12: Example of a prepared statement

7.3 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.

7.4 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 java.sql.ResultSet.next() is called.

7.5 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 × 1 table), and user defined functions.

8 Benchmarks

In this section we use some simple Java applications to benchmark our implementation against Illustra’s solution called 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.

{connect (only for JDBC)} {create}

 create table bench (t text, i integer, b BOX2, p POLYGON2);

{insert 500 records} for i := 0 to 499 do

       insert into bench values('string',i,'(1,2,3,4)',
            '(10:1,1,2,2,3,3,4,4,5,5,6,6,7,7,8,8,9,9,10,10)');

{select1: all in one}

 select * from bench;

{select2: one by one} for i := 0 to 499 do

       select * from bench where i = i;

{drop}

 drop table bench;

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).

bridged Illustra Wedji
Port 7532 Port 7531
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