db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Steve Ebersole <st...@hibernate.org>
Subject Re: Java stored procedure performing insert/update/delete
Date Thu, 01 Aug 2013 18:15:33 GMT
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 

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 
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 

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 

[1] See 

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
> 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

View raw message