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 14:56:21 GMT
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 

Note that the solution which I suggested ought to be portable across all 
JDBC databases.


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