Re: Java stored procedure performing insert/update/delete

[Rick Hillegas]

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