aspose file tools*
The moose likes JDBC and the fly likes Confused by 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 "Confused by Stored Procedure Error" Watch "Confused by Stored Procedure Error" New topic
Author

Confused by Stored Procedure 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!
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

I think since CallableStatements are invoked differently depending on the db vendor, you may have to refer to your JDBC driver documentation. I will include how I went about invoking a stored procedure from java to Oracle in the hopes it might help.

the stored procedure was:

just use it to see if you can modify it to work on the DB2 platform. Also, I read somewhere that you may have to declare the outparameters first. Give it a try.
Jamie
**NOTE: EDITED CODE (BOTH STORED PROCEDURE AND JAVA CODE) - I posted unmatching code!
[ February 15, 2002: Message edited by: Jamie Robertson ]
[ February 15, 2002: Message edited by: Jamie Robertson ]
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

Donna:
I believe that you should register the out parameters first. Some drivers/db's are more flexible than others, but I have seen some that won't work if the out parameters are not declared first.
Jamie
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Confused by Stored Procedure Error