[
http://issues.apache.org/jira/browse/DERBY-1364?page=comments#action_12414209 ]
Knut Anders Hatlen commented on DERBY-1364:
-------------------------------------------
The simple solution would be to trap the exception on the client and
roll back. The problem with this is that it would roll back the entire
transaction, not only the failing statement as the embedded driver
does.
I see these three ways to fix it:
1) Accept that the entire transaction is rolled back. Since the
exception indicates that something is wrong with the application
(should not use executeQuery()/executeUpdate()) or with the
stored procedure (should return the correct number of result
sets), I don't think it does very much harm. The application will
probably not try to commit the transaction anyway.
2) Let the client set a savepoint before executing the query, and
roll back to the savepoint if an incorrect number of results is
returned. This has the downside of adding complexity to the code
and extra round trips between the client and the server.
3) Make the network server execute the query with executeQuery() or
executeUpdate() instead of execute() in these cases. This way,
the error will happen at the server and the embedded driver will
ensure that the statement is rolled back correctly. DRDA has no
direct support for this, but I think we could use a SET statement
(something like what was done in DERBY-506).
Does anyone have opinions on these alternatives? Or other suggestions?
Thanks!
> 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.2.0.0, 10.1.2.1
> 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
