When the INTO Clause of FETCH is Required

When SELECT or EXECUTE FUNCTION (or EXECUTE PROCEDURE) omits the INTO clause, you must specify a data destination when a row is fetched.

For example, to dynamically execute a SELECT or EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement, the SELECT or EXECUTE FUNCTION (or EXECUTE PROCEDURE) cannot include its INTO clause in the PREPARE statement. Therefore, the FETCH statement must include an INTO clause to retrieve data into a set of variables. This method lets you store different rows in different memory locations.

You can fetch into a program-array element only by using an INTO clause in the FETCH statement. If you use a program array, you must list both the array name and a specific element of the array in data_structure. When you are declaring a cursor, do not refer to an array element within the SQL statement.

Tip: If you are certain of the number and data type of values in the select list of the Projection clause, you can use an INTO clause in the FETCH statement.

In the following example, a series of complete rows is fetched into a program array. The INTO clause of each FETCH statement specifies an array element as well as the array name:

EXEC SQL BEGIN DECLARE SECTION;
   char wanted_state[2];
   short int row_count = 0;
   struct customer_t{
   {
      int    c_no;
      char   fname[15];
      char   lname[15];
   } cust_rec[100];
EXEC SQL END DECLARE SECTION;

main()
{
   EXEC SQL connect to'stores_demo';
   printf("Enter 2-letter state code: ");
   scanf ("%s", wanted_state);
   EXEC SQL declare cust cursor for 
      select * from customer where state = :wanted_state;
   EXEC SQL open cust;
   EXEC SQL fetch cust into :cust_rec[row_count];
   while (SQLCODE == 0)
   {
      printf("\n%s %s", cust_rec[row_count].fname,
         cust_rec[row_count].lname);
      row_count++;
      EXEC SQL fetch cust into :cust_rec[row_count];
   }
   printf ("\n");
   EXEC SQL close cust;
   EXEC SQL free cust;
}