www.mimer.com/developer

Extended Dynamic Cursors

---

A "normal" cursor is identified by an SQL identifier. An extended cursor makes it possible to represent a dynamic cursor by a host variable or a literal. An extended cursor is allocated by the application with the ALLOCATE CURSOR statement, see the Mimer SQL Reference Manual for the syntax description.

When the application is finished with the processing of the SQL statement, the prepared statement may be destroyed by executing the DEALLOCATE PREPARE statement, see the Mimer SQL Reference Manual for the syntax description. DEALLOCATE PREPARE also destroys any extended cursor that was associated with the statement.

Example of how extended cursors are used:

 ...
 exec sql  BEGIN DECLARE SECTION;
 SQL_TXT   CHARACTER(255);
 C1        CHARACTER(128);
 STM1      CHARACTER(128);
 HOSTVAR1  INTEGER;
 HOSTVAR2  CHARACTER(10);
 ...
 exec sql  END DECLARE SECTION;
 ...
 SQL_TXT := "SELECT COL1,COL2 FROM TAB1";
 STM1 := "STATE_1";
 exec sql PREPARE :STM1 FROM :SQL_TXT;
 C1 := "CUR1";
 exec sql ALLOCATE :C1 CURSOR FOR :STM1;
 ...
 exec sql ALLOCATE DESCRIPTOR 'SQLA' WITH MAX 50;
 exec sql DESCRIBE OUTPUT :STM1 USING SQL DESCRIPTOR 'SQLA';
 ...
 exec sql OPEN :C1;
 exec sql WHENEVER NOT FOUND GOTO done;
 loop
     exec sql FETCH :C1 INTO SQL DESCRIPTOR 'SQLA';
     exec sql GET DESCRIPTOR 'SQLA' VALUE 1 :HOSTVAR1 = DATA;
     exec sql GET DESCRIPTOR 'SQLA' VALUE 2 :HOSTVAR2 = DATA;
     ...
     display HOSTVAR1,HOSTVAR2,...;
 end loop;
 done:
 exec sql CLOSE :C1;
 exec sql DEALLOCATE DESCRIPTOR 'SQLA';
 exec sql DEALLOCATE PREPARE :STM1;
 ...
 

Upright Database Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 dbtechnology@upright.se