Solved

JDBC with DB2/400 - Error when calling external routines

Posted on 2004-03-28
7
2,571 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
RPG IV and RPG ILE book 4 493
iSeries - Add Users to a Group 1 248
AS400 SQL Cast Issue on Current Date/Time 4 106
iBM i DB2 Administration Training 6 119
We asked our MSP customer base what their favorite tools were and how they help them serve clients. We focused our questions on favorite tools in the following categories: >PSA tools >RMM tools >Alert management tools >Communication tools and Mo…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

752 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