HP Driver for JDBC

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

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.

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.

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.

The HP JDBC Server components require an ALLBASE/SQL G3.01 or IMAGE/SQL G3.00 or greater.

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

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 will extract the server files and client archive files and set up the JDBC monitor startup scripts.

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:

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 will take place beginning with the next server process.

If logging is turned on in this file, it will be in effect for all subsequent server processes.

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.

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 will not be 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:

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 will focus 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.

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.

The first connection method specifies all connection parameters, including the user name and password, in an URL string.

java.sql.DriverManager.getConnection(url)

where:

String url =

      "jdbc:allbase://host[:port]/database?UID=uid&PWD=pwd"

      "[&TRACE=trace]";

	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.

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:

String url =       "jdbc:allbase://host[:port]/database[?TRACE=trace]";String uid = "uid";String pwd = "pwd";

Table 3-1 “Data Type Mapping” shows what the HP Driver for JDBC will report as the JDBC data type for each ALLBASE/SQL data type. These are the java.sql.Types values that will be returned from the java.sql.ResultSetMetaData.getColumnType method. An "X" in the column indicates the data type mapping.

Table 3-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.

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, will be converted to the java.sql.Types.BIT value of 0. All other values will be 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" will be 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) will follow 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.

The ALLBASE LONG BINARY, and LONG VARBINARY data types are currently not supported by the HP Driver for JDBC.

The HP JDBC Server will accept 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.

Table 3-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.

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 (?,?)

The HP Driver for JDBC supports the following types of ALLBASE/SQL stored procedures through the java.sql.CallableStatement interface:

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 will impact 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:

For example, the URL to turn on tracing of all connections with time stamp information would be:

"jdbc:allbase://server/database?TRACE=CONNECTION|TIMESTAMP"

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.

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.