#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   pname[10];
      short  dept;
      char userid[9];
      char passwd[19];
   EXEC SQL END DECLARE SECTION;
 
   printf( "Sample C program: OPENFTCH\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: openftch [userid passwd]\n\n");
      return 1;
   } /* endif */
   
   EXEC SQL DECLARE c1 CURSOR FOR  (1)
            SELECT name, dept FROM staff WHERE job='Mgr'
            FOR UPDATE OF job;
 
   EXEC SQL OPEN c1;  (2)
   CHECKERR ("OPEN CURSOR");
 
   do {
      EXEC SQL FETCH c1 INTO :pname, :dept;  (3)
      if (SQLCODE != 0) break;
 
      if (dept > 40) {
         printf( "%-10.10s in dept. %2d will be demoted to Clerk\n",
            pname, dept );
         EXEC SQL UPDATE staff SET job = 'Clerk'  (4)
            WHERE CURRENT OF c1;
         CHECKERR ("UPDATE STAFF");
      } else {
         printf ("%-10.10s in dept. %2d will be DELETED!\n",
            pname, dept);
         EXEC SQL DELETE FROM staff WHERE CURRENT OF c1;
         CHECKERR ("DELETE");
      } /* endif */
   } while ( 1 );
 
   EXEC SQL CLOSE c1;  (5)
   CHECKERR ("CLOSE CURSOR");
 
   EXEC SQL ROLLBACK;
   CHECKERR ("ROLLBACK");
   printf( "\nOn second thought -- changes rolled back.\n" );
 
   EXEC SQL CONNECT RESET;
   CHECKERR ("CONNECT RESET");
   return 0;
}
/* end of program : OPENFTCH.SQC */







1.Declare the cursor. The DECLARE CURSOR statement associates the cursor c1
    to a query. The query identifies the rows that the application retrieves
    using the FETCH statement. The job field of staff is defined to be
    updatable, even though it is not specified in the result table. 

  2.Open the cursor. The cursor c1 is opened, causing the database manager to
    perform the query and build a result table. The cursor is positioned
    before the first row. 

  3.Retrieve a row. The FETCH statement positions the cursor at the next row
    and moves the contents of the row into the host variables. This row
    becomes the current row. 

  4.Update OR Delete the current row. The current row is either updated or
    deleted, depending upon the value of dept returned with the FETCH
    statement. 

    If an UPDATE is performed, the position of the cursor remains on this row
    because the UPDATE statement does not change the position of the current
    row. 

    If a DELETE statement is performed, a different situation arises, because
    the current row is deleted. This is equivalent to being positioned before
    the next row, and a FETCH statement must be issued before additional
    WHERE CURRENT OF operations are performed. 

  5.Close the cursor. The CLOSE statement is issued, releasing the resources
    associated with the cursor. The cursor can be opened again, however. 

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.