aspose file tools*
The moose likes JDBC and the fly likes Stored Procedure Error Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Stored Procedure Error" Watch "Stored Procedure Error" New topic
Author

Stored Procedure Error

Pavan Kumar
Greenhorn

Joined: Jan 13, 2002
Posts: 8
Hi Have written follwoing code to call stored procedure. But I am getting error general SQL error.
here is the code.
public void connectGeneva(){

String url = "jdbc dbc:GENSYS";

Connection con;

try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
} catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
try{
con = DriverManager.getConnection(url,"geneva_admin", "geneva");
con.setAutoCommit(false);
System.out.println ("Connected Geneva .............");
Statement stmt = con.createStatement();

CallableStatement cs = con.prepareCall("{call GNVCUST.GETBILLINGDETAILS(?, ?, ?, ?, ?, ?) }");

cs.setString(1,"XXX");
cs.registerOutParameter(3, java.sql.Types.INTEGER);
cs.execute();
String CUSTOMERNAME = cs.getString(2);
int BILLPERIOD = cs.getInt(3);
System.out.println ("CUSTOMERNAME ............." + CUSTOMERNAME);
System.out.println ("BILLPERIOD ............." + BILLPERIOD);
}catch(SQLException ex) {
System.err.print("SQLException: ");
System.err.println(ex.getMessage());
}




}


pavan kuamr
Wayne L Johnson
Ranch Hand

Joined: Sep 03, 2003
Posts: 399
In your "prepareCall" you have a procedure call with six parameters. You then set one value (#1) and register another as an output parameter (#3). What about the other four parameters? If they are input parameters, you need to call a "setXXX()" method for them. If they are output parmeters you need to call "registerOutParmeter()" for them.
I notice that after the procedure call you are doing "cs.getString(2)", which indicates that parameter #2 is an output parameter, so you must call "registerOutParameter()" before doing the "cs.execute()". If parameters 4-6 are output parameters you need to call "registerOutParameter()" on them even if you don't plan on using the results. If they are input parameters, you need to set appropriate values first.
If doing this doesn't solve your problem, please post the exact error message with a traceback.
[ October 23, 2003: Message edited by: Wayne L Johnson ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Stored Procedure Error