Retrieve the Results Using a Cursor
To give your program the ability to access the database or issue other database statements while processing rows retrieved as the result of the select, use a cursor to retrieve those rows.
To use cursors, after the SQLDA has been analyzed and result variables have been allocated and pointed at, the program must declare and open a cursor in order to fetch the result rows.
The syntax of the cursor declaration for a dynamically defined SELECT statement is as follows:
EXEC SQL DECLARE cursor_name CURSOR FOR statement_name;
This statement associates the SELECT statement represented by statement_name with the specified cursor. Statement_name is the name assigned to the statement when the statement was prepared. As with non‑dynamic cursor declarations, the SELECT statement is not evaluated until the cursor is actually opened. After opening the cursor, the program retrieves the result rows using the FETCH statement with the SQLDA instead of the list of output variables.
The syntax for a cursor fetch statement is as follows:
EXEC SQL FETCH cursor_name USING DESCRIPTOR descriptor_name;
Before the FETCH statement, the program has filled the result descriptor with the addresses of the result storage areas. When executing the FETCH statement, OpenSQL copies the result columns into the result areas referenced by the descriptor.
The following program fragment elaborates on an earlier example in this section. The program reads a statement from the terminal. If the statement is quit, the program ends. Otherwise, the program prepares the statement. If the statement is not a SELECT, then it is executed. If the statement is a SELECT statement, then it is described, a cursor is opened, and the result rows are fetched. Error handling is not shown.
exec sql include sqlca;
exec sql include sqlda;
allocate an sqlda with 400 sqlvar elements;
sqlda.sqln = 400;
read statement_buffer from terminal;
loop while (statement_buffer <> 'quit')
exec sql prepare s1 from :statement_buffer;
exec sql describe s1 into sqlda;
if (sqlda.sqld = 0) then /* This is not a select */
exec sql execute s1;
else /* This is a select */
exec sql declare c1 cursor for s1;
exec sql open c1;
print column headers from the sqlname fields;
analyze the SQLDA, inspecting types and lengths;
allocate result variables for a cursor result row;
set sqlvar fields sqldata and sqlind;
loop while (sqlca.sqlcode = 0)
exec sql fetch c1 using descriptor sqlda;
if (sqlca.sqlcode = 0) then
print the row using sqldata and sqlind
pointed at by the sqlvar array;
end if;
end loop;
free result variables from the sqlvar elements;
exec sql close c1;
end if;
process sqlca and print the status;
read statement_buffer from terminal;
end loop;