|
View previous topic :: View next topic
|
| Author |
Message |
Bang_1
New User
Joined: 08 May 2009
Posts: 39
Location: Bangalore
|
|
|
|
Hi,
I have a query regarding DB2 CLOB column. Trying to understand processing of CLOB data. DB2 version is 9.
Below is the code for CURSOR declaration, open and fetch
| Code: |
EXEC SQL
DECLARE GTPHOTO CURSOR FOR
SELECT RESUME
FROM DSN8910.EMP_PHOTO_RESUME
WHERE EMPNO = :HST-EMPNO
END-EXEC.
PROCEDURE DIVISION.
MOVE '000130' TO HST-EMPNO.
EXEC SQL
OPEN GTPHOTO
END-EXEC.
..... evaluating SQL code .....
EXEC SQL
FETCH FROM GTPHOTO INTO
:HST-RESUME
END-EXEC.
..... evaluating SQL code .....
MOVE 'Resume:' TO WS-CLOB-POSSTR1.
EXEC SQL
SET :WS-CLOB-DATA-LENGTH = POSSTR(:HST-RESUME,
:WS-CLOB-POSSTR1)
END-EXEC. |
I got the starting position as 5 , which is correct ...
But when I used below SUBSTR to get the sub-string from 1 position with 5-bytes of length, I am getting SQLCODE of -84 in the precompilation
with error message - UNDEFINED OR UNUSABLE HOST VARIABLE "WS-CLOB-DATA"
| Code: |
MOVE 1 to WS-CLOB-START
MOVE WS-CLOB-DATA-LENGTH to WS-CLOB-END
EXEC SQL
SET :WS-CLOB-DATA = SUBSTR (:HST-RESUME,
:WS-CLOB-START,
:WS-CLOB-END)
END-EXEC. |
Did I miss anything in while getting data from SUBSTR. |
|
| Back to top |
|
 |
daveporcelan
Active Member
Joined: 01 Dec 2006
Posts: 792
Location: Pennsylvania
|
|
|
|
| Where (which SECTION) in your program is WS-CLOB-DATA defined? |
|
| Back to top |
|
|
Bang_1
New User
Joined: 08 May 2009
Posts: 39
Location: Bangalore
|
|
|
|
They are created in a copybook (manually, not by DCLGEN) , that is included in working storage section.
This is how compiler listing of that copybook...
| Code: |
*****EXEC SQL
***** INCLUDE HOSTCLOB
*****END-EXEC.
01 WS-CLOB-DATA.
02 WS-CLOB-DATA-LENGTH PIC S9(9) COMP.
02 WS-CLOB-DATA-DATA.
49 FILLER PIC X(32767).
........... after 159 repeats of the above line ....
49 FILLER PIC X(160).
01 WS-CLOB-START PIC S9(4) COMP.
01 WS-CLOB-END PIC S9(4) COMP. |
|
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| Code: |
01 WS-CLOB-START PIC S9(4) COMP.
01 WS-CLOB-END PIC S9(4) COMP. |
These are a bit small... |
|
| Back to top |
|
|
Bang_1
New User
Joined: 08 May 2009
Posts: 39
Location: Bangalore
|
|
|
|
Changed those two copy book fields to use S9(9) comp. same -84 is showing up.
| Code: |
*****EXEC SQL
***** SET :WS-CLOB-DATA = SUBSTR (:HST-RESUME,
***** :WS-CLOB-START,
***** :WS-CLOB-END)
*****END-EXEC.
MOVE -84 TO SQLCODE.
MOVE "42612" TO SQLSTATE. |
|
|
| Back to top |
|
|
Bang_1
New User
Joined: 08 May 2009
Posts: 39
Location: Bangalore
|
|
|
|
I just removed COPYBOOK include and added the following declaration at the begining of working-storage
| Code: |
WORKING-STORAGE SECTION.
01 WS-CLOB-DATA USAGE SQL TYPE IS CLOB(32767).
01 WS-CLOB-START PIC S9(9) COMP.
01 WS-CLOB-END PIC S9(9) COMP. |
it worked, i got the sysout for CLOB-DATA with first 50 chars
| Code: |
| CLOB-DATA : Resume: Bruce Adamson Personal Information |
So, looks like CLOB field has to be expanded by precompiler rather than adding them manually.
I do have one more question while testing yesterday - if SELECT query's WHERE clause has a literal in it (in CURSOR below cursor) , SQLCODE -84 was coming. If I replace that with a host-var, then it is not showing any error.
| Code: |
DECLARE GTPHOTO CURSOR FOR
SELECT RESUME
FROM DSN8910.EMP_PHOTO_RESUME
WHERE EMPNO = :HST-EMPNO |
Does it has to do anythng with BIND options. |
|
| Back to top |
|
|
|
|
