RE: Calling stored procedure from Java app

did you try to set up IN_ITEMLIST with escape character \ before each single 
quote?


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


Hi Alex,

Thanks for the tip, but that didn't do the trick either.  

Kelly

 

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



CONFIDENTIALITY NOTE: The information contained in this email or fax is 
intended only for the use of the individual or entity to whom it is
addressed.
If you are not the intended recipient, you are hereby notified that any 
disclosure, copying, distribution, printing or other use of, or any
action 
taken in reliance upon, the contents of this email or fax and the
information
contained therein is strictly prohibited. Before opening or using
attachments, 
check them for viruses and defects.  Our liability to you for viruses or

defects associated with any email or fax is strictly limited to
resupplying any 
affected attachments.  If you have received this communication in error,
please immediately notify the sender by telephone at (646) 485-5398 and 
delete the original message. Thank you.