wood burning stoves 2.0*
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


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
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
 
Similar Threads
Comparing two Dates
stored procedure giving me sql error
Confused by stored procedure sql error
Calling COBOL stored proc from Java Servlet
how to pass a NULL parameter in prepareCall method.