Limits and degradation rules

Some types of result sets are not applicable to some queries. If the result set type or concurrency type specified for a query is unavailable, the JDBC driver will follow some rules to choose the best feasible type for the query.

The actual result set type and concurrency type are determined when the statement is executed. If the required result set type or concurrency type is unavailable, the driver will issue SQLWarning on the statement object. The SQLWarning object contains the reason why the requested type is unavailable. Check the warning to verify whether you have received the result set of the requested type.

Result set limits

Queries on the result set are subject to the following limits. If a query fails to follow these guidelines, the JDBC driver will select an alternate result set type or concurrency type.

Limits on generation of updatable result sets:

• A query can select data only from a single table and cannot contain any join operations. In addition, to perform an insert operation, the query must select all non-nullable columns and all columns without default values.

• SELECT * cannot be used in a query.

  The query must select only table columns, and cannot select derived columns or aggregates, such as SUM or MAX of a group of columns.

Limits on generation of scroll-sensitive result sets:

• SELECT * cannot be used in a query.

• A query can select data only from a single table.

A scrollable and updatable result set cannot contain the Stream column. If a server must extract the Stream column, the read size is reduced to 1 and all columns after the Stream column are blocked, until the Stream column is read. As a result, columns cannot be obtained in batches and columns cannot be browsed by scrolling.

To use SELECT * in a query, you can use the table alias in the query, as shown in the following example:

SELECT tab.* FROM TABLE tab ...

You can use the following method to determine whether a query may generate a scroll-sensitive or updatable result set: If you can add the ROWID column to the query list without causing any exceptions, the query may generate a scroll-sensitive or updatable result set.

Result set degradation rules

If the specified result set type or concurrency type is unavailable, the OceanBase JDBC driver selects an alternate type based on the following rules:

• If the result set type specified is TYPE_SCROLL_SENSITIVE, but the JDBC driver cannot support this type, the driver will attempt to downgrade the type to TYPE_SCROLL_INSENSITIVE.

• If the result set type specified or degraded to is TYPE_SCROLL_INSENSITIVE, and the JDBC driver cannot satisfy the request, the driver will attempt to downgrade the type to TYPE_FORWARD_ONLY.

• If the concurrency type specified is CONCUR_UPDATABLE, and the JDBC driver cannot satisfy the request, the JDBC driver will attempt to downgrade the type to CONCUR_READ_ONLY.

Note

Any operation of the JDBC driver on the result set type is independent of that of the concurrency type.

After a query is executed, you can check the result set type and concurrency type actually used by the JDBC driver by calling the following methods on the result set object:

• int getType() throws SQLException

  This method returns the int value of the result set type for the query. Possible value: ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_SENSITIVE, or ResultSet.TYPE_SCROLL_INSENSITIVE.

• int getConcurrency() throws SQLException

  This method returns the int value of the concurrency type for the query. Possible value: ResultSet.CONCUR_READ_ONLY or ResultSet.CONCUR_UPDATABLE.

Avoid update conflicts

The JDBC driver is subject to the following limits on updatable result sets:

• The driver does not enforce write locking on updatable result sets.

• The driver does not check conflicts with DELETE and UPDATE operations on result sets.

If you attempt to execute a DELETE or UPDATE operation on rows of another committed transaction, a conflict will occur.

The OceanBase JDBC driver usesROWID to uniquely identify a row in the database table. The operation will be executed provided that ROWID is valid when the driver issues the UPDATE or DELETE operation to the database.

The driver will not report any changes made by other committed transactions. All conflicts will be ignored and your changes will overwrite previous changes.

To avoid such conflicts, we recommend that you use the FOR UPDATE feature when you query for generated result sets. This will avoid conflicts, but will also prevent simultaneous data access. A data item can retain only one write lock at a time.