Solved

JDBC with DB2/400 - Error when calling external routines

Posted on 2004-03-28
7
2,559 Views
Last Modified: 2011-09-20

Hi All:

I am using JDBC to connect to DB2/400 to call a Stored Procedure. Here is my AS400 code that works fine with no problems.

CREATE PROCEDURE  IJLIB/WEE1520IJ
(IN giftc# CHAR (19),            
 IN sbalal CHAR (11),            
 IN caltyp CHAR (2),            
 IN rspky CHAR (10),            
 IN wtime CHAR (3),              
 OUT DTA213 CHAR (213))          
 LANGUAGE SQL                    
 Modifies SQL DATA              
 NOT DETERMINISTIC              
                                 
 BEGIN                          
 set DTA213 = 'ABC';            
 END;

Now my actual requirement is to be able to call an external routine. So I made a few changes to the above code in that I removed the BEGIN..END block, changed LANGUAGE SQL to LANGUAGE RPGLE, and added the clause EXTERNAL NAME WER1520IJ, as shown below:

CREATE PROCEDURE  IJLIB/WEE1520IJ
(IN giftc# CHAR (19),            
 IN sbalal CHAR (11),            
 IN caltyp CHAR (2),            
 IN rspky CHAR (10),            
 IN wtime CHAR (3),              
 OUT DTA213 CHAR (213))          
 LANGUAGE RPGLE                    
 Modifies SQL DATA              
 NOT DETERMINISTIC  
 EXTERNAL NAME WER1520IJ
 Parameter Style SQL;

Now when I run my Java program I get the following error:

  Error in SP call: java.sql.SQLException: [SQL0440] Routine WEE1520 in RELIB not found with specified parameters.
  java.sql.SQLException: [SQL0440] Routine WEE1520 in RELIB not found with specified parameters.
        at com.ibm.as400.access.JDError.throwSQLException(JDError.java:594)
        at com.ibm.as400.access.JDError.throwSQLException(JDError.java:565)
        at com.ibm.as400.access.AS400JDBCStatement.commonPrepare(AS400JDBCStatement.java:1331)
        at com.ibm.as400.access.AS400JDBCPreparedStatement.<init>(AS400JDBCPreparedStatement.java:182)
        at com.ibm.as400.access.AS400JDBCCallableStatement.<init>(AS400JDBCCallableStatement.java:107)
        at com.ibm.as400.access.AS400JDBCConnection.prepareCall(AS400JDBCConnection.java:1582)
        at com.ibm.as400.access.AS400JDBCConnection.prepareCall(AS400JDBCConnection.java:1483)
        at callCommand2.main(callCommand2.java:82)

Does anyone have an idea what I might be missing?  I have added the libraries to my JDBC connection specification when opening it.

Thanks - Inayat
0
Comment
Question by:Inayat081501
7 Comments
 
LVL 33

Expert Comment

by:shalomc
ID: 10699714
You have put your stored procedure in IJLIB, but the JDBC connection's default library (the current library..) is RELIB.
You should indicate to JDBC that you want to use a library list rather than the current library,
or use a qualified procedure like
call  IJLIB.WEE1520IJ (....)

ShalomC
0
 

Author Comment

by:Inayat081501
ID: 10699873
Hi ShalomC:

Thanks for your response.

I am sorry for the confusion. Please ignore the error message I showed above. I messed up with the procedure that I actually called with the corresponding error message that I copied and pasted. Let me start afresh.

My AS400 Stored Procedure looks like this:

CREATE PROCEDURE IJLIB/PSE1210
(IN giftc# CHAR (19),          
 IN sbalal CHAR (11),          
 IN caltyp CHAR (2 ),          
 IN rspky CHAR (10 ),          
 IN wtime CHAR (3 ),          
 OUT DTA213 CHAR (213 ))      
LANGUAGE CL                    
NOT DETERMINISTIC              
MODIFIES SQL DATA              
EXTERNAL NAME ijlib/psc1210    
PARAMETER STYLE SQL;          

Here is the error message:

Error in SP call: java.sql.SQLException: [SQL0440] Routine PSC1210 in IJLIB not found with specified parameters.
java.sql.SQLException: [SQL0440] Routine PSC1210 in IJLIB not found with specified parameters.
        at com.ibm.as400.access.JDError.throwSQLException(JDError.java:594)
        at com.ibm.as400.access.JDError.throwSQLException(JDError.java:565)
        at com.ibm.as400.access.AS400JDBCStatement.commonExecute(AS400JDBCStatement.java:742)
        at com.ibm.as400.access.AS400JDBCPreparedStatement.execute(AS400JDBCPreparedStatement.java:744)
        at callCommand2.main(callCommand2.java:154)

Ofcourse I do have the routine PSC1210 in IJLIB. I was able to call PSE1210 directly from AS400 itself, and it worked fine. But when I want to do the same using JDBC, it fails with the above error message. I now have both the stored procedure and the external routine in the same library - IJLIB.

My JDBC connection string looks like this:

connection = DriverManager.getConnection("jdbc:as400://10.2.23.9/;libraries=IJLIB","TESTER","TESTER");

Please let me know if you have any suggestions.

Thanks - Inayat
0
 
LVL 33

Accepted Solution

by:
shalomc earned 150 total points
ID: 10700364
Inayat,
Did you try to call the stored procedure from the AS400 itself? Please tell me what are the results of such a call.
Either use interactive SQL or write RPG program to call the stored procedure.
Start a debug session before the call, so a detailed job log will be produced.

I have a feeling that you have a parameter mismatch between the CL, the stored procedure declaration, or the actual call.

ShalomC
0
Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

 
LVL 1

Expert Comment

by:Helixir
ID: 10706728
Guys sorry to disturb you in this Thread but could you go see that Question !! We havea  Java Question !!

http://www.experts-exchange.com/Programming/Programming_Platforms/AS400/Q_20935878.html#10706631
0
 
LVL 27

Expert Comment

by:tliotta
ID: 10771366
Since I'm no JDBC expert by any means, I'm only guessing here; but you _might_ want to make some changes in your parameter definitions. For example:

 IN rspky CHAR (10 )

might be better specified as

 IN rspky VARCHAR (10 )

Seems surprising that Java wouldn't be trying to send VARCHAR parms. Of course, appropriate changes should be made to your stored procedure as well.

Tom
0
 
LVL 14

Expert Comment

by:daveslater
ID: 11170431
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

Accept shalomc comment as answer
 

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
daveslater
Page Editor
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

After-hours service is a fact of life for most MSPs. While not the most pleasant aspect of the job, there are ways to make after-hours servicing a more profitable and organized enterprise.
Active Directory security has been a hot topic of late, and for good reason. With 90% of the world’s organization using this system to manage access to all parts of their IT infrastructure, knowing how to protect against threats and keep vulnerabil…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question