From Ingres Community Wiki

This project is about making Ingres JDBC driver compliant with JDBC 4.0 specification.

Contents 1 The JDBC 4.0 API 2 JDK/JRE Requirements 3 List of JDBC 4.0 Features that will be Implemented Now 4 List of JDBC 4.0 Features that may be Implemented in the Future if Necessary or as DBMS Capabilities Permit 5 Classification of New JDBC 4.0 Methods Based on Implementation 6 Design Specifications 7 Ingres Enhancement Number 8 Sample Unit Tests 9 Implementation Status 10 Documentation

The JDBC 4.0 API

The JDBC API was originally introduced in the year 1997 based on X/Open SQL CLI ( which is also basis for ODBC) to provide programmatic access to relational data from the Java programming language. The JDBC 4.0 API introduced in 2006 is the current version of JDBC API included in both Java SE and Java EE platforms. The two packages of JDBC 4.0 API are java.sql and javax.sql.

JDK/JRE Requirements

To implement/support JDBC 4.0 features in the Ingres JDBC driver JDK 1.6 and Java JRE version 6 is required.

List of JDBC 4.0 Features that will be Implemented Now

• Data types NCHAR, NVARCHAR, LONGNVARCHAR, NCLOB

New distinct JDBC types for NCS values. Java representation is identical to other string types, so distinction is primarily for driver/DBMS benefit. DBMS now supports conversion between NCS and installation character types. Driver currently supports NCS types internally and transparently same as other character types.

Internally, very little additional work is required. Primary change in driver for existing applications will be the declaration of NCS columns as one of the N* types rather than standard character types. Driver will support both standard and NCS character types to be accessed through standard and new N* methods.

• setN*() in PreparedStatement
• getN*() and setN*() in CallableStatement
• getN*() and updateN*() in ResultSet

• SQL Exception hierarchy

JDBC now declares an extensive SQLException sub-class hierarchy with 13 new exception classes. This will permit applications to catch various categories of exceptions for handling rather than a single class requiring content examination to determine handling.

Usage of the driver internal SqlEx exception class will likely need to be dropped as the difficulty in supporting the new exception classes will be too difficult using the same model.

One option is to make the SqlEx class static as a factory for SQL exceptions. This will require tedious but simple bookkeeping type changes to almost all methods in the driver.

A second option is to keep the SqlEx class in the generic packages (utils and messaging) and translate to JDBC exceptions at some point, either directly above the generic methods or just prior to returning to the application. In either case, significant changes to SqlEx are needed.

• Large Object enhancements

A number of new methods have been added for enhanced support of Large Objects, including i8 lengths for sizes > 2G. All are easy additions to current driver capabilities:

• New NClob class
• Creating local LOB objects (Connection): createBlob(), createClob(), createNClob()
• Freeing LOB resources(Blob, Clob, NClob): free()
• Setting LOB parameters from streams:
  • set*lob() in PreparedStatement by index with i8 lengths & without lengths
  • set*lob() in CallableStatement by name with i8 lengths & without lengths
  • update*log() in ResultSet by index & name with i8 lengths & without lengths.
• Setting stream parameters:
  • set*Stream() in PreparedStatement by index with i8 lengths & without lengths
  • set*Stream() in CallableStatement by name with i8 lengths & without lengths
  • update*Stream() in ResultSet by index & name with i8 lengths & without lengths
  • get*Stream() in Blob, Clob, NClob with position and length
  • setObject() in PreparedStatement, CallableStatement and updateObject() in
  • ResultSet scale parameter may be length for stream objects.

• Auto-loading of drivers

The JDBC provided DriverManager class searches for a manifest file in the Java CLASSPATH. The contents of the file is the class name of the Driver class to be loaded.

• Connection Validation

Method isValid() has been added to Connection class to test for active connection. The method is permitted to issue a query against the DBMS and takes a timeout value. Will need to determine a suitable query.

List of JDBC 4.0 Features that may be Implemented in the Future if Necessary or as DBMS Capabilities Permit

• Unwrapping JDBC classes

Wrapper interface allows applications to utilize the generic JDBC interfaces via the JDBC API and still access DBMS specific functionality by unwrapping driver classes. This allows drivers to provide DBMS specific functionality while still maintaining the generic JDBC interface architecture. JDBC spec suggests that drivers should support all native DBMS capabilities. Drivers must document driver class hierarchies and public methods.

Wrapper interface defines methods isWrapperFor() & unWrap() and is a base interface for the JDBC interfaces Connection, DataSource, DatabaseMetaData, Statement, PreparedStatement, CallableStatement, ResultSet, ParameterMetaData, ResultSetMetaData.

• Support for EE Connector SPI

JDBC spec notes that the EE Connector SPI interface may be supported by drivers in addition to the JDBC driver manager and DataSource interfaces. The driver may provide wrappers or implement the SPI requirements directly. The driver must also be packaged in a Resource Archive (RAR) file in addition to the standard jar packaging.

• Connection client Information

Client connection information which may be stored in a DBMS special register, session parameter, or database table. Properties are name/value pairs with ApplicationName, ClientUser, ClientHostname as examples. This info appears to be similar to that passed in the GCA_MD_ASSOC parameter describing the client application environment, except that our information is not accessible to the application. Info is set/accessed after connection establishment and meta-data provides list of known properties.

• Driver provided Connection Pool Manager DataSource

Connection pool managers are usually provided by application server (EE) environments. JDBC spec notes that drivers may provide a Connection Pool Manager DataSource to permit connection pooling in other environments.

• Poolable statements

Poolable statements are supported in conjunction with poolable connections. Pooled statements seem to be a cross between prepared statements and repeat queries. These statements can exist for the life of the physical connection and may be re-used across instances of logical sessions during connection pooling. They are not shared across physical connections. Pooled connections provide statement events to permit connection pools to also manage statements.

• Stored Functions

User defined functions are not supported as Ingres does not support them.

• Data types ROWID, SQLXML

New distinct types for row identifiers and XML. Row identifiers have implementation dependent lifetimes of transaction, session, or forever.

• Further research to support escape syntax for calling stored procedures in Statement and PreparedStatement interfaces in addition to CallableStatement for Java EE JDBC compliance revealed complex implementation issues that are further discussed here.

Classification of New JDBC 4.0 Methods Based on Implementation

The new JDBC 4.0 methods to be implemented will be classified into the following implementation types.

• Unsupported - throws SQLException

• createStruct(String typeName, Object[] attributes)
• createSQLXML()
• setSQLXML(int parameterIndex, SQLXML xmlObject)
• setSQLXML(String parameterName, SQLXML xmlObject)
• getSQLXML(String parameterName)
• getSQLXML(String parameterIndex)
• getSQLXML(String columnLabel)
• getSQLXML(String columnIndex)
• updateSQLXML(String columnLabel, SQLXML xmlObject)
• updateSQLXML(String columnIndex, SQLXML xmlObject)
• setRowId(int paramIndex, RowId rid)
• getRowId(int parameterIndex)
• getRowId(String parameterName)
• setRowId(String parameterName, RowId x)
• getRowId((String columnLabel)
• getRowId(int columnIndex)
• updateRowId(String columnLabel, RowId x)
• updateRowId(int columnIndex, RowId x)
• createArrayOf(String typeName, Object[] elements)

• Stub - will be implemented later, throws SQLException for now

• free()
• isWrapperFor(Class<?> iface)
• unwrap(Class iface)
• isValid(int timeout)

• Wrapper - simply calls another method that is already implemented by the driver.

• setNString( int paramIndex, String value )
• getBinaryStream(long pos, long length)
• getCharacterStream(long pos, long length)
• createNClob()
• createBlob()
• createClob()
• setNCharacterStream(int parameterIndex, Reader value, long length)
• setNClob(int parameterIndex, NClob value)
• setClob(int parameterIndex, Reader reader, long length)
• setBlob(int parameterIndex, InputStream? inputStream, long length)
• setNClob(int parameterIndex, Reader reader, long length)
• setAsciiStream(int parameterIndex, InputStream x, long length)
• setBinaryStream(int parameterIndex, InputStream x, long length)
• setCharacterStream(int parameterIndex, Reader reader, long length)
• setAsciiStream(int parameterIndex, InputStream x)
• setBinaryStream(int parameterIndex, InputStream x)
• setCharacterStream(int parameterIndex, Reader reader)
• setNCharacterStream(int parameterIndex, Reader value)
• setClob(int parameterIndex, Reader reader)
• setBlob(int parameterIndex, InputStream inputStream)
• setNClob(int parameterIndex, Reader reader)
• setNString(String parameterName, String value)
• setNCharacterStream(String parameterName, Reader value, long length)
• setBlob(String parameterName, InputStream inputStream)
• setBlob(int parameterIndex, InputStream inputStream, long length)
• setBlob(String parameterName, InputStream inputStream, long length)
• setClob(String parameterName, Reader reader)
• setClob(String parameterName, Reader reader, long length)
• setNClob(String parameterName, NClob value)
• setNClob(String parameterName, Reader reader)
• setNClob(String parameterName, Reader reader, long length)
• getNClob(int parameterIndex)
• getNClob(String parameterName)
• getNString(int parameterIndex)
• getNString(String parameterName)
• getNCharacterStream(int parameterIndex)
• getNCharacterStream(String parameterName)
• getCharacterStream(int parameterIndex)
• getCharacterStream(String parameterName)
• setAsciiStream(String parameterName, InputStream x, long length)
• setBinaryStream(String parameterName, InputStream x, long length)
• setCharacterStream(String parameterName, Reader reader, long length)
• setAsciiStream(String parameterName, InputStream x)
• setBinaryStream(String parameterName, InputStream x)
• setCharacterStream(String parameterName, Reader reader)
• setNCharacterStream(String parameterName, Reader value)
• getNClob(String columnLabel)
• getNClob(int columnIndex)
• getNCharacterStream(String columnLabel)
• getNCharacterStream(int columnIndex)
• getNString(String columnLabel)
• getNString(int columnIndex)
• updateNClob(int columnIndex, Reader reader)
• updateNClob(String columnLabel, Reader reader)
• updateClob(String columnLabel, Reader reader)
• updateClob(int columnIndex, Reader reader)
• updateBlob(String columnLabel, InputStream inputStream)
• updateBlob(int columnIndex, InputStream inputStream)
• updateCharacterStream(String columnLabel, Reader reader)
• updateBinaryStream(String columnLabel, InputStream x)
• updateAsciiStream(String columnLabel, InputStream x)
• updateCharacterStream(int columnIndex, Reader x)
• updateBinaryStream(int columnIndex, InputStream x)
• updateAsciiStream(int columnIndex, InputStream x)
• updateNCharacterStream(String columnLabel, Reader reader)
• updateNCharacterStream(int columnIndex, Reader x)
• updateNClob(String columnLabel, Reader reader, long length)
• updateNClob(int columnIndex, Reader reader, long length)
• updateClob(String columnLabel, Reader reader, long length)
• updateClob(int columnIndex, Reader reader, long length)
• updateBlob(String columnLabel, InputStream inputStream, long length)
• updateBlob(int columnIndex, InputStream inputStream, long length)
• updateCharacterStream(String columnLabel, Reader reader, long length)
• updateBinaryStream(String columnLabel, InputStream x, long length)
• updateAsciiStream(String columnLabel, InputStream x, long length)
• updateCharacterStream(int columnIndex, Reader x, long length)
• updateBinaryStream(int columnIndex, InputStream x, long length)
• updateAsciiStream(int columnIndex, InputStream x, long length)
• updateNCharacterStream(String columnLabel, Reader reader, long length)
• updateNCharacterStream(int columnIndex, Reader x, long length)
• updateNClob(String columnLabel, NClob nClob)
• updateNClob(int columnIndex, NClob nClob)
• updateNString(String columnLabel, String nString)
• updateNString(int columnIndex, String nString)

• Trivial - easily implemented

• addStatementEventListener(StatementEventListener listener)
• removeStatementEventListener(StatementEventListener listener)
• getClientInfoProperties()
• createStruct(String typeName, Object[] attributes)
• getHoldability()
• isClosed()
• setPoolable(boolean poolable)
• isPoolable()
• supportsStoredFunctionsUsingCallSyntax()
• autoCommitFailureClosesAllResultSets()
• getFunctions(String catalog, String schemaPattern, String functionNamePattern)
• getFunctionColumns(String catalog, String schemaPattern,String functionNamePattern, String columnNamePattern)
• getSchemas(String catalog, String schemaPattern)
• getClientInfo()
• getClientInfo(String name)
• setClientInfo(Properties properties)
• setClientInfo(String name, String value)
• getRowIdLifetime()

Design Specifications

The Detailed Design Specification is available at JDBC 4.0 Compliance Specification.

Ingres Enhancement Number

SIR Number 121238

Sample Unit Tests

Some sample unit tests are provided at Ingres JDBC 4.0 Tests.

Implementation Status

• All the new JDBC 4.0 methods had been added to the Ingres JDBC driver source so that the driver can be successfully compiled with JDK 1.6 which is a requirement. The changes have been prototyped in a development codeline.
• SQLException hierarchy implementation have been completed.
• The new methods for data types NCHAR, NVARCHAR, LONGNVARCHAR, and NCLOB in PreparedStatement, Resultset, and CallableStatement interfaces have been completed.
• Implementation of JDBC 4.0 DatabaseMetaData interface changes in the driver have been completed.
• Implementation of JDBC 4.0 Connection, Statement interface changes have been completed.
• Implementation of free() method for freeing LOB resources(Blob, Clob, NClob) has been completed.
• Implementation of get*Stream() methods in Blob, Clob, NClob with position and length have been completed.
• Fix JdbcConn.isValid() - Done.
• Fix JdbcDBMD.getRowIdLifetime() - Done
• Fix update*Stream() methods in RsltUpd -Done
• Add README file which shows how to map the files and directories under common!gcf!gcj and the command line to correctly build the driver archive for autoloading feature. - Done
• Implement isWrapperFor() and unwrap() for JDBC 4.0 API compliance. - Done.
• Implement new scalar functions for Java EE JDBC compliance. - Done.
• Fix the semantics of scale in setObject() and updateObject() to see if the length of streams can be used (backward compatibility is also an issue). - Completed research on this. The scale parameter also represents length of the characters (Reader) or bytes present (InputStream) values. It has been decided for now that the length parameter will not be taken into account for further processing for these data types in the initial JDBC 4.0 implementation. The requirements to support this semantics change of scale in the driver will be re-visited. Also there are some additional clarifications in the Java 1.6 documentation that setXXX() and UpdateXXX() methods need to throw SQLException if the PreparedStatement/ResultSet is already closed. This check is not done in the initial implementation.

The Ingres JDBC 4.0 compatible driver JAR file (iijdbc.jar) and the JDBC information utility (both require JRE 1.6) have been made available at code.ingres.com: Revision 2084 for further use. The JDBC 4.0 compatible Ingres driver version is 4.0.0.

Documentation

JDBC 4.0 Support

The following features of JDBC 4.0 are included in the Ingres JDBC driver:

• Automatic loading of the driver

Applications do not need to explicitly load the driver by calling Class.forName(). Existing applications that currently load the Ingres JDBC driver using Class.forName() will continue to work without modification.

• National Character Set support

The Ingres JDBC driver provides support for the new setter, getter, and updater methods for NCHAR, NVARCHAR, LONGNVARCHAR, and NCLOB types.

• Support for createBlob(), createClob(), and createNClob Methods

Applications can use the above methods for creating BLOB, CLOB, NCLOB objects.

• Support for java.sql.Wrapper Interface

Applications can use the Wrapper interface to access extensions to the JDBC API that are specific to the Ingres JDBC driver, but currently the driver does not implement any such extensions that the applications can benefit from.

The following features of JDBC 4.0 are not supported:

• PooledConnection interface

The Ingres JDBC driver does not support Statement pooling and does not implement the JDBC 4.0 methods addStatementEventListener() and RemoveStatementEventListener().

• Calling stored procedures

The Ingres JDBC driver does not support escape syntax for calling stored procedures in Statement and PreparedStatement.