db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-1364) Client driver does not roll back the effects of a stored procedure when incorrectly invoked by executeQuery()/executeUpdate()
Date Sat, 03 Jun 2006 12:35:30 GMT
    [ http://issues.apache.org/jira/browse/DERBY-1364?page=comments#action_12414572 ] 

Knut Anders Hatlen commented on DERBY-1364:
-------------------------------------------

My previous analysis of the problem wasn't entirely correct. Here's a
new attempt (hopefully correct this time):

Commit/rollback behaviour when using executeQuery() to call a stored
procedure:

Auto-commit enabled, no ResultSet returned from the stored procedure:

  * Embedded: The statements executed from the stored procedure are
    always rolled back.

  * Client: The statements executed from the stored procedure are
    always committed.

Auto-commit enabled, more than one Result Set returned:

  * Embedded: The statements executed from the stored procedure are
    always rolled back.

  * Client: If Connection.rollback() is called before the statement or
    connection is closed, the statements executed from the stored
    procedure are rolled back. If the statement or connection is
    closed, or Connection.commit() is called, the statements executed
    from the stored procedure are committed. If the connection dies
    before any of commit(), rollback() or close() have been called,
    the statements in the stored procedure are rolled back.

Auto-commit disabled, no ResultSet or more than one ResultSet returned
from the stored procedure:

  * Embedded and client: If commit() is called, the entire transaction
    is committed, including statements executed from the stored
    procedure in the failing call to executeQuery(). If rollback() is
    called, the entire transaction is rolled back.

When using executeUpdate() to call a stored procedure which returns
one or more ResultSets, the embedded driver does not raise an
exception. The client driver does that, and it has the same
commit/rollback behaviour as executeQuery() when the stored procedure
returns more than one ResultSet.



Since the behaviour is the same for embedded and client when
auto-commit is disabled, none of the complex solutions described in my
previous comment are needed. We only need to make sure the transaction
is rolled back when auto-commit is enabled.

> Client driver does not roll back the effects of a stored procedure when incorrectly invoked
by executeQuery()/executeUpdate()
> -----------------------------------------------------------------------------------------------------------------------------
>
>          Key: DERBY-1364
>          URL: http://issues.apache.org/jira/browse/DERBY-1364
>      Project: Derby
>         Type: Bug

>   Components: JDBC, Network Client
>     Versions: 10.1.2.1, 10.2.0.0
>     Reporter: Knut Anders Hatlen
>     Priority: Minor
>      Fix For: 10.2.0.0

>
> When executing a stored procedure with executeQuery() and the proc
> doesn't return exactly one result set, the query should fail and the
> statement should be rolled back. Embedded does this correctly.
> The client driver, however, does not roll back the statement, so the
> effects of statements executed on nested connections in the stored
> procedure are still visible. The reason for this is that the network
> server executes the statement with execute() instead of
> executeQuery(), so that it succeeds on the server. The client then
> counts the number of result sets, and raises an exception if it is not
> one, but it does not roll back the effects of the stored procedure.
> verified that.)
> The same problem exists for executeUpdate(). JCC also has these
> problems.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


Mime
View raw message