Fetching Rows

The program uses the FETCH statement to retrieve each row of output. This statement names a cursor and can also name the host variables that receive the data. The following example shows the completed IBM Informix ESQL/C code:

EXEC SQL DECLARE the_item CURSOR FOR
   SELECT order_num, item_num, stock_num
      INTO :o_num, :i_num, :s_num
      FROM items;
EXEC SQL OPEN the_item;
while(SQLCODE == 0)
{
   EXEC SQL FETCH the_item;
   if(SQLCODE == 0)
      printf("%d, %d, %d", o_num, i_num, s_num);
}

Detecting End of Data

In the previous example, the WHILE condition prevents execution of the loop in case the OPEN statement returns an error. The same condition terminates the loop when SQLCODE is set to 100 to signal the end of data. However, the loop contains a test of SQLCODE. This test is necessary because, if the SELECT statement is valid yet finds no matching rows, the OPEN statement returns a zero, but the first fetch returns 100 (end of data) and no data. The following example shows another way to write the same loop:

EXEC SQL DECLARE the_item CURSOR FOR
   SELECT order_num, item_num, stock_num
   INTO :o_num, :i_num, :s_num 
   FROM items;
EXEC SQL OPEN the_item;
if(SQLCODE == 0)
   EXEC SQL FETCH the_item;      /* fetch 1st row*/
while(SQLCODE == 0)
{
   printf("%d, %d, %d", o_num, i_num, s_num);
   EXEC SQL FETCH the_item;
}

In this version, the case of no returned rows is handled early, so no second test of SQLCODE exists within the loop. These versions have no measurable difference in performance because the time cost of a test of SQLCODE is a tiny fraction of the cost of a fetch.

Locating the INTO Clause

The INTO clause names the host variables that are to receive the data that the database server returns. The INTO clause must appear in either the SELECT or the FETCH statement. However it cannot appear in both statements. The following example specifies host variables in the FETCH statement:

EXEC SQL DECLARE the_item CURSOR FOR
   SELECT order_num, item_num, stock_num
      FROM items;
EXEC SQL OPEN the_item;   
while(SQLCODE == 0)   
{
   EXEC SQL FETCH the_item INTO :o_num, :i_num, :s_num;
   if(SQLCODE == 0)
      printf("%d, %d, %d", o_num, i_num, s_num);
}

This form lets you fetch different rows into different locations. For example, you could use this form to fetch successive rows into successive elements of an array.