|
View previous topic :: View next topic
|
| Author |
Message |
prahalad
New User
Joined: 14 Sep 2010
Posts: 18
Location: Pune
|
|
|
|
Guys,
Please help me in stored procedure issue. Below is my Internal Stored procedure . I'm calling this stored procedure thorugh my cobol program .
Cobol program returns SQLCODE = 0 but the value of V_ACT_ID is coming as junk. So it is not returing a proper numeric value to my main program
Stored Procedure code:
| Code: |
********************************* Top of Data ********************************** .
CREATE PROCEDURE BP.PBP00010 (OUT V_ACT_ID INTEGER) .
FENCED .
RESULT SETS 0 .
EXTERNAL NAME PBP00010 .
LANGUAGE SQL .
MODIFIES SQL DATA .
COLLID CBP001BD .
PARAMETER STYLE GENERAL WITH NULLS .
NOT DETERMINISTIC .
ASUTIME NO LIMIT .
PROGRAM TYPE MAIN .
SECURITY DB2 .
COMMIT ON RETURN NO .
STAY RESIDENT YES .
-------------------------------------------------------------------- .
--
---------------------------------------------------------------------
BEGIN
--
--
DECLARE v_base_n INTEGER;
DECLARE v_base_n1 INTEGER;
DECLARE v_base_n2 INTEGER;
DECLARE V_ACT_ID INTEGER DEFAULT 0;
DECLARE v_act_upd CHAR(09);
--
SELECT INTEGER(I_LAST_ACCOUNT_NUMBER) INTO v_base_n
FROM BP.TBP_CONTROL where I_COMPANY_ID = 1;
--
SET v_base_n1 = v_base_n + 1 ;
SET v_base_n1 = v_base_n + 1 ; .
-- .
UPDATE BP.TBP_CONTROL .
SET I_LAST_ACCOUNT_NUMBER = char(v_base_n1) .
where I_COMPANY_ID = 1; .
-- .
SET v_base_n2 = v_base_n1 * 10; .
SET V_ACT_ID = v_base_n2; .
-- .
-- .
END .
|
Cobol program: Linkage section:
| Code: |
LINKAGE SECTION.
01 V_ACT_ID PIC S9(9) USAGE COMP-3
VALUE ZEROS.
|
Procedure division:
| Code: |
EXEC SQL
CALL BP.PBP00010(:V_ACT_ID)
END-EXEC
|
after executing this piece of code. I do get SQL code zero , however value returned value is incorrect. |
|
| Back to top |
|
 |
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1787
Location: Bloomington, IL
|
|
|
|
| And would that returned value actually be appropriate to an INTEGER (i.e., binary) field? |
|
| Back to top |
|
|
prahalad
New User
Joined: 14 Sep 2010
Posts: 18
Location: Pune
|
|
|
|
Hi Akatsukami ,
Yes , returned value is an integer (it is nothing but a unique 9 digit number ).
actual value eturned in my main program(spool details)
| Code: |
Returned value:000³00000
|
|
|
| Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1787
Location: Bloomington, IL
|
|
|
|
| prahalad wrote: |
Hi Akatsukami ,
Yes , returned value is an integer (it is nothing but a unique 9 digit number ).
actual value eturned in my main program(spool details)
| Code: |
Returned value:000³00000
|
|
So then, that's going to be incompatible with the declaration of V-ACT-ID as a COMP-3 (i.e. packed decimal) variable in the linkage section, isn't it? |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
Akatsukami,
good eyes, nice catch (as in catch the mistake, Robert) |
|
| Back to top |
|
|
raj1984
New User
Joined: 28 Oct 2010
Posts: 16
Location: India
|
|
|
|
Hi ,
Thanks first.
I changed the piece of code now .
Linkage section is now declared as comp instead of comp-3:
| Code: |
01 V_ACT_ID PIC S9(9) USAGE COMP
VALUE ZEROS.
|
Now what i get is after executing this code :
Spool details :
| Code: |
AFTER EXECU :000655360
|
it is now giving a standard number as 000655360 for every execution. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
Why is v-act-id defined in linkage section ? that won't work.
Host variables have to be in Working storage. |
|
| Back to top |
|
|
raj1984
New User
Joined: 28 Oct 2010
Posts: 16
Location: India
|
|
|
|
| I did even tried declaring the V_ACT_ID in the working storage section . Still it returned the value as zeros. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
Have you corrected the PIC or are you still using comp-3. . .?
(As Akatsukami mentioned earlier). . . |
|
| Back to top |
|
|
daveporcelan
Active Member
Joined: 01 Dec 2006
Posts: 792
Location: Pennsylvania
|
|
|
|
Dick,
TS said it was switched to USAGE COMP.
I wonder why not just use PIC 9(9). With nothing else. |
|
| Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1787
Location: Bloomington, IL
|
|
|
|
| raj1984 wrote: |
| it is now giving a standard number as 000655360 for every execution. |
Suspicious; 655,360 decimal is X'000A0000'. I'd suspect alignment issues at a minimum.
I recommend that you take daveporcelan's suggestion of using character/zoned decimal/DISPLAY variables for inter-module communication. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
| Quote: |
| TS said it was switched to USAGE COMP. |
Thanks Dave 
I missed that. . . 
d |
|
| Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1787
Location: Bloomington, IL
|
|
|
|
| dick scherrer wrote: |
| Quote: |
| TS said it was switched to USAGE COMP. |
Thanks Dave
I missed that. . .
|
Perhaps because the TS has also apparently changed his member ID from prahalad to raj1984? |
|
| Back to top |
|
|
raj1984
New User
Joined: 28 Oct 2010
Posts: 16
Location: India
|
|
|
|
Hi ,
It is still an issue . I even tried to get the value in char mode . still it fails . |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
You're not supposed to declare your output variables again in the coding.
remove
| Code: |
| DECLARE V_ACT_ID INTEGER DEFAULT 0; |
extra 1: instead of coding and calling a stored procedure you could just
| Code: |
select integer(I_LAST_ACCOUNT_NUMBER) * 10 from FINAL TABLE (
update TBP_CONTROL
set I_LAST_ACCOUNT_NUMBER = char(integer(I_LAST_ACCOUNT_NUMBER)+1)
where I_COMPANY_ID = 1 ) |
extra 2: a function would properly be more useful than a procedure.
extra 3: consider using sequences instead of a tbp_control table which will cause locking problems.
V_ACT_ID is actually NULL because of not assigning any value to it in the proc (because of duplicate definition) |
|
| Back to top |
|
|
raj1984
New User
Joined: 28 Oct 2010
Posts: 16
Location: India
|
|
|
|
Thanks GuyC,
Above code is a sample, i'm just trying to establish the connection first . Once it is done , then actual logic of unique nbr generation will be inserted.
So i need to make this SP work. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| so do as I told you, and it will |
|
| Back to top |
|
|
raj1984
New User
Joined: 28 Oct 2010
Posts: 16
Location: India
|
|
|
|
Thanks GuyC,
I tried that option. I removed V_ACT_ID from the declaration (duplicate)
and declared same variable in COBOL working storage. Now it is giving a values are zeros(becuase i intialized the variable in COBOL program).
Thanks
Rajesh |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| publib.boulder.ibm.com wrote: |
| Do not include the FENCED or EXTERNAL keywords |
|
|
| Back to top |
|
|
raj1984
New User
Joined: 28 Oct 2010
Posts: 16
Location: India
|
|
|
|
Thanks GuyC ,
I removed "FENCED" command . Bad luck this time also.. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| removed "EXTERNAL NAME PBP00010 " also ? |
|
| Back to top |
|
|
raj1984
New User
Joined: 28 Oct 2010
Posts: 16
Location: India
|
|
|
|
Yes GuyC , both FENCED and EXTERNAL are removed.
Below is my SP procedure:
| Code: |
CREATE PROCEDURE BP.PBP00024 (INOUT V_ACT_ID char(09))
-- FENCED
RESULT SETS 0
-- EXTERNAL NAME PBP00024
LANGUAGE SQL
MODIFIES SQL DATA
COLLID CBP001BD
-- PARAMETER STYLE GENERAL
NOT DETERMINISTIC
ASUTIME NO LIMIT
PROGRAM TYPE MAIN
SECURITY DB2
COMMIT ON RETURN NO
STAY RESIDENT NO
|
|
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
this works on my side :
| Code: |
--drop PROCEDURE GUYCTEST ;
CREATE PROCEDURE GUYCTEST (INOUT V_ACT_ID char(9))
RESULT SETS 0
LANGUAGE SQL
MODIFIES SQL DATA
COLLID CBP001BD
NOT DETERMINISTIC
ASUTIME NO LIMIT
PROGRAM TYPE MAIN
SECURITY DB2
COMMIT ON RETURN NO
STAY RESIDENT NO
BEGIN
DECLARE v_base_n2 INTEGER;
SET v_base_n2 = 10;
SET V_ACT_ID = char(v_base_n2);
--
--
END |
|
|
| Back to top |
|
|
raj1984
New User
Joined: 28 Oct 2010
Posts: 16
Location: India
|
|
|
|
It works for me when i run it standalone(we have AQT tool through which i can run the SP).
However , when i call it through COBOL program , it returns no value .. |
|
| Back to top |
|
|
raj1984
New User
Joined: 28 Oct 2010
Posts: 16
Location: India
|
|
|
|
Please let me know , how did u run it ?
through COBOL ? |
|
| Back to top |
|
|
|
|
