Cursors in Embedded SQL

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

Cursor management in embedded SQL involves of the following steps:

1. Declare a cursor for a particular SELECT statement, using the DECLARE CURSOR statement.

2. Open the cursor using the OPEN statement.

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

4. Fetch rows until the Row Not Found warning is returned.

   Errors and warnings are returned in the SQLCA structure.

5. Close the cursor, using the CLOSE statement.

By default, cursors are automatically closed at the end of a transaction (on COMMIT or ROLLBACK). Cursors that are opened with a WITH HOLD clause are kept open for subsequent transactions until they are explicitly closed.

The following is a simple example of cursor usage:

void print_employees( void )
{
  EXEC SQL BEGIN DECLARE SECTION;
  char      name[50];
  char      sex;
  char      birthdate[15];
  a_sql_len ind_birthdate;
  EXEC SQL END DECLARE SECTION;
  EXEC SQL DECLARE C1 CURSOR FOR
    SELECT GivenName || ' ' || Surname, Sex, BirthDate FROM Employees;
  EXEC SQL OPEN C1;
  for( ;; ) 
  {
    EXEC SQL FETCH C1 INTO :name, :sex, :birthdate:ind_birthdate;
    if( SQLCODE == SQLE_NOTFOUND ) 
    {
      break;
    } 
    else if( SQLCODE < 0 ) 
    {
      break;
    }

    if( ind_birthdate < 0 ) 
    {
      strcpy( birthdate, "UNKNOWN" );
    }
    printf( "Name: %s Sex: %c Birthdate: %s\n", name, sex, birthdate );
  }
  EXEC SQL CLOSE C1;
}