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 22:39:27 GMT
I think we both know that "clear consensus [between] databases" is 
probably not going to happen ;)

But after I build out the functional suite I'll report back what I find 
amongst the non-in-process dbs.  From what I can tell, even 
non-embedded Derby does this how I explained/expected..


On Thu 01 Aug 2013 04:36:46 PM CDT, Rick Hillegas wrote:
> 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
>>> 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
>>>>>>>> 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
>>>>>>>> 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
>>>>>>>> client,
>>>>>>>> I always get zero (and not -1).
>>>>>>>> Obviously I am doing something wrong.  Any pointers?
>>>>>>>> Thanks,
>>>>>>>> Steve

View raw message