RE: Calling stored procedure from Java app

Paul,

For those "thousands of people who want to know what it is", here is the
answer....


CREATE PROCEDURE CWMPINH.BULKINSERTITEM98USER11
 (IN IN_COMPANY VARCHAR(3),
 IN IN_ITEMLIST VARCHAR(15000))
LANGUAGE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA 
BEGIN DECLARE STMT VARCHAR ( 4000 ) ;  
SET STMT = '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 (' || CAST(IN_ITEMLIST AS VARCHAR(15000)) || ')'
;  
PREPARE SQLSTMT FROM STMT ; 
EXECUTE SQLSTMT ; 
END 


While I didn't include this example in my original email, I had been
trying dynamic SQL as well as the "static" statements.  I started
thinking that if the SQL engine was seeing ''xxxx'',''xxxx'',''xxxx,''
then I might need to use Replace(IN_ITEMLIST, "''", "'"), but it seems
that wasn't necessary.  The "trick" was to CAST the IN_ITEMLIST.  DB2
isn't nearly as "friendly" as SQL server!

Thanks for everyone's help.  I'm a happy camper now...

Kelly



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

"I just want the stored procedure to interpret exactly what I'm passing
it."

And that's exactly what happens. Conceptually you're passing it a
string. Just one string. The callable-statement parameter mechanism
conceptually puts quotes around that one string and plugs it into the
parameter. That results in a IN-clause that is comparing to just one
string. There is no way to pass it two or three or four strings (which
is what you would like to do) because you only have one parameter.

"There must be a way..."

Let me put it this way. Your question (usually in the context of
PreparedStatement) has been asked approximately biweekly on Sun's Java
forum for the last several years. From time to time the usual
"solutions" are proposed but they turn out not to work. Nobody has ever
posted a working solution. To me that says that there is no way. But if
you find one, there are thousands of people who want to know what it is.

Sorry,
PC2

-----Original Message-----
From: java400-l-bounces@xxxxxxxxxxxx
[mailto:java400-l-bounces@xxxxxxxxxxxx] On Behalf Of Kelly Jones
Sent: April 5, 2006 13:58
To: Java Programming on and around the iSeries / AS400
Subject: 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

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