× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.