RE: Calling stored procedure from Java app

Hi,

Try this:

CREATE PROCEDURE CWMPINH.BULKINSERTITEM98USER NOT DETERMINISTIC MODIFIES SQL 
DATA
(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)

MODIFIES SQL DATA should do the trick...

Regards,
Alex Pinsky
Intranet developer
Aeropostale, Inc.

-----Original Message-----
From: java400-l-bounces@xxxxxxxxxxxx
[mailto:java400-l-bounces@xxxxxxxxxxxx]On Behalf Of Kelly Jones
Sent: Wednesday, April 05, 2006 4:14 PM
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.
 
 
 
 
Kelly Jones
Sr. Web Developer
Chef's Catalog
ph: (719) 272-2600
fax:  (719) 272.2601
email: kjones@xxxxxxxxxxxxxxxx
web: www.chefscatalog.com <http://www.chefscatalog.com/>