Generating calls to stored procedures in autocommit mode

[Øystein Grøvlen <Oy...@Sun.COM>]

In the current work to use locators for LOBs on client, we have defined 
a set of stored procedures that will be called to operate on the LOBs at 
the server (ref. DERBY-208).

Testing my experimental code for locator support, shows that my 
implementation has problems with auto-commit mode.  One scenario is a 
follows:

   1. Client sends "select <blob-column> from <table>" to server
   2. Server executes queries, sends reply
   3. Client does ResultSet.next() to get the first row of result set.
   4. Server create locator to blob mapping and sends back locator.
   5. Client calls stored procedures to get (part of) the blob value.

If auto-commit is on, step 5 will cause the current transaction to 
commit.  Since locators are only supposed to be valid for the duration 
of the transaction, this results in that the locator to blob mapping is 
cleared, and when the stored procedure is executed, the specified 
locator is not found.  Hence, we want step 5 to execute in the same 
transaction as the previous steps.

I have looked around in the client trying to find whether there are 
other mechanism with similar needs, but I have not found anything yet. 
Can anyone point me to existing code where the client execute 
sql-statements as a side effect of user operations.  (Maybe there are 
similar issus when SQL is executed for metadata calls?)   Pointers and 
advice will be highly appreciated.

--
Øystein

Re: Generating calls to stored procedures in autocommit mode

[Oystein Grovlen - Sun Norway <Oy...@Sun.COM>]

Øystein Grøvlen wrote:
> I have looked around in the client trying to find whether there are 
> other mechanism with similar needs, but I have not found anything yet. 
> Can anyone point me to existing code where the client execute 
> sql-statements as a side effect of user operations.  (Maybe there are 
> similar issus when SQL is executed for metadata calls?)   Pointers and 
> advice will be highly appreciated.

I think I have found the solution to my problems.  The Statement class 
has the following member field:

     // When this is false we skip autocommit for this PreparedStatement.
     // This is needed when the PreparedStatement object is used 
internally by
     // the driver and a commit is not desired, e.g., Blob/Clob API calls
     public boolean isAutoCommittableStatement_ = true;

This seems to be what I need.  Currently, isAutoCommittableStatement_ is 
true for all statements.  By setting it to false for prepared procedure 
calls, executing the calls does not initiate a commit.  I will update my 
locator framework patch to do so.  It is also probably a good idea to 
make this field package private.

-- 
Øystein