Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

JDBC with DB2/400 - Error when calling external routines

Posted on 2004-03-28
7
Medium Priority
?
2,648 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 600 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
The core idea of this article is to make you acquainted with the best way in which you can export Exchange mailbox to PST format.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Suggested Courses

670 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