Retrieving Query Results

To retrieve the results of a query, embedded SQL provides special variants of the usual commands SELECT and FETCH. These commands have a special INTO clause that specifies which host variables the retrieved values are to be stored in. SELECT is used for a query that returns only a single row, and FETCH is used for a query that returns multiple rows, using a cursor.

• Here is an example using the command SELECT:

  /*  
   * Assume a table:
   * CREATE TABLE test1 (a int, b varchar(50)); 
   */
  EXEC SQL BEGIN DECLARE SECTION;
      int v1;
      VARCHAR v2;
  EXEC SQL END DECLARE SECTION;
  
      ...
  
      EXEC SQL SELECT a, b INTO :v1, :v2 FROM test;

  The INTO clause appears between the SELECT list and the FROM clause. The number of elements in the SELECT list and the list after INTO (also called the target list) must be equal.

• Here is an example using the command FETCH:

  EXEC SQL BEGIN DECLARE SECTION;
      int v1;
      VARCHAR v2;
  EXEC SQL END DECLARE SECTION;
      ...
  
      EXEC SQL DECLARE foo CURSOR FOR SELECT a, b FROM test;
      ...
      do
      {
          ...
          EXEC SQL FETCH NEXT FROM foo INTO :v1, :v2;
          ...
      } while (...);

  The INTO clause appears after all SQL clauses.