Differences between client and embedded when invoking stored procedures using Statement.executeUpdate()

• Key: DERBY-1314
• Type: Bug
• Status: Closed
• Resolution: Fixed
• Priority: Major
• Assignee: Knut Anders Hatlen
• Reporter: Knut Anders Hatlen
• Component/s: JDBC
• Affects Version/s: 10.2.1.6
• Fix Version/s: 10.2.1.6

Description

When invoking a stored procedure using Statement.executeUpdate(), the
client driver and the embedded driver behave differently.

If the stored procedure does not return any result set, the embedded
driver returns 0, whereas the client returns -1.

If the stored procedure returns one or more result sets, the embedded
driver returns -1, whereas the client fails with an exception.

Comments

[Knut Anders Hatlen]

The JDBC 3.0 javadoc for Statement.executeUpdate() says:

  Returns: either the row count for INSERT, UPDATE or DELETE
    statements, or 0 for SQL statements that return nothing

  Throws: SQLException - if a database access error occurs or the
    given SQL statement produces a ResultSet object

Embedded behaves correctly when no result sets are produced by the
stored procedure, and the client behaves correctly when result sets
are produced.

[Knut Anders Hatlen]

The attached patch fixes the issue on the client. When executeUpdate() is used to invoke a stored procedure, an update count of -1 (indicating that no update count is returned) is changed to 0, as the javadoc for executeUpdate() says. With this patch, four more of the test cases in jdbcapi/ProcedureTest.junit succeed with the client driver (but the test will not be enabled until the patch for DERBY-501 is checked in).

executeUpdate() on the embedded driver will be fixed as part of DERBY-501.

Derbyall didn't show any failures with the patch. Please review. Thanks!

[Knut Anders Hatlen]

Proposal for release note (DERBY-501 and DERBY-1314):

The behaviour of executeQuery() and executeUpdate() has been modified
to follow the JDBC standard when executing stored procedures. For the
embedded driver, this means that

  a) it is now possible to use executeQuery() to execute a stored
     procedure that returns exactly one ResultSet (this would fail in
     previous releases of Derby)

  b) executeUpdate() will raise an exception if it is used to execute
     a stored procedure that returns one or more ResultSets (this
     would succeed in previous releases of Derby)

For the client driver, the following has changed:

  a) PreparedStatement.executeUpdate() will raise an exception if it
     is used to execute a stored procedure that returns one or more
     ResultSets (previously, it would succeed, whereas Statement and
     CallableStatement correctly raised an exception)

  b) executeUpdate() will return 0 when executing a stored procedure
     which doesn't return a ResultSet (previously it would return -1)

[Knut Anders Hatlen]

Does anyone have comments to the patch? If not, I plan to commit in a couple of days.

[Knut Anders Hatlen]

Committed revision 416696.

[Knut Anders Hatlen]

PROBLEM

The behaviour of executeQuery() and executeUpdate() did not match the
JDBC specification when invoking stored procedures.

SYMPTOMS

(1) When invoking a stored procedure with executeQuery() or
    executeUpdate(), an exception was thrown indicating that the
    procedure did not return the correct number of ResultSet objects,
    although the correct number of ResultSet objects was in fact
    returned.

(2) When invoking a stored procedure with executeQuery() or
    executeUpdate(), and the procedure did not return the correct
    number of ResultSet objects, the query executed successfully.

(3) With the network client driver, when invoking a stored procedure
    with executeUpdate(), the return value was -1, whereas the JDBC
    specification says it should be 0.

CAUSE

The methods executeQuery() and executeUpdate() were not implemented in
compliance with the JDBC specification.

SOLUTION

In Derby 10.2, the behaviour of the methods executeQuery() and
executeUpdate() has been changed to match the JDBC specification.

WORKAROUND

Use execute() instead of executeUpdate()/executeQuery() to invoke a
stored procedure which does not return exactly 0 or 1 ResultSet
objects.