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 12:41:56 GMT
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

Mime
View raw message