Meaningless Drivel is fun!*
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: stored procedure giving me sql error
 
Similar Threads
Comparing two Dates
Confused by stored procedure sql error
Confused by Stored Procedure Error
Calling COBOL stored proc from Java Servlet
Problem in Two-phase Commit