Confused by Stored Procedure Error

[Donna Reschke]

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]

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]

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.

//CallableStatement csTuition = conn.prepareCall("{CALL sp_studentFeesBilled(?,?,?,?,?)}");
csTuition.registerOutParameter(1, Types.VARCHAR);
csTuition.registerOutParameter(2, Types.VARCHAR);
csTuition.setString(3, learner_id.substring(5,13));
csTuition.setString(4, start.substring(2,6));
csTuition.setString(5, end.substring(2,6));
csTuition.execute();
String tuition = csTuition.getString(1).trim();
String other_costs = csTuition.getString(2).trim();

the stored procedure was:

CREATE OR REPLACE PROCEDURE sp_studentFeesBilled(sum_tuition OUT VARCHAR2,
  sum_other OUT VARCHAR2, learner_id VARCHAR2, start_date VARCHAR2, end_date  VARCHAR2)
AS
BEGIN
SELECT TO_CHAR(NVL(SUM(sop_invoice_total_currency + line_tax),0),'999990.99')
INTO sum_tuition
FROM sales_order_invoice_lines@dblink_finance
WHERE customer_number = learner_id
AND (part_code like '%1_' OR part_code like '%01')
AND substr(part_code, 8, 4) < end_date
AND substr(part_code, 8, 4) >= start_date;

SELECT TO_CHAR(NVL(SUM(sop_invoice_total_currency + line_tax),0),'999990.99')
INTO sum_other
FROM sales_order_invoice_lines@dblink_finance
WHERE customer_number = learner_id
AND (part_code like '%02' OR part_code like '%03'
 OR part_code like '%04' OR part_code like '%05'
 OR part_code like '%06' OR part_code like '%07'
 OR part_code like '%08' OR part_code like '%09')
AND substr(part_code, 8, 4) < end_date
AND substr(part_code, 8, 4) >= start_date;
END sp_studentFeesBilled;

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]

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