Subject Re: [Firebird-Java] disable TYPE_SCROLL_INSENSITIVE when ResultSet is HOLD_CURSORS_OVER_COMMIT
Author Mark Rotteveel
On 17-7-2013 20:14, mariuz wrote:
> This is a Question from Doru , he doesn't want the
> full caching in the client side (it will slow down his app)
> but he needs HOLD_CURSORS_OVER_COMMIT and TYPE_FORWARD_ONLY
>
> the code that it enables is this
>
> if (resultSetHoldability == FirebirdResultSet.HOLD_CURSORS_OVER_COMMIT &&
> resultSetType == ResultSet.TYPE_FORWARD_ONLY) {
>
> addWarning(new FBSQLWarning("Holdable result set must be scrollable."));
> resultSetType = ResultSet.TYPE_SCROLL_INSENSITIVE;
>
> Can this behavior be disabled or only by patching the jaybird ?

Unfortunately it can't be disabled. To hold the cursor over a commit, it
must be loaded completely on the client side, because the cursor on the
Firebird side is closed on commit and a call to ResultSet.next() and
fetch from server after the commit would result in errors.

Jaybird already does this loading for TYPE_SCROLL_INSENSITIVE, so we
change to a TYPE_SCROLL_INSENSITIVE. Changing a resultset type is
allowed per the JDBC 4.1 specification, section 15.1.1 (page 126):

"If the driver does not support the type supplied to the methods
createStatement, prepareStatement, or prepareCall, it generates an
SQLWarning on the Connection object that is creating the statement. When
the statement is executed, the driver returns a ResultSet object of a
type that most closely matches the requested type. An application can
find out the type of a ResultSet object by calling the method
ResultSet.getType."

According to section 15.1.3.1 Jaybird could decide to switch to a
CLOSE_CURSORS_AT_COMMIT type, but I think this is the lesser of evils.

The only options I can think of right now are to either eliminate the
need to hold the cursor over a commit (for example, eliminate the need
for the commit before your done with processing, or by using a separate
readonly connection for this query), or query less information to reduce
application slow down (eg use paging).

A TYPE_SCROLL_INSENSITIVE can be used exactly the same (it can even do
more) as a TYPE_FORWARD_ONLY, the only downside is the additional memory
requirements in the case of its implementation in Firebird/Jaybird.

Mark

--
Mark Rotteveel