JDBC Implementation Considerations

This section presents several programming considerations and guidelines related to implementing Java applications in the Ingres environment.

JDBC User ID Options

The Ingres JDBC Driver does not require a user ID and password to establish a connection when the Ingres DAS is running on the same machine as the Java client. When a userID/password is not provided, the Java client process user ID is used to establish the DBMS connection. If the target database specification includes a VNODE, the VNODE login information is used to access the DBMS machine. Optionally, a userID/password can be provided and is handled as described below.

When the Java client and DAS are on different machines, a user ID and password are required to establish a connection to the DBMS. If the DAS and DBMS server are running in the same Ingres instance (no VNODE in target database specification), the userID/password is used to validate access to the DAS/DBMS machine.

When the DAS and DBMS servers are on different machines, a VNODE is required in the target database specification. The VNODE provides the connection and (optionally) login information needed to establish the DBMS connection.

The driver property vnode_usage determines how the VNODE is used to access the DBMS. The vnode_usage property also determines the context (DAS or DBMS) in which the application userID/password is used. VNODE usage without a userID/password is described above. If the target database specification does not contain a VNODE, the vnode_usage property is ignored.

When vnode_usage is set to 'connect', only global VNODE connection information is used to establish the DBMS connection. The application-provided user ID and password are used in the DBMS context to access the DBMS machine.

When vnode_usage is set to 'login', both connection and login VNODE information is used to access the DBMS machine. The application-provided user ID and password are used in the DAS context, allowing access to private and global VNODEs.

How Transactions Are Autocommitted

Application developers must be aware that the DBMS Server imposes severe limits on the operations that can be performed when autocommit is enabled (the JDBC default transaction mode) and a cursor is opened. In general, only one cursor at a time can be open during autocommit, and only cursor-related operations (cursor delete, cursor update) can be performed. Violating this restriction results in an exception being thrown with the message text:

No MST is currently in progress, cannot declare another cursor

Cursors are opened by the Statement and PreparedStatement executeQuery() methods and remain open until the associated ResultSet is closed. The driver closes a cursor automatically when the end of the result set is reached, but applications must not rely on this behavior. JDBC applications can avoid many problems by calling the close() method of each JDBC object when the object is no longer needed.

autocommit_mode Connection Property—Set Autocommit Processing Mode

The Ingres JDBC Driver provides alternative autocommit processing modes that help overcome the restriction of autocommitting transactions or handle problems that applications have with closing result sets.

The autocommit processing modes can be selected by setting the connection property"'autocommit_mode" to one of the following values. For additional information, see JDBC Driver Properties.

Cursors and Result Set Characteristics

Ingres cursors and JDBC result sets both have an associated concurrency characteristic specifying that the object is readonly or updateable. The Ingres JDBC Driver automatically provides an updateable ResultSet when the associated cursor is updateable. The JDBC readonly/update mode characteristics are used by the Ingres Driver to control the mode of the resulting cursor.

For an updateable cursor, row updates and deletes can be performed using the updateable ResultSet interface or by using a separate JDBC Statement to issue positioned update and delete statements on the cursor. The cursor name needed to issue a positioned update or delete statement can be assigned using the Statement method setCursorName() or obtained by using the ResultSet method getCursorName().

Cursor concurrency can be specified using the 'FOR READONLY' or 'FOR UPDATE' clause in the SELECT statement. The Ingres JDBC Driver supports the JDBC syntax 'SELECT FOR UPDATE' (and also 'SELECT FOR READONLY') and translates this to the correct Ingres syntax.

A cursor is opened as readonly if one of the following is true (listed in descending precedence):

• The SELECT statement contains the 'FOR READONLY' clause.
• The associated statement was created using a Connection method that specified the concurrency as ResultSet.CONCUR_READ_ONLY.
• The connection is readonly (Connection.setReadOnly( true )).
• The connection property cursor_mode is set to 'readonly' (the default setting).
• The connection property cursor_mode is set to 'dbms' and the DBMS Server determines that the cursor cannot be updated.

A cursor is opened as updateable if one of the following is true (listed in descending precedence):

• The SELECT statement contains the 'FOR UPDATE' clause.
• The associated statement was created using a Connection method that specified the concurrency as ResultSet.CONCUR_UPDATABLE and the DBMS Server determines that the cursor can be updated.
• No other readonly condition is true and the DBMS Server determines that the cursor can be updated.

Note: The Ingres JDBC Driver does not attempt to force the cursor to be updateable even when the application requests a concurrency of ResultSet.CONCUR_UPDATABLE when creating the associated statement or the connection property cursor_mode is set to 'update'. In these cases, the cursor will be updateable if the DBMS Server determines that an updateable cursor is possible, otherwise the cursor will be readonly. The JDBC specification requires "graceful degradation" with a warning rather than throwing an exception when a requested concurrency cannot be provided.

Cursors and Select Loops

By default, the Ingres JDBC Driver uses a cursor to issue SQL select queries. Cursors permit other SQL operations, such as deletes or updates, to be performed while the cursor is open. (Operations can be restricted during autocommit. For more information, see How Transactions Are Autocommitted.

Cursors also permit multiple queries to be active at the same time. These capabilities are possible because only a limited number of result rows (frequently only a single row) are returned by the DBMS Server for each cursor fetch request. The low ratio of driver requests to returned rows results in lower performance compared to other access methods.

The Ingres JDBC Driver uses cursor pre-fetch capabilities whenever possible. Updateable cursors only return a single row for each fetch request. READONLY cursors return a fixed number of rows on each fetch request. For details, see Cursors and Result Set Characteristics. By default, the Ingres JDBC Driver obtains as many rows as fit in one communications block on each fetch request.

Depending on row size, this can greatly increase data access efficiency. The application can also specify the number of rows to be retrieved for READONLY cursors by using the setFetchSize() method.

The Ingres JDBC Driver also permits the JDBC application to use a data access method called a select loop. In a select loop request, the DBMS Server returns all the result rows in a single data stream to the driver. Because select loops use the connection while the result set is open, no other operation or query can be performed until the result set is closed.

The statement cancel() method can be used to interrupt a select loop data stream when a result set needs to be closed before the last row is processed. Because the DBMS Server does not wait for fetch requests from the driver, this access method is the most efficient available.

Select loops are enabled in the Ingres JDBC Driver by setting the driver connection property select_loop to a value of 'on.' For more information, see JDBC Driver Properties.

With select loops enabled, the driver avoids using cursors for SELECT queries unless explicitly indicated by the application. An application can request a cursor be used for a query by assigning a cursor name to the statement (setCursorName() method) or by using the JDBC syntax 'SELECT FOR UPDATE ...' to request an updateable cursor.

Database Procedures

Database procedures are supported through the JDBC CallableStatement interface. The Ingres JDBC Driver supports the following database procedure syntax.

Note: Items enclosed in brackets are optional.

For all of these statements, the Ingres JDBC Driver supports a combined parameter syntax supporting features of the ODBC positional parameter syntax and the Ingres named parameter syntax:

parameters := param | param, parameters

param := [name =] [value]

value := ? | literal | SESSION.table_name

literal := numeric_literal | string_literal | hex_string

Named and Unnamed Parameters

Parameters can be named or unnamed, but mixing of named and unnamed parameters is not allowed. Dynamic parameters can also be named using CallableStatement methods introduced with JDBC 3.0. Literals can only be named using the syntax provided above. All Ingres database procedure parameters are named.

If parameter names are not provided to the Ingres JDBC Driver, the driver must query the database and assign names to the parameters based on the declared order of the procedure parameters. Because querying the database reduces the performance of database procedure execution, using named parameters in your applications is strongly encouraged.

The Ingres JDBC Driver provides support for parameter default values by allowing parameter values to be omitted. This support is intended primarily for ODBC positional parameters. For Ingres named parameters, default values can be used simply by omitting the parameter entirely.

Additional Parameter Considerations

Ingres supports the parameter attributes IN, OUT, and INOUT when creating database procedures. When invoking a database procedure, the Ingres JDBC Driver marks a parameter as IN when an input value is set using a CallableStatement.setXXX() method. Registering a parameter for output using a CallableStatement registerOutParameter() method will mark the parameter as OUT. Setting a value and registering for output will mark a parameter as INOUT. All dynamic parameters must have an input value assigned and/or be registered for output prior to executing the procedure.

Ingres database procedure parameters can also be passed by value or reference when not explicitly marked with IN, OUT, or INOUT attributes. The Ingres JDBC Driver treats parameters passed by value as IN parameters, and parameters passed by reference (BYREF) as INOUT parameters. If an input value is not provided for a parameter registered for output, the driver sends a NULL value of the output type registered for that parameter.

Ingres Global Temporary Table procedure parameters are specified by providing a parameter value in the form session.table_name. In this parameter, table_name is the name of the Global Temporary Table, and 'session.' identifies the parameter as a Global Temporary Table parameter.

Executing Procedures

The CallableStatement methods executeQuery() and execute() can be used to execute a row-producing procedure. The methods executeUpdate() and execute() can be used for non-row-producing procedures. Ingres does not permit output parameters with procedures that return rows.

Procedure return values, output parameter values and rows returned by row-producing procedures are accessed by standard JDBC methods and interfaces. The CallableStatement getXXX() methods are used to retrieve procedure return and output parameter values. Rows returned by a procedure are accessed using the ResultSet returned by the CallableStatement getResultSet() method.

Ingres database procedures permit the use of the transaction statements COMMIT and ROLLBACK, however, the use of these statements is highly discouraged!

Using these statements in a procedure executed by the Ingres JDBC Driver can result in the unintentional commitment or rollback of work done prior to procedure execution. It is also possible that a change in transaction state during procedure execution can be interpreted as a transaction abort. For these reasons, applications must make sure that no transaction is active prior to executing a database procedure that contains COMMIT or ROLLBACK statements.

BLOB Column Handling

The presence of a BLOB column in a result set places limitations on the Ingres JDBC Driver and JDBC application. BLOB data is serialized with the rest of the result set when returned by the DBMS Server. The length of a BLOB is not known beforehand, and the Ingres JDBC Driver does not attempt to buffer BLOB data. As a result, a BLOB column must be accessed and processed prior to accessing any data that follow the BLOB.

A result set containing a BLOB column does not permit READONLY cursor pre-fetch. Only one row of a BLOB result set is retrieved with each DBMS Server access. While this does not directly affect the JDBC application, performance is reduced when a result set contains a BLOB column.

A BLOB column can be accessed only once. Because BLOB data is not buffered, only one call (to getString(), getCharacterStream(), etc.) can be made for each BLOB column in each row of the result set. A request to access a BLOB column after it has already been accessed generates an SQLException indicating that the BLOB data is no longer available.

A BLOB column must be accessed and read completely prior to accessing any column that follows the BLOB in the result set. When a column is accessed which follows an unaccessed BLOB, the BLOB data must be read and discarded so that the data for the requested column can be returned. If an attempt is made to access the BLOB column, an SQLException is generated indicating that the BLOB data is no longer available.

BLOB data must be read fully before making any further request on the connection. Because data from the DBMS Server is serialized on the connection, the results from additional requests on the connection are queued behind any unread BLOB data. The Ingres JDBC Driver avoids conflicts resulting from multiple simultaneous requests on a connection by locking the connection for the duration of each request.

When a BLOB column is present in a result set, the connection is not unlocked until all the data in a row, including the BLOB data, has been read. An attempt to make an additional request on a connection when a BLOB column has not been read completely generates an SQLException indicating that a request was made before the prior request had completed.

In general, the following recommendation from the Sun JDBC documentation must be followed: "For maximum portability, columns within a row must be read in left-to-right order, and each column must only be read once. This reflects implementation limitations in some underlying database protocols."

Date/Time Columns and Values

The Ingres DBMS uses the timezone and date format of the client to perform various types of processing of data values. By default, the Ingres JDBC Driver uses the Java/JDBC conventions for dates by setting the client timezone to GMT and the date format to match that specified by JDBC. When using these settings, the Ingres JDBC Driver manipulates date/time values to match the requirements of both the DBMS and JDBC.

Because the DBMS does not have the actual client timezone, the following restrictions exist:

• Ingres date literal formats are not supported. JDBC specifies the format for date, time, and timestamp literals using the following escape clause syntax:

  Literal Syntax

  date {d 'yyyy-mm-dd'}

  time {t 'hh:mm:ss'}

  timestamp {ts 'yyyy-mm-dd hh:mm:ss.f...'}

• These escape clauses must be used to include date, time, and timestamp literals in SQL text. Applications can use other date/time formats by using the classes java.sql.Date, java.sql.Time, java.sql.Timestamp, and java.util.date with an appropriately configured date formatter (java.text.DateFormat).
• Ingres specific date processing, such as intervals and date functions, causes problems associated with the difference between GMT and the actual client timezone and must be avoided.

The Ingres JDBC Driver allows the Ingres timezone and date format to be passed to the DBMS. For more information, see JDBC Driver Properties. When these property values are provided, all Ingres date processing is supported in addition to the JDBC functionality listed above. Note that the Ingres timezone provided must correspond to the Java client default timezone. Using an arbitrary timezone results in time values that differ by the relative timezone offsets.

The Ingres JDBC Driver supports Ingres empty dates ('') by returning the JDBC date/time epoch values ('1970-01-01','00:00:00') for methods getDate(), getTime() and getTimestamp() and a zero-length string for getString(). In addition, a DataTruncation warning is created by the driver when an empty date is returned by any of these methods. An application checks for the warning by calling the getWarnings() method after calling one of the previously mentioned methods. An Ingres empty date is different than a NULL value, and cannot be detected using the wasNull() method.

A DataTruncation warning is also created for Ingres date-only values (no time component) for the same conditions described for empty dates. While an Ingres date-only value is comparable to a JDBC DATE value, Ingres date columns are described as being JDBC TIMESTAMP types and date-only values are technically a truncation of that type.

Ingres interval values are not supported by the methods getDate(), getTime(), and getTimestamp(). An exception is thrown if an Ingres date column containing an interval value is accessed using these methods. Ingres interval values can be retrieved using the getString() method. Because the output of getString() for an interval value is not in a standard JDBC date/time format, the Ingres JDBC Driver creates a warning that can be checked by calling the getWarnings() method following the call to getString().

National Character Set Columns

The Ingres JDBC Driver supports the Ingres data types of nchar, nvarchar, and long nvarchar. Retrieval of National Character Set values is done transparently through the existing getXXX() ResultSet methods.

When using character parameters for a PreparedStatement, the data type sent by the driver is determined by the JDBC methods used to assign the parameter value, and the data types supported by the target database.

The JDBC parameter methods and resulting Ingres parameter data type for both standard and National Character Set databases are as follows:

Note: The driver's use of National Character Set parameters can be overridden using the JDBC SQL type of OTHER in the setObject() method.