RE: Calling stored procedure from Java app

Hi Paul,

Yes, I am looking for the resulting SQL statement to look like what you
describe.  However, I'm not sure what you mean by "A CallableStatement
isn't a macro function that replaces arbitrary strings".  I'm not trying
to replace any string, I just want the stored procedure to interpret
exactly what I'm passing it.

I can get the procedure to work from Ops Nav "run a script", but can not
get it work from the Java app.  I can't believe something as simple as
this isn't doable.

I suppose I could loop through all of the items in my itemList and put
them in a temp table on the iSeries and do a select from somestuff where
item in (select item from temp table), but I would prefer not to have to
do that.

There must be a way...

Kelly



-----Original Message-----
From: java400-l-bounces@xxxxxxxxxxxx
[mailto:java400-l-bounces@xxxxxxxxxxxx] On Behalf Of Clapham, Paul
Sent: Wednesday, April 05, 2006 2:29 PM
To: Java Programming on and around the iSeries / AS400
Subject: 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.

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