RE: Calling stored procedure from Java app

Thanks Richard,

You were on the right track.  See my "answer"...

Kelly 

-----Original Message-----
From: java400-l-bounces@xxxxxxxxxxxx
[mailto:java400-l-bounces@xxxxxxxxxxxx] On Behalf Of Richard Casey
Sent: Wednesday, April 05, 2006 3:16 PM
To: Java Programming on and around the iSeries / AS400
Subject: RE: Calling stored procedure from Java app

Kelly,

Using a single parameter to replace a list of values in the IN condition
won't work. What should work is to define a large CHAR variable and
store your INSERT statement in that variable, concatenating your
IN_ITEMLIST parameter between the parentheses following the IN
condition. Then use PREPARE to prepare the statement and EXECUTE to
process it.

Hope this helps!

Richard


-----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/>


--
This is the Java Programming on and around the iSeries / AS400
(JAVA400-L) mailing list To post a message email: JAVA400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/java400-l
or email: JAVA400-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/java400-l.