#include <stdio.h>
#include <stdlib.h>
#include <string.h>
 
EXEC SQL INCLUDE SQLCA;
 
#define CHECKERR(CE_STR)   if (sqlca.sqlcode != 0) {printf("%s failed. Reason %ld\n", CE_STR, sqlca.sqlcode); exit(1); }


int main(int argc, char *argv[]) {
 
   EXEC SQL BEGIN DECLARE SECTION;
      char  table_name[19];
      char  st[80];  (1)
      char  parm_var[19];
      char userid[9];
      char passwd[19];
   EXEC SQL END DECLARE SECTION;
 
   printf( "Sample C program: DYNAMIC\n" );
 
   if (argc == 1) {
      EXEC SQL CONNECT TO sample;
          CHECKERR ("CONNECT TO SAMPLE");
   }
   else if (argc == 3) { 
      strcpy (userid, argv[1]);
      strcpy (passwd, argv[2]);
      EXEC SQL CONNECT TO sample USER :userid USING :passwd;
      CHECKERR ("CONNECT TO SAMPLE");
   }
   else {
      printf ("\nUSAGE: dynamic [userid passwd]\n\n");
      return 1;
   } /* endif */
   
   strcpy( st, "SELECT tabname FROM syscat.tables" );
   strcat( st, " WHERE tabname <> ?" );
   EXEC SQL PREPARE s1 FROM :st;  (2)
   CHECKERR ("PREPARE");
 
   EXEC SQL DECLARE c1 CURSOR FOR s1;  (3)
 
   strcpy( parm_var, "STAFF" );
   EXEC SQL OPEN c1 USING :parm_var;  (4)
   CHECKERR ("OPEN");
   do {
      EXEC SQL FETCH c1 INTO :table_name;  (5)
      if (SQLCODE != 0) break;
 
      printf( "Table = %s\n", table_name );
   } while ( 1 );
 
   EXEC SQL CLOSE c1;  (6)
   CHECKERR ("CLOSE");
 
   EXEC SQL COMMIT;
   CHECKERR ("COMMIT");
 
   EXEC SQL CONNECT RESET;
   CHECKERR ("CONNECT RESET");
   return 0;
}
/* end of program : DYNAMIC.SQC */







1.Declare host variables. This section includes declarations of three host
    variables: 
    table_name Used to hold the data returned during the FETCH statement 
    st Used to hold the dynamic SQL statement in text form parm_var 
        Supplies a data value to replace the parameter marker in st. 

  2.Prepare the statement. An SQL statement with one parameter marker
    (indicated by '?') is copied to the host variable. This host variable is
    passed to the PREPARE statement for validation. The PREPARE statement
    parses the SQL text and prepares an access section for the package in the
    same way that the precompiler or binder does, only it happens at run time
    instead of during preprocessing. 

  3.Declare the cursor. The DECLARE statement associates a cursor with a
    dynamically prepared SQL statement. If the prepared SQL statement is a
    SELECT statement, a cursor is necessary to retrieve the rows from the
    result table. 

  4.Open the cursor. The OPEN statement initializes the cursor declared
    earlier to point before the first row of the result table. The USING
    clause specifies a host variable to replace the parameter marker in the
    prepared SQL statement. The data type and length of the host variable
    must be compatible with the associated column type and length. 

  5.Retrieve the data. The FETCH statement is used to move the NAME column
    from the result table into the table_name host variable. The host variable
    is printed before the program loops back to fetch another row. 

  6.Close the cursor. The CLOSE statement closes the cursor and releases the
    resources associated with it. 

The CHECKERR macro/function is an error checking utility which is external
to the program. The location of this error checking utility depends upon the
programming language used: 

C check_error is redefined as CHECKERR and is located in the util.c
file.