To execute an SQL statement with a single result row, EXECUTE can be used. To save the result, add an INTO clause.
EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 STMT PIC X(50) VARYING. 01 V1 PIC S9(9) COMP. 01 V2 PIC S9(9) COMP. 01 V3 PIC X(50) VARYING. EXEC SQL END DECLARE SECTION END-EXEC. MOVE "SELECT a, b, c FROM test1 WHERE a > ?" TO ARR OF STMT. COMPUTE LEN OF STMT = FUNCTION STORED-CHAR-LENGTH (ARR OF STMT). EXEC SQL PREPARE MYSTMT FROM :STMT END-EXEC. ... EXEC SQL EXECUTE MYSTMT INTO :V1, :V2, :V3 USING 37 END-EXEC.
An EXECUTE command can have an INTO clause, a USING clause, both, or neither.
If a query is expected to return more than one result row, a cursor should be used, as in the following example. (See "D.3.2 Using Cursors" for more details about the cursor.)
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 DBANAME PIC X(128) VARYING.
01 DATNAME PIC X(128) VARYING.
01 STMT PIC X(200) VARYING.
EXEC SQL END DECLARE SECTION END-EXEC.
MOVE "SELECT u.usename as dbaname, d.datname
- " FROM pg_database d, pg_user u
- " WHERE d.datdba = u.usesysid"
TO ARR OF STMT.
COMPUTE LEN OF STMT = FUNCTION STORED-CHAR-LENGTH (ARR OF STMT).
EXEC SQL CONNECT TO testdb AS con1 USER testuser END-EXEC.
EXEC SQL PREPARE STMT1 FROM :STMT END-EXEC.
EXEC SQL DECLARE cursor1 CURSOR FOR STMT1 END-EXEC.
EXEC SQL OPEN cursor1 END-EXEC.
EXEC SQL WHENEVER NOT FOUND GOTO FETCH-END END-EXEC.
PERFORM NO LIMIT
EXEC SQL FETCH cursor1 INTO :DBANAME,:DATNAME END-EXEC
DISPLAY "dbaname=" ARR OF DBANAME ", datname=" ARR OF DATNAME
END-PERFORM.
FETCH-END.
EXEC SQL CLOSE cursor1 END-EXEC.
EXEC SQL COMMIT END-EXEC.
EXEC SQL DISCONNECT ALL END-EXEC.