Re: Java stored procedure performing insert/update/delete

[Dag H. Wanvik]

Hi,

please see answer inlined:
On 01/08/13 10:02, 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()}" );

Workaround: You could make deleteAllUsers into a function and return the 
value, and then do
PreparedStatement ps = conn.prepareStatement("values deleteAllUsers()");
ResultSet rs = ps.executeQuery();
rs.next();
int count = rs.getInt(1);

Dag


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