|
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 ?
As an Amazon Associate we earn from qualifying purchases.
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.