Re: Java stored procedure performing insert/update/delete

[Rick Hillegas]

On 7/31/13 5:02 PM, 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

Hi Steve,

If all you need to do is pass the information back to the client 
somehow, then you could use an output parameter. The following code 
shows how to do this:

import java.sql.*;

public class w
{
    public  static  void    main( String... args ) throws Exception
    {
        Connection  conn = DriverManager.getConnection( 
"jdbc:derby:memory:db;create=true" );

        conn.prepareStatement( "create table t_user( userName varchar( 
50 ) )" ).execute();
        conn.prepareStatement( "insert into t_user values ( 'fred' ), ( 
'wilma' ), ( 'pebbles' )" ).execute();
        conn.prepareStatement
            (
             "create procedure deleteAllUsers( out updateCount int )\n" +
             "language java parameter style java modifies sql data\n" +
             "external name 'w.deleteAllUsers'\n"
             ).execute();

        CallableStatement   cs = conn.prepareCall( "call 
deleteAllUsers( ? )" );
        cs.registerOutParameter( 1, Types.INTEGER );
        cs.execute();

        System.out.println( "Deleted " + cs.getInt( 1 ) + " rows." );
    }

    public static void deleteAllUsers( int[] updateCount )
        throws SQLException
    {
        Connection conn = DriverManager.getConnection( 
"jdbc:default:connection" );
        PreparedStatement ps = conn.prepareStatement( "delete from 
t_user" );
        updateCount[ 0 ] = ps.executeUpdate();
        ps.close();
        conn.close();
    }
}

Hope this helps,
-Rick