jQuery in Action, 2nd edition*
The moose likes Java in General and the fly likes stored procedure giving me sql error Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Java in General
Bookmark "stored procedure giving me sql error" Watch "stored procedure giving me sql error" New topic
Author

stored procedure giving me sql error

Donna Reschke
Greenhorn

Joined: Jul 05, 2001
Posts: 23
I'm trying to call a stored procedure on a DB2 database from a Java Servlet. This is my code:
try{
cstmt = con.prepareCall("{CALL MKTDS80A
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
cstmt.setShort(1, shFiscalYr);
cstmt.setInt(2, iInvoiceNbr);
cstmt.setString(3, sInvoiceTypeCd);
cstmt.setInt(4, iUserNbr);
cstmt.setString(5, sFormId);
cstmt.setString(6, sSubSysCd);
cstmt.setShort(7, shModNbr);
cstmt.registerOutParameter(8, Types.INTEGER);
cstmt.registerOutParameter(9, Types.INTEGER);
cstmt.registerOutParameter(10, Types.CHAR);
cstmt.registerOutParameter(11, Types.INTEGER);
cstmt.registerOutParameter(12, Types.CHAR);
cstmt.execute();

iParm1 = cstmt.getInt(8);
iParm2 = cstmt.getInt(9);
sParm3 = cstmt.getString(10);
iParm4 = cstmt.getInt(11);
sParm5 = cstmt.getString(12);
if (iParm1 == 0){ bReturnZero = true;
}
CloseSQLStatement(cstmt);
}
catch (SQLException ex) {
CloseSQLStatement(cstmt);
bReturnZero = false;
System.out.println("SQL exception occurred: "
+ ex.toString());
return iParm1;
}
It seems to fail on the execute and I get the following SQL Exception message:
SQL exception occurred in callStoredProcedure method:
COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2] SQL0440N
No function by the name "MKTDS22B" having compatible arguments was
found in the function path. SQLSTATE=42884
Can anyone tell me what the problem might be? I've checked the parameter datatypes and everything matches up. Does anyone have a clue?
Thanks in advance!
Donna Reschke
Greenhorn

Joined: Jul 05, 2001
Posts: 23
Sorry, I included the wrong error message in my previous post. This is the correct one...
SQL exception occurred in callStoredProcedure method:
COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2] SQL0440N
No function by the name "MKTDS80A" having compatible arguments was
found in the function path. SQLSTATE=42884

Please help!
Anonymous
Ranch Hand

Joined: Nov 22, 2008
Posts: 18944
I don't know if this will help you, I have no experience using DB2. I have however some experience working with Oracle.
Could the reason be that the procedure isn't visible for the user/password combo you're using when connecting via jdbc?
Maybe the procedure is in another schema (maybe Oracle specific term) so you need to set up a synonym and grant priviliges for the user that is trying to execute the procedure via the servlet.
Carl Trusiak
Sheriff

Joined: Jun 13, 2000
Posts: 3340
What is the signiture of the Stored Procedure? It looks like you are trying to set SQL Types in your registerOutParameter that are incompatable with the stored procedure.


I Hope This Helps
Carl Trusiak, SCJP2, SCWCD
 
Consider Paul's rocket mass heater.
 
subject: stored procedure giving me sql error