db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: Java stored procedure performing insert/update/delete
Date Thu, 01 Aug 2013 21:36:46 GMT
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.


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