Solved

JDBC with DB2/400 - Error when calling external routines

Posted on 2004-03-28
7
2,515 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
iSeries (AS400) MSF Job Ended, Reason code 05 7 153
V7R2 Virtual Tape to an iASP 1 64
AS400 Logging Print device 2 73
iSeries Memory Utilization 2 73
In 2017, ransomware will become so virulent and widespread that if you aren’t a victim yourself, you will know someone who is.
An analysis of the phishing scam that has been affecting Google users, along with steps to take for protection, as well as what to do if you receive one of the emails.
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

920 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now