RE: Getting an output variable from a stored proc in a stored pro c

[Todd Bryant <tbryant@xxxxxxxxxxxxxxxxxxxxxxx>]

I apologize I was not clear. I had been beating my head all day and could
not find any info on this so I was a little fried. 

Yes, we are not getting a value for SPENDABLE. However, I found a way that
it works. If I set SPENDABLE to a bogus value BEFORE I send it to the stored
proc, THEN it gets set with what it should in the proc. Very odd, and I
could not find this behavior documented anywhere. 

So, for the SPENDABLE var to be set in the call to ACTQ010 as an ouput
variable I have to do this:
-------------------

...
DECLARE STMT CHAR(4000);
DECLARE SPENDABLE CHAR(20);
DECLARE C1 CURSOR WITH RETURN FOR S1;

/* set spendable to an empty string */
SET SPENDABLE = '';
 
/* Now value gets set in proc like it should here */
CALL TST.ACTQ010('30013550', SPENDABLE);

SET STMT = 'SELECT FUND.ACTNO AS ACCOUNT_NUMBER,
        FUND.ACTNM AS SHORT_NAME,
        NAME.RCFDCP AS LONG_NAME,
        END.CODDSC AS ENDOWMENT,
/* concatenate amount in here */
        ''' || SPENDABLE || ''' AS SPENDABLE_AMT,
        PUR.CODE30 AS PURPOSE,
        DEV.TCNAM AS DEV_OFFICER,
        ADDR.PHONE AS PHONE_NUMBER,
        SUM(INVEST.INBKVL) AS BOOK_VALUE,
        SUM(INVEST.INMKVL) AS MARKET_VALUE,
        FUND31.CASH AS PRINCIPAL_CASH,
...

-----------------

I can only imagine that when it is compiling it to a C program that it is
only creating a pointer to a String with the declaration and doesn't make a
String object until I set it to something. That's my best guess. Sorry again
for not being more clear.







-----Original Message-----
From: Gary L Peskin [mailto:garyp@xxxxxxxxxxxx] 
Sent: Wednesday, April 23, 2003 6:10 PM
To: 'Java Programming on and around the iSeries / AS400'
Subject: RE: Getting an output variable from a stored proc in a stored proc

Todd --

I'm confused as to exactly what the problem is.  Are you not getting a value
for SPENDABLE from ACTQ010 or what?

What output variable doesn't "work" and what are you seeing?

Gary

> -----Original Message-----
> From: java400-l-bounces@xxxxxxxxxxxx 
> [mailto:java400-l-bounces@xxxxxxxxxxxx] On Behalf Of Todd Bryant
> Sent: Wednesday, April 23, 2003 1:24 PM
> To: 'java400-l@xxxxxxxxxxxx'
> Subject: Getting an output variable from a stored proc in a 
> stored proc
> 
> 
> Hi, I have a stored procedure question and though it is not 
> technically Java, it is going to be used in a Websphere 
> environment so I thought someone might be able to help me. 
> 
> We have a stored procedure that we are trying to get an 
> output variable to work. I know how to register an output 
> variable when calling a stored proc that accepts one in Java 
> (though I have not actually done it. I just know how), but we 
> want to call a stored proc from within an SQL stored proc and 
> get the output variable. Maybe looking at our code will help 
> (See code below. we are trying to get a "spendable amount" 
> from another stored proc. The other stored proc is written in 
> RPG and appears to be working properly. The called stored 
> proc takes a char string input var and accepts a char output 
> var. It returns no resultset). 
> 
> Maybe you can only get output variables in any other language 
> than SQL procedure language? Or maybe we are just missing 
> something simple? I truly appreciate any help anyone can give. 
> 
> -----------------
> CREATE PROCEDURE TST.FUND_DETAILS_1 ( 
>       IN FUND CHAR(8) , 
>       IN LIBRARY CHAR(15) ) 
>       DYNAMIC RESULT SETS 1 
>       LANGUAGE SQL 
>       SPECIFIC TST.FUND_DETAILS_1 
>       NOT DETERMINISTIC 
>       MODIFIES SQL DATA 
>       CALLED ON NULL INPUT
> BEGIN 
> DECLARE STMT CHAR(4000);
> DECLARE SPENDABLE CHAR(20);
> DECLARE C1 CURSOR WITH RETURN FOR S1;
> 
> 
> /* set spendable amount here */
> CALL TST.ACTQ010('30013550', SPENDABLE);
> 
> SET STMT = 'SELECT FUND.ACTNO AS ACCOUNT_NUMBER,
>       FUND.ACTNM AS SHORT_NAME,
>       NAME.RCFDCP AS LONG_NAME,
>       END.CODDSC AS ENDOWMENT,
> /* concatenate amount in here */
>       ''' || SPENDABLE || ''' AS SPENDABLE_AMT,
>       PUR.CODE30 AS PURPOSE,
>       DEV.TCNAM AS DEV_OFFICER,
>       ADDR.PHONE AS PHONE_NUMBER,
>       SUM(INVEST.INBKVL) AS BOOK_VALUE,
>       SUM(INVEST.INMKVL) AS MARKET_VALUE,
>       FUND31.CASH AS PRINCIPAL_CASH,
>       FUND.PLEDGE AS PLEDGE,
>       FUND.INVEST AS INVESTED,
>       FUND.CASH AS INCOME_CASH,
>       FUND.TOTBAL AS TOTAL_BALANCE,
>       HIER1.TBODSC AS CAMPUS_DESC,
>       HIER2.TBODSC AS INST_DESC, 
>       HIER3.TBODSC AS COLL_DESC,
>       HIER4.TBODSC AS DEPT_DESC,
>       HIER5.TBODSC AS SUB_DESC
> FROM
>       ' || LIBRARY ||'.LDSP010 AS FUND
>       inner join ' || LIBRARY ||'.LDSL014 AS FUND31
>               on (SUBSTR(FUND.ACTNO, 3, 5) = 
> SUBSTR(FUND31.ACTNO, 3, 5))
> 
>       inner join ' || LIBRARY || '.RCTP080 AS NAME
>               on (FUND.ACTNO = NAME.RCFNDC)
>       inner join ' || LIBRARY || '.TABP120 AS END
>               on (FUND.ENDWL = TRIM(END.CODE))
>       inner join ' || LIBRARY || '.TABP300 AS PUR
>               on (FUND.LDLD03 = PUR.CODE3)
>       inner join ' || LIBRARY || '.TABP290 AS DEV
>               on (FUND.CONT1 = DEV.TCONT)
>       left join ' || LIBRARY || '.ADDP010 AS ADDR
>               ON (DEV.TCMID# = ADDR.MSTID)
>       left join ' || LIBRARY || '.INVP020 AS INVEST
>               on (FUND.ACTNO = INVEST.INACT#)
>       left join ' || LIBRARY || '.TABL476 AS HIER1
>               on (FUND.LDOLV1 = HIER1.TBOLV1)
>       left join ' || LIBRARY || '.TABL472 AS HIER2
>               on (FUND.LDOLV1 = HIER2.TBOLV1
>               AND FUND.LDOLV2 = HIER2.TBOLV2)
>       left join ' || LIBRARY || '.TABL473 AS HIER3
>               on (FUND.LDOLV1 = HIER3.TBOLV1
>               AND FUND.LDOLV2 = HIER3.TBOLV2
>               AND FUND.LDOLV3 = HIER3.TBOLV3)
>       left join ' || LIBRARY || '.TABL474 AS HIER4
>               on (FUND.LDOLV1 = HIER4.TBOLV1
>               AND FUND.LDOLV2 = HIER4.TBOLV2
>               AND FUND.LDOLV3 = HIER4.TBOLV3
>               AND FUND.LDOLV4 = HIER4.TBOLV4)
>       left join ' || LIBRARY || '.TABL475 AS HIER5
>               on (FUND.LDOLV1 = HIER5.TBOLV1
>               AND FUND.LDOLV2 = HIER5.TBOLV2
>               AND FUND.LDOLV3 = HIER5.TBOLV3
>               AND FUND.LDOLV4 = HIER5.TBOLV4
>               AND FUND.LDOLV5 = HIER5.TBOLV5)
> 
> WHERE FUND.ACTNO = ?
>       AND END.FIELD = ''ENDWL''
>       AND PUR.FIELD = ''UDLD03'' 
>       AND ADDR.ADDTYP = ''B''
> 
> GROUP BY
>       FUND.ACTNO, FUND.ACTNM, NAME.RCFDCP, END.CODDSC, 
> PUR.CODE30, DEV.TCNAM, 
>       ADDR.PHONE, FUND31.CASH, FUND.PLEDGE, FUND.INVEST, 
> FUND.CASH, FUND.TOTBAL,HIER1.TBODSC,
>       HIER2.TBODSC, HIER3.TBODSC, HIER4.TBODSC, HIER5.TBODSC';
> 
> PREPARE S1 FROM STMT;
> OPEN C1 USING FUND;
> END
> ------------------------------------
> 
> 
> Todd Bryant, SCJP
> Programmer/Analyst
> University of Nebraska Foundation
> 402-472-0107


_______________________________________________
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.cgi/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.