Hi Steve,
Thanks for explaining your reasoning in detail. I do think that the spec
is ambiguous on this topic and other opinions are possible. It sounds as
though you are writing a portable piece of code which you expect to
deploy against many JDBC databases. If you discover a clear consensus
among those databases, then we can take your evidence to the JDBC expert
group and ask them to clarify the spec. That would give us a powerful
argument for changing Derby's behavior.
Thanks,
-Rick
On 8/1/13 11:15 AM, Steve Ebersole wrote:
Well I have not looked at the spec itself (the pdf) for this part tbh,
I am just going off of javadocs. Also, I guess maybe some of my
opinion is based as a quite heavy user of JDBC and knowing what is
actually useful :)
All that said, I think you have to look at executeUpdate() and
getUpdateCount() in similar light. Hopefully we can agree on that.
Both are inherited from PreparedStatement/Statement. Whether the
inheritence in these JDBC statement classes is a good decision we'll
leave for another discussion :)
Looking at
http://docs.oracle.com/javase/6/docs/api/java/sql/PreparedStatement.html#executeUpdate(),
we see that it "Executes the SQL statement in this PreparedStatement
object, which must be an SQL Data Manipulation Language (DML)
statement, such as INSERT, UPDATE or DELETE; or an SQL statement that
returns nothing, such as a DDL statement.". It goes on to say that
the expected return is "either (1) the row count for SQL Data
Manipulation Language (DML) statements or (2) 0 for SQL statements
that return nothing". "return nothing" is not the same as returning
an empty result set, I hope we can agree. Its the difference between
returning null and having a void return type. So overall, the return
here is expected to be either (1) the number of rows affected
(mutated, modified, whatever term you like) or (2) 0 for any
statements which "return nothing" (void, not null/empty)
The javadocs for executeUpdate do not discuss -1 as a result. It
expects a SQLException for statements that return ResultSets, rather
than returing -1. Personally I avoid executeUpdate for this reason
and use execute() since my work is more general purpose.
For getUpdateCount() we have to look at
http://docs.oracle.com/javase/6/docs/api/java/sql/Statement.html#getUpdateCount
which says "Retrieves the current result as an update count; if the
result is a ResultSet object or there are no more results, -1 is
returned. This method should be called only once per result.". Honestly
As for my usability comment, at the end of the day users of this API
need to be able to access the sum total of all "outputs" of the
procedure being called. That includes one or more ResultSets, one or
more updateCounts (or a single overall updateCount depending on your
reading) and one or more INOUT/OUT parameters. The docs anyway are
pretty clear that as API users we can expect we are done processing
all non-parameter outputs when `((stmt.getMoreResults() == false) &&
(stmt.getUpdateCount() == -1))` evaluates to true [1]. Going back to
my original question, the testing procedure clearly is not returning
results [stmt.getMoreResults()==false]. As the procedure is doing
"SQL Data Manipulation Language (DML) statements", I fully expect to
next query stmt.getUpdateCount() to be able to get the number of rows
affected.
To be quite honest, I do not even begin to see the argument (from a
usability perspective) for stmt.getUpdateCount() in my case to return
0. Maybe there is something in the actual spec PDF that lends to that
argument, but like I said that really just kills the usability of this
API.
[1] See
docs.oracle.com/javase/6/docs/api/java/sql/Statement.html#getMoreResults
On Thu 01 Aug 2013 12:03:52 PM CDT, Rick Hillegas wrote:
On 8/1/13 8:27 AM, Steve Ebersole wrote:
As far as I could tell, the embedded driver was always returning
zero. That happens from both executeUpdate() and getUpdateCount().
Now its very possible that I did not code the stored procedure
properly in terms of how Derby expects to "see" the update count. I
followed examples, but maybe I missed something.
And the options of using a function nor an (IN)OUT parameter is not
an option unfortunately.
I am trying to spin up unit tests here, so have only used in-process
dbs thus far for testing this. Specifically H2 and then Derby.
Neither returned what I expect based on JDBC. Thomas agrees H2 does
not do this properly.
In terms of expectation, the execute() call should of course execute
the proc. Since this proc does not return results, execute() should
return false. Then getUpdateCount() ought to return the number of
rows "modified" by the call, or -1 if no modification was done. 0
and -1 are distinctly different according to the spec. I hate "magic
values" as much as anyone, but the spec says what it says ;)
Hi Steve,
Can you point me at the sections of the JDBC spec which you feel
support this interpretation? I am concerned that this part of the spec
may be undefined and I am seeing a range of opinions, including:
1) Should return -1 because a procedure call is not an
INSERT/UPDATE/DELETE statement.
2) Should return the update count of the last INSERT/UPDATE/DELETE
statement executed by the procedure.
3) Should return the sum of the update counts of all
INSERT/UPDATE/DELETE statements executed by the procedure.
Thanks,
-Rick
On Thu 01 Aug 2013 09:56:21 AM CDT, Rick Hillegas wrote:
Hi Steve,
I'm not sure that DERBY-211 addresses the functionality you want. That
issue seems to me to be a discussion about whether 0 or -1 is the
correct value for CallableStatement.getUpdateCount() when the stored
procedure does NOT return any ResultSets. It sounds as though you want
getUpdateCount() to return a positive number in that case, maybe the
sum of the update counts for all INSERT, UPDATE, and DELETE statements
executed inside the procedure.
It also sounds as though you are experimenting with other databases
for which CallableStatement.getUpdateCount() returns a positive
number. Can you describe the behavior of the other databases? I would
like to take your evidence to the JDBC expert group in order to
determine what the correct behavior should be. As a result, we may
need to open a new Derby issue.
Note that the solution which I suggested ought to be portable across
all JDBC databases.
Thanks,
-Rick
On 8/1/13 7:08 AM, Steve Ebersole wrote:
Dag, Rick.. thanks for your replies. I am not subscribed to the
list, but saw them in archive.
Unfortunately a workaround won't work. This is support for stored
procedures across all databases that I am adding in Hibernate. So
there has to be a certain level of consistency. I'll just add a note
that stored procedures doing manipulation will not work correctly
with Derby (in terms of getting the "affect rows" count) when used in
embedded mode and reference to DERBY-211.
On Thu 01 Aug 2013 09:02:15 AM CDT, Steve Ebersole wrote:
Looks like I am seeing
https://issues.apache.org/jira/browse/DERBY-211
On Wed 31 Jul 2013 07:02:01 PM CDT, Steve Ebersole wrote:
I am trying to work out how to define a Java stored procedure using
Derby that performs a insert/update/delete and results in the
proper
"update count" on the JDBC client. But I have so far been
unsuccessful.
Here is what I have...
First, through JDBC I execute:
create procedure deleteAllUsers()
language java
external name 'TheClass.deleteAllUsers'
parameter style java
TheClass.deleteAllUsers looks like:
public static void deleteAllUsers() {
Connection conn = DriverManager.getConnection(
"jdbc:default:connection" );
PreparedStatement ps = conn.prepareStatement( "delete from
t_user" );
int count = ps.executeUpdate();
System.out.println( "Count : " + count );
ps.close();
conn.close();
}
And on the JDBC client side:
Connection conn = ...;
CallableStatement stmnt = conn.prepareCall( "{call
deleteAllUsers()}" );
// yes I know this could be stmnt.executeUpdate()...
stmnt.execute();
int count = stmnt.getUpdateCount();
So the deleteAllUsers() prints the correct count. But on the
client,
I always get zero (and not -1).
Obviously I am doing something wrong. Any pointers?
Thanks,
Steve
