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 15:27:22 GMT
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 ;)


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

Mime
View raw message