IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Pre-Compiler Bug with DSNH520I message?


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
John Poulakos

Active User


Joined: 13 Jun 2012
Posts: 178
Location: United States

PostPosted: Thu Aug 09, 2018 9:00 pm
Reply with quote

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.
Back to top
View user's profile Send private message
John Poulakos

Active User


Joined: 13 Jun 2012
Posts: 178
Location: United States

PostPosted: Thu Aug 09, 2018 9:09 pm
Reply with quote

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.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts How I Found a Bug in a FORTRAN Compiler All Other Mainframe Topics 4
This topic is locked: you cannot edit posts or make replies. how can I proof that message was post... Java & MQSeries 1
No new posts IMS Message : DFS3577A IMS DB/DC 4
No new posts dsnrexx fails without error message CLIST & REXX 9
No new posts Compiler and run JCL for basic PL/I p... PL/I & Assembler 10
Search our Forums:

Back to Top