PL/SQL and JDBC

[Ambrose Tati]

Dear all,
My ADD_REC stored procedure takes 2 varchar2 in parameters.
The following call raises exception.
...
CallableStatement storedProc =
con.prepareCall("{call ADD_REC(?,?)}");
storedProc.setString(1,"AAAA");
storedProc.setString(2,"BBBB");
storedProc.executeUpdate();
...
But if I make ADD_REC stored procedure parameterless
the following call works fine.
...
CallableStatement storedProc =
con.prepareCall("{call ADD_REC}");
storedProc.executeUpdate();
...
Can't we call PL/SQL stored procedures with parameters from within Java? Help please.
Thanks
Ambrose Tati

[Monty Ireland]

I have not tested this feature. But, it should work..
Enclosed you will find an application that exec a db2 stored procedure. For what it is worth... I hope it helps.
<code>
import java.sql.*;// java sql classes
public class JdbcTest {
public static void main(String args[]) {
try {
String userid, password;
String account, subgroup, segment, category, sector, level4;
String fromdate, todate, annind, incretind, rtntyp;
System.out.println("Loading JDBC Driver ..." );
Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");
System.out.println("Connecting To jdbc:db2SNT ..." );
userid = "USER_ID";
password = "PASSWORD";
Connection con =
DriverManager.getConnection("jdbc:db2SNT", userid, password );
System.out.println("Creating SQL Statement ..." );
String sqlsp =
"call SPPEF001 ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )";

System.out.println("Preparing Callable Statement JAVA SQL Stored Procedure ..." );
CallableStatement call_stmt = con.prepareCall( sqlsp );
System.out.println("Adding Criteria To JAVA SQL Stored Procedure ..." );
account = "USWF00370002";
subgroup = "000";
segment = "001";
category = "000";
sector = "000";
level4 = "000";
fromdate = "1993-07-31";
todate = "1998-07-31";
annind = "Y";
incretind = "N";
rtntyp = "TB";
call_stmt.setString( 1, account );
call_stmt.setString( 2, subgroup );
call_stmt.setString( 3, segment );
call_stmt.setString( 4, category );
call_stmt.setString( 5, sector );
call_stmt.setString( 6, level4 );
call_stmt.setString( 7, fromdate );
call_stmt.setString( 8, todate );
call_stmt.setString( 9, annind );
call_stmt.setString( 10, incretind );
call_stmt.setString( 11, rtntyp );
call_stmt.setDouble( 12, 0.0 );
System.out.println("Register Output With JAVA SQL Stored Procedure ..." );
call_stmt.registerOutParameter( 12, 8, 6);
System.out.println("Executing JAVA SQL Stored Procedure ..." );
ResultSet call_rs = call_stmt.executeQuery();
System.out.println("Getting Output From JAVA SQL Stored Procedure ..." );
double col1 = call_stmt.getDouble( 12 );
System.out.println("Rate Of Return Is " + col1);

}// end try block

catch(ClassNotFoundException e) {
System.out.println("ClassNotFoundException!!!: " + e);
}
catch(SQLWarning e) {
System.out.println("SQL_CODE !!!: " + e);
}
catch(SQLException e) {
System.out.println("SQL_CLASS !!: " + e);
}// end of catch block(s)

}// end of main
}// end of class JdbcTest
</code>
------------------
We learn more from our mistake's than from our success's.
a.k.a. monty6

[menon]

I feel that you should use
storedProc.execute();
instead of storedProc.executeUpdate();
Regards
Ram