• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Devaka Cooray
  • Liutauras Vilda
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Paul Clapham
  • Knute Snortum
  • Rob Spoor
Saloon Keepers:
  • Tim Moores
  • Ron McLeod
  • Piet Souris
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • Tim Holloway
  • Frits Walraven
  • Ganesh Patekar

PL/SQL and JDBC

 
Ranch Hand
Posts: 55
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 161
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:db2:DSNT ..." );
userid = "USER_ID";
password = "PASSWORD";
Connection con =
DriverManager.getConnection("jdbc:db2:DSNT", 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
 
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I feel that you should use
storedProc.execute();
instead of storedProc.executeUpdate();
Regards
Ram
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!