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 17:03:52 GMT
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.

> 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