Limitations

Some types of result sets are not applicable to some queries. If an unavailable result set type or concurrency type is specified for a query, OceanBase Connector/J will follow some rules to choose the best feasible result set 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 alerts to verify whether you have received the result set of the requested type.

Result set limitations

Queries on the result set are subject to the following limitations. If a query fails to follow these guidelines, OceanBase Connector/J will select an alternate result set type or concurrency type.

• Limitations on generating 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. You can use table aliases in a query, as shown in the following example:

    SELECT tab.* FROM TABLE tab ...
    

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

• Limitations on generating scroll-sensitive result sets:

  • SELECT * cannot be used in a query. You can use table aliases in a query, as shown in the following example:

    SELECT tab.* FROM TABLE tab ...
    

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

In Oracle mode, 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.

Verify the result set type

After a query is executed, you can check the result set type and concurrency type actually used by OceanBase Connector/J 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 values: ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_SENSITIVE, and ResultSet.TYPE_SCROLL_INSENSITIVE.

• int getConcurrency() throws SQLException

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

Avoid update conflicts

Note the following limitations 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.

In MySQL mode, a row is uniquely identified by the primary key in the result set. If no primary key is available, the row cannot be updated. When the driver issues an UPDATE or DELETE operation to the database, the operation will be executed provided that the primary key is valid.

In Oracle mode, ROWID is used to uniquely identify a row in a database table. When the driver issues an UPDATE or DELETE operation to the database, the operation will be executed provided that ROWID is valid.

The driver will not report any changes made by other committed transactions 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. However, it will also prevent simultaneous data access. A data item can retain only one write lock at a time.