John Poulakos
Active User
Joined: 13 Jun 2012
Posts: 178
Location: United States
|
|
|
|
I am getting DSNH520I on a dynamic cursor.
(THE OPEN STATEMENT FOR CURSOR "GETDOC" IS INVALID BECAUSE THE CURSOR WAS DEFINED BY AN ALLOCATE CURSOR STATEMENT.
Here is my code:
| Code: |
MOVE 'SELECT DOC_TYPE, BATCH, DOC_ID, DATE_TIME, '
TO VAR-TEXT (1:43).
MOVE 'MESSAGE_ID, MESSAGE_TEXT FROM PFIAFNT.SCISTAT '
TO VAR-TEXT (44:46).
MOVE 'WHERE DATE_TIME > CURRENT_TIMESTAMP - '
TO VAR-TEXT (90:38).
MOVE WS-DAYS TO VAR-TEXT (128:4).
MOVE ' DAYS' TO VAR-TEXT (132:5).
MOVE 136 TO VAR-LEN.
IF WS-CONTROL = 'NSA'
MOVE ' ORDER BY DOC_TYPE, BATCH, DOC_ID, DATE_TIME
TO VAR-TEXT (137:44)
ADD 44 TO VAR-LEN
END-IF.
PERFORM P190-PREPARE.
|
Other code.....
| Code: |
P190-PREPARE.
EXEC SQL
PREPARE SELECT_VERB FROM :VAR-STRING
END-EXEC
EXEC SQL
DECLARE GETDOC CURSOR FOR SELECT_VERB
END-EXEC
EXEC SQL
OPEN GETDOC
END-EXEC |
However, if I take the code in P190-PREPARE and move it to where the perform was, it works fine:
MOVE 'SELECT DOC_TYPE, BATCH, DOC_ID, DATE_TIME, '
TO VAR-TEXT (1:43).
MOVE 'MESSAGE_ID, MESSAGE_TEXT FROM PFIAFNT.SCISTAT '
TO VAR-TEXT (44:46).
MOVE 'WHERE DATE_TIME > CURRENT_TIMESTAMP - '
TO VAR-TEXT (90:38).
MOVE WS-DAYS TO VAR-TEXT (128:4).
MOVE ' DAYS' TO VAR-TEXT (132:5).
MOVE 136 TO VAR-LEN.
IF WS-CONTROL = 'NSA'
MOVE ' ORDER BY DOC_TYPE, BATCH, DOC_ID, DATE_TIME'
TO VAR-TEXT (137:44)
ADD 44 TO VAR-LEN
END-IF.
EXEC SQL
PREPARE SELECT_VERB FROM :VAR-STRING
END-EXEC
EXEC SQL
DECLARE GETDOC CURSOR FOR SELECT_VERB
END-EXEC
EXEC SQL
OPEN GETDOC
END-EXEC
Either way, the fetch statement is coded after the open. |
|
John Poulakos
Active User
Joined: 13 Jun 2012
Posts: 178
Location: United States
|
|
|
|
I found the problem. Just in case anyone else runs into this, when you do dynamic SQL the PREPARE, DECLARE, OPEN, FETCH and CLOSE statements must be in that physical order; logical order doesn't matter.
In my case the CLOSE was physically ahead of the others when I performed P190. |
|
