Declaring and using cursors in a dynamically executed SQL application

Processing a cursor dynamically is nearly identical to processing it using static SQL. When a cursor is declared, it is associated with a query. By using the FETCH statement the cursor is positioned on the next row of the result table and assigns the values of that row to host variables.

In static SQL, the query is a SELECT statement in text form, while in dynamic SQL, the query is associated with a statement name assigned in a PREPARE statement. Any referenced host variables are represented by parameter markers.

The main difference between a static and a dynamic cursor is that a static cursor is prepared at precompile time, and a dynamic cursor is prepared at run time. Additionally, host variables referenced in the query are represented by parameter markers, which are replaced by runtime host variables when the cursor is opened.

 strcpy( prep_string, "SELECT tabname FROM syscat.tables" 
                      "WHERE tabschema = ?" ); 
 EXEC SQL PREPARE s1 FROM :prep_string; 
 EXEC SQL DECLARE c1 CURSOR FOR s1; 
 EXEC SQL OPEN c1 USING :host_var;

 MOVE "SELECT TABNAME FROM SYSCAT.TABLES WHERE TABSCHEMA = ?" 
      TO PREP-STRING. 
 EXEC SQL PREPARE S1 FROM :PREP-STRING END-EXEC. 
 EXEC SQL DECLARE C1 CURSOR FOR S1 END-EXEC. 
 EXEC SQL OPEN C1 USING :host-var END-EXEC.

 prep_string = 'SELECT tabname FROM syscat.tables WHERE tabschema = ?' 
 EXEC SQL PREPARE s1 FROM :prep_string 
 EXEC SQL DECLARE c1 CURSOR FOR s1 
 EXEC SQL OPEN c1 USING :host_var

   prep_string = "SELECT TABNAME FROM SYSCAT.TABLES WHERE TABSCHEMA = ?"
   CALL SQLEXEC 'PREPARE S1 FROM :prep_string'; 
   CALL SQLEXEC 'DECLARE C1 CURSOR FOR S1'; 
   CALL SQLEXEC 'OPEN C1 USING :schema_name';