RE: Calling stored procedure from Java app

If you were hoping to have the resulting SQL look like

AND TRIM(SUBECD) IN ('0181','0183','90005')

then you are out of luck. A CallableStatement isn't a macro function that 
replaces arbitrary strings; instead it replaces syntactic units. Since your 
stored procedure already has IN(IN_ITEMLIST), the stored procedure mechanism 
interprets that as IN with a single entry to choose from. Giving it the string 
you are giving it results in something like

AND TRIM(SUBECD) IN ('''0181'',''0183'',''90005''')

which will compare to a single string (that contains a lot of quotes and 
commas). There is no good solution for what you are trying to do.

PC2

-----Original Message-----
From: java400-l-bounces@xxxxxxxxxxxx [mailto:java400-l-bounces@xxxxxxxxxxxx] On 
Behalf Of Kelly Jones
Sent: April 5, 2006 13:14
To: java400-l@xxxxxxxxxxxx
Subject: Calling stored procedure from Java app

Hi,
 
I'm hoping somebody can help with this.  I have an application that is running 
on a WinTel box which makes calls to an iSeries via various stored procedures.  
However, I have run into something I can't figure out.
 
I have a stored procedure on the iSeries as follows:
 
CREATE PROCEDURE CWMPINH.BULKINSERTITEM98USER (IN IN_COMPANY VARCHAR(3), IN 
IN_ITEMLIST VARCHAR(15000)) LANGUAGE SQL 
 
INSERT INTO CWWEBEX.ITEM98USER(
 COMPANY,
 ITEM,
 WEB98ID,
 ITEM98VALUE)
SELECT A.SUCOMP AS COMPANY,
 TRIM(A.SUBECD) AS ITEM,
 TRIM(B.RKVETT) AS WEB98ID,
 TRIM(A.SUIAGM) AS ITEM98VALUE
FROM CWMPDTA.INITUFL0 A
JOIN CWMPDTA.MSUSFDL0 B ON A.SUCOMP = B.RKCOMP AND A.SUKINR = B.RKYPNO AND 
TRIM(B.RKDRSV) = 'ITM'
WHERE A.SUCOMP = IN_COMPANY
AND TRIM(A.SUIAGM) != ''
AND TRIM(SUBECD) IN (IN_ITEMLIST)
 
 
I am passing IN_COMPANY as a character string 001 and IN_ITEMLIST is a 
character string such as '0181',0183','90005' .  
 
The Java app sets up the stored proc as follows:
 
            cs = connection.prepareCall("{CALL 
CWMPINH.BULKINSERTITEM98USER7(?,?)}");
            cs.setInt(1, 1);
            // cs.setString(1, company);
            cs.setString(2, itemlist);
            cs.execute();
            cs.close();
 
The problem I am having is that no matter how I structure the item list string 
I can not get the stored procedure to fire.  It either does nothing (I'm 
assuming it's reading the string incorrectly), or I completely break the stored 
proc.
 
I have also tried to structure the stored procedure as dynamic SQL where it 
evaluates the entire SQL statement before executing, but to know avail.  I'm 
sure it has to be the way I'm escaping (or not) the single tick marks.  If I 
hard code some values in the "in" statement in the stored proc it works.  So I 
know it's the IN_ITEMLIST that is the problem.
 
Any help would be appreciated.