Solved

JDBC with DB2/400 - Error when calling external routines

Posted on 2004-03-28
7
2,501 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 32

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 32

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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

In this article, I will show you HOW TO: Install VMware Tools for Windows on a VMware Windows virtual machine on a VMware vSphere Hypervisor 6.5 (ESXi 6.5) Host Server, using the VMware Host Client. The virtual machine has Windows Server 2016 instal…
Is your company's data protection keeping pace with virtualization? Here are 7 dynamic ways to adapt to rapid breakthroughs in technology.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

744 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

9 Experts available now in Live!

Get 1:1 Help Now