Fetching multiple rows

You use a cursor to retrieve rows from a query that has multiple rows in the result set. A cursor is a handle or an identifier for the SQL query result set and a position within that result set.

For an introduction to cursors, see Working with cursors.

 To manage a cursor in embedded SQL
  1. Declare a cursor for a particular SELECT statement, using the DECLARE statement.

  2. Open the cursor using the OPEN statement.

  3. Retrieve rows from the cursor one at a time using the FETCH statement.

    • Fetch rows until the SQLE_NOTFOUND warning is returned. Error and warning codes are returned in the variable SQLCODE, defined in the SQL communications area structure.

  4. Close the cursor, using the CLOSE statement.

Cursors in UltraLite applications are always opened using the WITH HOLD option. They are never closed automatically. You must explicitly close each cursor using the CLOSE statement.

The following is a simple example of cursor usage:



void print_employees( void )
{
   int status;
   EXEC SQL BEGIN DECLARE SECTION;
   char name[50];
   char sex;
   char birthdate[15];
   a_sql_len   ind_birthdate;
   EXEC SQL END DECLARE SECTION;
   /* 1. Declare the cursor. */
   EXEC SQL DECLARE C1 CURSOR FOR
      SELECT emp_fname || ' ' || emp_lname,
               sex, birth_date
      FROM "DBA".employee
      ORDER BY emp_fname, emp_lname;
   /* 2. Open the cursor. */
   EXEC SQL OPEN C1;
   /* 3. Fetch each row from the cursor. */
   for( ;; ) {
      EXEC SQL FETCH C1 INTO :name, :sex,
            :birthdate:ind_birthdate;
      if( SQLCODE == SQLE_NOTFOUND ) {
         break; /* no more rows */
      } else if( SQLCODE < 0 ) {
         break; /* the FETCH caused an error */
      }
      if( ind_birthdate < 0 ) {
         strcpy( birthdate, "UNKNOWN" );
      }
      printf( "Name: %s Sex: %c Birthdate:
               %s\n",name, sex, birthdate );
   }
   /* 4. Close the cursor. */
   EXEC SQL CLOSE C1;
}

For more information about the FETCH statement, see FETCH statement [ESQL] [SP].

 Cursor positioning
 Order of rows in a cursor
 Repositioning a cursor
 Cursor positioning after updates