Win a copy of Reactive Streams in Java: Concurrency with RxJava, Reactor, and Akka Streams this week in the Reactive Progamming forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Junilu Lacar
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Knute Snortum
  • Tim Cooke
  • Devaka Cooray
Saloon Keepers:
  • Ron McLeod
  • Stephan van Hulst
  • Tim Moores
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Ganesh Patekar

Confused by Stored Procedure Error

 
Greenhorn
Posts: 23
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 23
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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!
 
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 know this defies the law of gravity... but I never studied law." -B. Bunny Defiant tiny ad:
Java file APIs (DOC, XLS, PDF, and many more)
https://products.aspose.com/total/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!