Shu-Feng Wei
Commercial Systems Division
Java Database Connectivity (JDBC) is a Java API that enables
development of Java applications and applets with a wide range of
relational databases. It consists of a set of classes and interfaces
written in Java. JDBC allows developers to write database applications
using a standard, pure Java API.
A typical JDBC Java application consists of a Java application
or applet, the JDBC Driver Manager, a vendor specific JDBC driver,
and a database. The JDBC Driver Manager is provided with the Java
SDK and its primary function is to load and register the vendor-specific
JDBC driver with the Java applications and then get out of the way.
The following diagram shows the various components of a client-server
JDBC application.
Figure 5-1 JDBC Client-Server Application
The HP Driver for JDBC is a vendor specific JDBC Driver that
allows Java applications to connect to ALLBASE/SQL or IMAGE/SQL
databases on MPE/iX or ALLBASE/SQL on HP-UX. The HP Driver for JDBC
is a Type 3 (Network-Protocol) driver, meaning the driver translates
the JDBC API into a DBMS-independent protocol on the client-side,
and then translates to the ALLBASE/SQL protocol on the server. The
driver components on the client-side are written in 100% Pure Java,
which provides the complete compatibility with all Java Virtual
Machines on all platforms.
HP JDBC Components
There are three components supplied with the HP JDBC product,
the JDBC Driver, the JDBC Monitor, and the JDBC Server. The user
is responsible for writing a Java application or applet that uses
JDBC on the client, as well as providing the ALLBASE/SQL or IMAGE/SQL
database on the server.
The HP Driver for JDBC is a set of Java classes that
implement the java.sql.* interfaces and provide an
implementation of a JDBC driver that can communicate with an
ALLBASE/SQL or IMAGE/SQL database. The HP Driver for JDBC typically
resides on the client side of the user application. It provides the
translation from the Java language and the JDBC API to the HP
proprietary network protocol.
The JDBC Monitor is a component that is installed on the
JDBC server host that manages all client JDBC Driver connections to
the server host. It is typically started as a daemon when the server
machine is booted. All JDBC client connections are made through the
JDBC Monitor. The monitor performs validation of the userid and
password that are passed in the client connection request and spawns
JDBC Server processes to serve each of the client connections. Once
the server process is spawned, the monitor returns to wait for the
next client connection.
The JDBC Server is the server process that is spawned by
the JDBC Monitor to service a client connection. It handles the
translation from the HP proprietary network protocol to the
ALLBASE/SQL calls. There is at least one JDBC Server process for each
client connection to the server host. More than one JDBC Server
process may be used to handle multiple client statements using the
same connection. This component also handles the translation from
JDBC SQL to ALLBASE SQL and conversion of the database data from
ALLBASE/SQL format to JDBC format.
Both the JDBC Monitor and the JDBC Server must be installed on the same host
where the ALLBASE/SQL or IMAGE/SQL databases reside.
Requirements
Java Requirements
The HP JDBC Client components (the JDBC driver itself) require
a Sun-compliant JDK version 1.1 and above, which includes a JDBC
version 1.2. Install the JDK from Sun or from your platform vendor.
Individual platform vendors may have their own requirements for
the platform host. For example, Java only runs on HP-UX 10.20 and
MPE/iX 6.0 and above, HP-UX 9.x and MPE/iX 5.5 is not supported.
Java and JDK components only need to be installed on the client
platform. The JDBC server platforms use native components and must
be installed on the same host where the ALLBASE/SQL or IMAGE/SQL
databases reside.
HP-UX Server Requirements
The HP JDBC Server components require HP-UX version 10.20
or greater. HP-UX components are only required if your JDBC Server
platform is the HP-UX operating system.
MPE/iX Server Requirements
The HP JDBC Server components require MPE/iX version 6.0 or
greater. MPE/iX components are only required if your JDBC Server
platform is the MPE/iX operating system.
ALLBASE/SQL or IMAGE/SQL Requirements
The HP JDBC Server components require an ALLBASE/SQL G3.01
or IMAGE/SQL G3.00 or greater.
Installation
HP-UX Server Components
The installation of the JDBC Server components must be done by a system
administrator who has "root" capability on the server host where the database
resides. A temporary directory, /tmp/jdbc, is used to stage the user
manual, installation scripts, and product tar file.
The server components are distributed as a UNIX tape archive (TAR) file,
hpjdbc_XXX.tar, where XXX represents the release number of
the product.
Once the tar file is copied into a staging directory, /tmp/jdbc,
extract the installation shell script from the tar file:
$ tar xf hpjdbc_XX.tar install.sh
MPE/iX Server Components
The installation of the HP JDBC product must be done by a system administrator
on the server host where the database resides.
The components of HP JDBC are distributed as an archive file,
HFSFILES.JDBC.SYS. Stream the JDBC installation job I00IJDBC,
to perform the actual installation:
:stream I00IJDBC.JDBC.SYS
The installation script extracts the server files and client archive files and
set up the JDBC monitor startup scripts.
Java Client Components
The HP JDBC product must first be installed on the server
host before the client can be installed. This is because the client
files are bundled with the server product.
The HP Driver for JDBC components consist of the Driver Java
class files and a sample JDBC client source file. These are the
only components required on the client-side. The driver components
are packaged in three formats (the same files contents are in each package),
UNIX tape archive (TAR), Java archive (JAR), and Windows ZIP (ZIP).
Setting up HP Driver for JDBC client involves:
Downloading the appropriate HP JDBC archive file to a temporary
directory.
Extracting the HP JDBC archive file onto your client
platform.
Extracting the HP Driver for JDBC Class Files
The HP Driver for JDBC class files must be installed in your
Java class path so that the Java compiler and the Java class loader
can find them.
Example on the Win32 Platform
CLASSPATH=C:\JDK1.1.4\LIB;.
You would then install the HP Driver for JDBC class files in the directory:
C:\JDK1.1.4\LIB\
Example on HP 3000
CLASSPATH=/usr/local/java/latest/lib:.
In order to use the HP Driver for JDBC client on the MPE/iX
system, you need to have the JAVA/iX installed. JAVA/iX is shipped
with MPE/iX starting in Release 6.0.
The following sub-directories for the Driver class files should
be automatically created:
com/hp/jdbc/allbasecom/hp/jdbc/allbase/samples
Configuring HP JDBC Server
The HP JDBC Server components behave the same on both the
HP-UX and MPE/iX platforms. Thus both the configuration file and
log file are the same (except for filenames).
The following example shows the standard HP JDBC default server configuration
file, servcfg. This is a text file that resides on the server host
in the same directory as the HP JDBC Server executable files and
can be used to alter some of the server's behavior.
LOGFILE /opt/allbase/jdbc/logs/servlog
TIMEOUT 7200
#LOGGING ERROR
#LOGGING CONNECTION
#LOGGING INFO
#LOGGING WARNING
#LOGGING FATAL
#LOGGING IN
#LOGGING OUT
#LOGGING INHEX
#LOGGING OUTHEX
#LOGGING DEBUG
#LOGGING TIMESTAMP
LOGGING NONE
# If LOGGING NONE is not commented out, it must be the
# last line for it to have the desired effect.
The first configuration option LOGFILE refers to the path and filename
for the HP JDBC Server log file. On HP-UX this is normally set to
/opt/allbase/jdbc/logs/servlog. On MPE/iX this is normally set to
SERVLOG.JDBC.SYS.
The second configuration option TIMEOUT specifies the number of
seconds that the HP JDBC Server can remain idle before terminating.
The next set of lines are the LOGGING levels used to determine the
type of information to be logged to the server log file. The meanings of the
various logging levels are discussed in the Troubleshooting Section, under
"Server Logging" of the HP Driver for JDBC User's Manual.
The server configuration file is read in each time a new server process is
started. Thus once the file is edited and saved, the changes takes place
beginning with the next server process.
If logging is turned on in this file, it is in effect for all subsequent server
processes.
Starting and Stopping HP JDBC Monitor
HP JDBC Monitor is the server-side counterpart of HP Driver
for JDBC, present on the client-side. For a JDBC application to
connect to a database, the JDBC Monitor must be running on the database
server. JDBC Monitor can be started manually or automatically at
system startup by including the JDBC Monitor start commands in the
system startup scripts.
HP-UX Monitor Startup and Shutdown
The startup and shutdown of the HP-UX JDBC Monitor is normally
controlled by the system startup and shutdown scripts. Thus as long
as the machine is up and running, the JDBC Monitor will also be up.
The only time the Monitor should ever be brought down is to install a newer
version. In this case, use the monctrl command to kill the Monitor
process. Do not use the UNIX kill command, as this could render the
server unable to start a new Monitor process. The monctrl command to
shut down the monitor is:
monctrl kill [portnumber]
The portnumber parameter is necessary only if the monitor you
wish to shut down is not running on the default port number 31700.
To restart the monitor after it has been accidentally terminated or was shut
down, use the monctrl command to start it up:
monctrl start [portnumber]
Again, the portnumber parameter is necessary only if you wish
to start the monitor on a port number other than the default port number 31700.
You cannot start a monitor on the same port number as a currently
running monitor. You also can not restart a monitor on its original
port number until all child processes that were spawned by the previous
monitor process are terminated, and the port released. If the monitor
is intentionally terminated or accidentally terminates, all of its
child processes must be terminated before it can be restarted. For
this reason, the monitor must not be terminated by using the UNIX
kill command. Always use the monctrl tool to kill the monitor.
The tool searches out all the child processes and kill them first, before
killing the monitor.
MPE/iX Monitor Startup and Shutdown
The startup and shutdown of the MPE/iX HP JDBC Monitor is normally done by the
startup and shutdown stream jobs, JSTRTMON and JSTOPMON,
which are normally included in MPE/iX system startup and shutdown scripts. Thus
as long as the machine is up and running, the JDBC Monitor will also be up.
The only time the monitor should ever be brought down is to install a newer
version. In this case, use the JSTOPMON stream job or ABORTJOB
command to kill the monitor process. The ABORTJOB command to shut down
the monitor is:
:ABORTJOB #JXX
where XX is the job number.
To restart the monitor after it was shut down or accidentally terminated, use
the JSTRTMON stream job to start it up.
:STREAM JSTRTMON.JDBC.SYS
On the MPE/iX platform, each client connection is serviced
by a separate process that is in the same session as the monitor.
Thus, if the monitor is terminated or dies, the existing server
process already serving the client requests also dies. This could
result in client applications experiencing dropped connections.
Using the HP Driver for JDBC
Java Class Path
Before the HP Driver for JDBC can be used, the Java class
path must be set to include the HP Driver for JDBC class files.
Without this setting, both the Java compiler and the Java Run Time
Environment is not able to locate the HP Driver for JDBC. Please
refer to your JDK documentation for more
explicit details as to how to set the Java class path. In general,
the Java class path must include the directory in which the HP Driver
for JDBC class files have been installed.
If the HP Driver for JDBC class files are installed in:
/opt/java/lib/com/hp/jdbc/allbase
then the Java class path must include the directory:
/opt/java/lib
In most cases, this would mean that the CLASSPATH environment variable
must be set to something resembling:
Example on the HP 9000
CLASSPATH=/opt/java/lib:.
Sample JDBC Client
The HP Driver for JDBC comes with the source code to two JDBC
applications, a sample client and a simple client. Both can be used
to test the installation of JDBC components on the client and server.
The simple client is called SimpleClient and is a bare-bones
text-based application that makes a connection to an ALLBASE/SQL or IMAGE/SQL
database, and allows
the user to send SQL statements and retrieve the results. There
are no frills in the application, to make the code as simple as
possible. This client should mainly be used for educational purposes.
The rest of this discussion focuses on the more robust application.
The sample client is called SampleClient and is a more robust version
of the simple client. It shows how to use a dialog box to obtain user input,
and also formats the result set output a lot better.
The first step to using the sample client is to build it by compiling the Java
source code. Normally, you would do this by using the Java compiler command
(the command you use may be different, depending on your JDK):
NOTE: If you are using JDK1.2.1 or later, add the following line in
SampleClient.java before the call to class.forName:
DriveManager.setLogStream(System.Out);
javac SampleClient.java
To run the sample client, follow the instructions provided
with your Java SDK for your client platform. Make sure that your
Java CLASSPATH includes the directory in which the HP Driver
for JDBC class files have been installed, so that the Java Virtual
Machine is able to load them. For most platforms, the command to
run the sample client is:
java SampleClient [-w] [-t]
Follow the prompts given by the application to connect to
your database and to execute SQL statements.
The sample client can be used as a starting point for developing
your own applications or for troubleshooting connection problems
with the database.
Loading the HP Driver for JDBC
The Java method that is used to load all JDBC drives is the
class.forName method. To load the HP Driver for JDBC, the code is:
class.forName("com.hp.jdbc.allbase.JdbcDriver");
This loads the HP Driver for JDBC and registers it with the
JDBC Driver Manager. Once a driver has been loaded and registered
with the Driver Manager, it is ready to be used to connect to a database.
URL Syntax (Including User Name and Password)
The first connection method specifies all connection parameters,
including the user name and password, in an URL string.
java.sql.DriverManager.getConnection(url)
where:
Optional port number on which the JDBC Monitor is listening. If not
specified, the default part number 31700 is used.
database
ALLBASE/SQL or IMAGE/SQL database name.
uid
Server host userid that is authorized to access the database.
pwd
Server host password that matches the user id provided above.
trace
Optional trace values separated by the vertical bar "|" character. For
more information on tracing, see the Troubleshooting section.
NOTE: This connection method may result in the user name and
password being logged in various places, as URLs are commonly logged
on various web servers and proxy servers. The URL is also logged
if tracing is turned on. For this reason, the second connection
method is preferred.
URL Syntax (Without User Name and Password)
The second connection method specifies the user id and password as method
arguments, so this information is not present in the URL.
java.sql.DriverManager.getConnection(url, uid, pwd)
where:
Optional port number on which the JDBC Monitor is listening. If not
specified, the default part number 31700 is used.
database
ALLBASE/SQL or IMAGE/SQL database name.
uid
Server host userid that is authorized to access the database.
pwd
Server host password that matches the user id provided above.
trace
Optional trace values separated by the vertical bar "|" character. For
more information on tracing, see the Troubleshooting section.
ALLBASE/SQL Specifics
ALLBASE/SQL to JDBC Data Type Mapping
Table 5-1 "Data Type Mapping" shows what
the HP Driver for JDBC reports as the JDBC data type for each ALLBASE/SQL data
type. These are the java.sql.Types values that are returned from the
java.sql.ResultSetMetaData.getColumnType method. An "X" in the column
indicates the data type mapping.
Table 5-1 Data Type Mapping
JDBC DATA TYPES (vertical)
T I N Y I N T
S M A L L I N T
I N T E G E R
B I G I N T
R E A L
F L O A T
D O U B L E
D E C I M A L
N U M E R I C
B I T
C H A R
V A R C H A R
L O N G V A R C H A R
B I N A R Y
V A R B I N A R Y
L O N G V A R B I N A R Y
D A T E
T I M E
T I M E S T A M P
ALLBASE/SQL DATA TYPES (horizontal)
SMALLINT (16-bits)
X
INTEGER (32-bits)
X
REAL
X
FLOAT(1...24)
X
FLOAT(25...53)
X
DOUBLE PRECISION
X
DECIMAL
X
NUMERIC
X
CHAR
X
VARCHAR
X
DATE
X
TIME
X
DATETIME
X
INTERVAL
X
ALLBASE/SQL to JDBC Data Type Conversions
Table 5-2 "Data Type Conversions" shows the
supported data type conversions between ALLBASE/SQL and JDBC. For conversions
from JDBC to Java, please refer to a JDBC book or the Java JDBC documentation.
Those conversions are generic to all JDBC Drivers.
Suggested conversions are denoted by a capital letter "X" in
the conversion grid. Conversions which are supported, but which
may result in a loss of precision, overflow, or rounding, are denoted
by a lowercase letter "x" in the conversion grid.
Table 5-2 Data Type Conversions
JDBC DATA TYPES (vertical)
T I N Y I N T
S M A L L I N T
I N T E G E R
B I G I N T
R E A L
F L O A T
D O U B L E
D E C I M A L
N U M E R I C
B I T
C H A R
V A R C H A R
L O N G V A R C H A R
B I N A R Y
V A R B I N A R Y
L O N G V A R B I N A R Y
D A T E
T I M E
T I M E S T A M P
ALLBASE/SQL DATA TYPES (horizontal)
SMALLINT (16-bits)
x
X
X
X
X
X
X
X
X
x
X
X
X
x
x
x
INTEGER (32-bits)
x
x
X
X
x
x
x
X
X
x
X
X
X
x
x
x
REAL
x
x
x
x
X
X
X
X
X
X
X
X
X
x
x
x
FLOAT(1...24)
x
x
x
x
x
x
X
X
X
X
X
X
X
x
x
x
FLOAT(25...53)
x
x
x
x
x
x
X
X
X
X
x
x
x
x
x
x
DOUBLE PRECISION
x
x
x
x
x
x
X
X
X
x
X
X
X
x
x
x
DECIMAL
x
x
x
x
x
x
x
X
X
x
X
X
X
x
x
x
NUMERIC
x
x
x
x
x
x
x
X
X
x
X
X
X
x
x
x
CHAR
x
x
x
x
x
x
x
x
x
x
X
X
X
x
x
x
VARCHAR
x
x
x
x
x
x
x
x
x
x
X
X
X
x
x
x
DATE
X
X
X
x
x
x
X
x
TIME
X
X
X
x
x
x
X
x
DATETIME
X
X
X
x
x
x
x
x
X
INTERVAL
X
X
X
x
x
x
The conversion of any ALLBASE/SQL data type to the java.sql.Types.BIT
data type is such that only the value of zero, in either numeric or character
format, is converted to the java.sql.Types.BIT value of 0. All other
values are converted to the java.sql.Types.BIT value of 1. Thus only
the integer value 0, the floating-point value 0.0, the decimal value 0 (not
0.0), and the character string "0" is converted to a bit value
of 0. Everything else is converted to a bit value of 1.
The conversion of an ALLBASE/SQL data type to a JDBC data
type that has a smaller degree of precision (such as conversion
from ALLBASE/SQL INTEGER to java.sql.Types.SMALLINT)
follows the Java VM rules of casting one data
type to another. This may result in different values on different
Java platforms. There is also no warning generated when this occurs.
It is up to the application developer to choose the appropriate JDBC
data type.
The conversion of an ALLBASE/SQL character data type to a JDBC numeric data
type uses the Java numeric conversion routines and any necessary numeric
casting. Thus this could result in a java.lang.NumberFormatException
being shown when the conversion is performed.
Again, the application developer should take the necessary precautions.
Unsupported ALLBASE/SQL Data Types
The ALLBASE LONG BINARY, and LONG VARBINARY data types are
currently not supported by the HP Driver for JDBC.
Acceptable SQL Syntax
The HP JDBC Server accepts either ODBC SQL statement syntax
or ALLBASE/SQL statement syntax. All SQL statements are first parsed
for ODBC 2.0 SQL syntax. If the statement conforms to the ODBC 2.0
SQL syntax, it is translated to ALLBASE/SQL before being passed
to the ALLBASE/SQL DBMS. If the statement does not conform to the
ODBC 2.0 SQL syntax rules, it is assumed to be an ALLBASE/SQL statement,
and is passed without modification to the ALLBASE/SQL SQL DBMS.
Unsupported ALLBASE/SQL SQL Statements
Table 5-3 "Unsupported Statements" shows a
list of unsupported ALLBASE/SQL statement types in HP JDBC. Note that in many
cases, JDBC provides a standardized method of performing the same action. For
example, the ALLBASE/SQL "COMMIT WORK" statement is not supported,
since the user should be using the java.sql.Connection.commit method.
Table 5-3 Unsupported Statements
ADVANCE
BEGIN DECLARE SECTION
BEGIN WORK
CLOSE
COMMIT WORK
CONNECT
DECLARE CURSOR
DELETE WHERE CURRENT
DESCRIBE
DISCONNECT
END DECLARE SECTION
EXECUTE
EXTRACT
FETCH
INCLUDE
OPEN
PREPARE
RELEASE
ROLLBACK WORK
SET CONNECTION
SET SESSION
SET TRANSACTION
SETOPT
START DBE
STOP DBE
SQLEXPLAIN
UPDATE WHERE CURRENT
TERMINATE USER
WHENEVER
Dynamic/Parameterized SQL Statements
The HP Driver for JDBC supports the use of parameterized SQL statements through
the java.sql.PreparedStatement interface. The SQL statements must use
a question mark (?) as the marker character for passing the parameters.
For example, a SELECT statement with parameters in the where clause
would look like:
SELECT NAME, ADDRESS FROM ADDRBOOK WHERE NAME=?
Another example is an INSERT statement:
INSERT INTO ADDRBOOK(NAME, ADDRESS) VALUES (?,?)
Stored Procedures
The HP Driver for JDBC supports the following types of ALLBASE/SQL stored
procedures through the java.sql.CallableStatement interface:
Procedures that return one or more result sets.
Procedures that take one or more input arguments.
Procedures that return one or more output arguments.
Procedures that have a return status value.
Troubleshooting
Client Tracing
Tracing of the HP Driver for JDBC client class files is invoked
by adding tracing commands to the connection URL. The connection
URL can be altered to both invoke tracing and to control the type
of information that is traced. The tracing information is sent to
a Java stream, which must be specified by the application using
the java.sql.DriverManager.setLogStream method.
Both the tracing level and tracing output must be specified before any tracing
can be done.
Note that the use of tracing impacts performance of the application.
The greater the detail in the tracing, the slower the performance
of the application.
The URL syntax to invoke tracing is:
"jdbc:allbase://server[:port]/database?TRACE=trace"
where trace is any vertical bar (|) separated combination
of the values:
ARGUMENTS
Trace HP Driver for JDBC class method arguments. Input arguments and
return values are all traced. Only the methods called by the application
are traced.
ARGUMENTS_ALL
Trace all HP Driver for JDBC class methods that are called by both the
application and the driver itself.
TIME
Include the time in HHMMSSFFF format on all tracing output
lines where HH is the hour from 00 to 23, MM is the
minute from 00 to 59, SS is the second from 00 to 59, and FFF is
the millisecond from 000 to 999. The information appears in the third
column of the tracing output.
DATE
Include the date in YYYYMMDD format on all tracing output
lines where YYYY is the year from 0000 to 9999, MM is
the month from 01 to 12, DD is the day of the month from 00 to
31. This information appears in the second column of the tracing
output.
TIMESTAMP
Include both the date and the time on all tracing output lines. This
is just a combination of the DATE and TIME tracing
values.
OBJECT
Include the object hash code in the format HHHHHHHH on all
tracing output lines where HHHHHHHH is the eight digit
hexadecimal value of the hash code. This information appears in the first
column of the tracing output.
THREAD
Include the executing thread name on all tracing output lines. This
information appears as the fourth column of the tracing output.
DEBUG
Trace debugging statements from the driver. This produces a large
amount of output, so it should be used sparingly.
CONNECTION
Trace driver connection information.
NETWORK
Trace driver network information.
ERROR
Trace driver errors.
WARNING
Trace driver warnings.
SQL
Trace SQL statements that are passed to the driver.
ALL
Trace everything.
For example, the URL to turn on tracing of all connections with time stamp
information would be:
Do not user any space characters between tracing levels and
the vertical bar separator as some browsers and web servers do not
allow space characters in a URL.
NOTE: Remember that the application must also set the tracing
stream using the java.sql.DriverManager.setLogStream method.
Server Logging
The server configuration file controls logging of the JDBC
Server. The following is a list of the various logging levels that
can be set in the server configuration file.
NONE
Turns off all preceding logging levels.
CONNECTION
Turns on logging of connection information such as the time of the
connection, the client IP address, and the name of the database.
IN
Turns on logging of input information coming from the client.
INHEX
Turns on logging of the hexadecimal dump of the input information
coming from the client.
OUT
Turns on logging of output information being sent back to the
client.
OUTHEX
Turns on logging of the hexadecimal dump of the output information
being sent back to the client.
INFO
Turns on logging of miscellaneous information about the processing of
the server. This has the potential to log a fair amount of data, which
may affect performance.
TIMESTAMP
This causes each log entry to be prefaced by a timestamp of the form
HH:MM:SS.mmm.
WARNING
Turns on logging of warning messages generated by the Server.
ERROR
Turns on logging of error messages generated by the Server.
FATAL
This is the same as specifying INFO, WARNING, and
ERROR.
DEBUG
Turns on logging of a lot of debugging messages. This should only be
done at the request of support personnel. This generates a large amount
of logging information and can severely affect performance.
MPE/IX Software Developer's Kit (SDK) for Java, Version 1.2.2