SQL/MP Programming Manual for COBOL

Dynamic SQL Operations
HP NonStop SQL/MP Programming Manual for COBOL—529758-003
10-38
Sample Dynamic SQL Program
PROCEDURE DIVISION.
1000-DRIVER.
PERFORM 3000-SPECIFY-ERROR-HANDLING.
PERFORM 3100-PROCESS-QUERIES.
STOP RUN.
3000-SPECIFY-ERROR-HANDLING.
EXEC SQL
WHENEVER SQLERROR PERFORM :6000-HANDLE-ERROR
END-EXEC.
3100-PROCESS-QUERIES.
MOVE "SELECT EMPNUM FROM =EMPLOYEE WHERE
- " SALARY > 100000" TO TEXT-STRING OF STMT-TEXT (1)
MOVE "SELECT EMPNUM FROM =EMPLOYEE WHERE
- " SALARY < 20000" TO TEXT-STRING OF STMT-TEXT (2)
MOVE "SELECT SALARY FROM =EMPLOYEE WHERE
- " JOBCODE = 400" TO TEXT-STRING OF STMT-TEXT (3)
MOVE "C1" TO CURSOR-NAME OF CURSORS (1)
MOVE "C2" TO CURSOR-NAME OF CURSORS (2)
MOVE "C3" TO CURSOR-NAME OF CURSORS (3)
MOVE "S1" TO STMT-NAME OF STATEMENTS (1)
MOVE "S2" TO STMT-NAME OF STATEMENTS (2)
MOVE "S3" TO STMT-NAME OF STATEMENTS (3)
PERFORM VARYING IDX FROM 1 BY 1 UNTIL IDX > 3
EXEC SQL BEGIN WORK END-EXEC
MOVE TEXT-STRING OF STMT-TEXT (IDX) TO TEMP-STMT-TEXT
MOVE STMT-NAME OF STATEMENTS (IDX) TO TEMP-STMT-NAME
MOVE TEXT-STRING OF STMT-TEXT (IDX) TO TEMP-STMT-TEXT
EXEC SQL PREPARE :TEMP-STMT-NAME FROM :TEMP-STMT-TEXT
END-EXEC
MOVE CURSOR-NAME OF CURSORS (IDX) TO TEMP-CURSOR-NAME
EXEC SQL DECLARE :TEMP-CURSOR-NAME CURSOR FOR
:TEMP-STMT-NAME
END-EXEC
EXEC SQL OPEN :TEMP-CURSOR-NAME END-EXEC
PERFORM UNTIL SQLCODE < 0 OR SQLCODE = 100
EXEC SQL FETCH :TEMP-CURSOR-NAME INTO
:ANSWER END-EXEC
IF SQLCODE >= 0 AND SQLCODE NOT = 100 THEN
MOVE ANSWER TO DISPLAY-VALUE
DISPLAY "ANSWER IS " DISPLAY-VALUE
END-IF
END-PERFORM
EXEC SQL CLOSE :TEMP-CURSOR-NAME END-EXEC
EXEC SQL COMMIT WORK END-EXEC
END-PERFORM.
6000-HANDLE-ERROR.
ENTER TAL "SQLCADISPLAY" USING SQLCA
STOP RUN.
Example 10-4. Sample Dynamic SQL Program (page 2 of 2)