#1419 executeUpdate does not return the correct number
Hi,
I found a bug during the execution of executeUpdate() of CallableStatement.
The return value of executeUpdate is equal of the number of row inserted/deleted/updated as written in
http://hsqldb.org/doc/src/org/hsqldb/jdbc/JDBCPreparedStatement.html#executeUpdate()
I created a small test - the attached file - that creates a very simple scenario:
a table with 2 columns, 4 store procedure for each operation and a test that:
1) Insert a tuple (with data = value), select that tuple
2) Update that tuple (with data = value2), select it
3) Delete it, select it (result = null).
As result of the test, the tuple is inserted, updated, selected and deleted correctly but the value of RowCount is always equal to ZERO!
For this test I used the same types I used in my code to find this bug: CallableStatement, HikariDataSource.
Execution Screenshot
This is not a bug. You are looking at the JavaDoc for PreparedStatement but you are using CallableStatement. If you execute the INSERT, UPDATE or DELETE without a procedure, a positive update count is returned. A procedure call always returns 0.
First of all: CallableStatement implements PreparedStatement.
In http://hsqldb.org/doc/src/org/hsqldb/jdbc/JDBCCallableStatement.html in the section
Methods inherited from class org.hsqldb.jdbc.JDBCPreparedStatement there is executeUpdate() this means that CallableStatement inherits executeUpdate() from PreparedStatement.
I tested my project with 2 Databases: SQLServer and MySQL. I'm using HSQLDB for UTC. The only one that always return 0 is HSQLDB.
Does not matter if it is a stored procedure, a function or a normal sql statement, executeUpdate() has to return the number of row edited/inserted/deleted during the entire execution.
This is a bug.
Inherited methods do not necessarily return the same values as the parent.
A procedure can update more than one table, therefore there is not much point returning an update count. You can return values (including number of updated rows) from procedures using its OUT parameters.
Last edit: Fred Toussi 2015-10-01
Inherited methods can return different values as the parent, if the child class override the method.
In this case the method is not override by child class, so has to return the same result.
In software engineering, we cannot change the code to fix UTC (Unit Test Case).
If you don't want to fix this bug, I should suggest you to update the JavaDoc of HSQLDB, in this way match with your code otherwise it is a paste and copy from Oracle (http://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html#executeUpdate()).
When methods are not static their return values can reflect other changes in the subclass. With JDBC there is a lot of implementation-defined behaviour and this is one of them. Different JDBC drivers do not work the same way. The doc says row count is returned for DML statements (INSERT/UPDATE/DELETE) and 0 for statements that return nothing. Your statements are CALL statements which return nothing directly.
You can always run your unit tests against the target database engine and get the results you expect.
Last edit: Fred Toussi 2015-10-01