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. A 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 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 are as follows:

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

  • You cannot use the SELECT * statement for queries. 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 are as follows:

  • You cannot use the SELECT * statement for queries. 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, a query may generate a scroll-sensitive or updatable result set if you can add the ROWID column to the SELECT list without causing any exceptions.

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 are 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 are 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.