Describing a Collection Variable

The DESCRIBE INPUT statement can provide information about a collection variable if you use the INTO or USING SQL DESCRIPTOR clause.

You must execute the DESCRIBE INPUT statement after you open the Select or Insert cursor. Otherwise, DESCRIBE INPUT cannot get information about the collection variable because it is the OPEN . . . USING statement that specifies the name of the collection variable to use.

The next program fragment dynamically selects the elements of the :a_set collection variable into a system-descriptor area called desc1:

EXEC SQL BEGIN DECLARE SECTION;
      client collection a_set;
      int i, set_count;
      int element_type, element_value;
EXEC SQL END DECLARE SECTION;

EXEC SQL allocate collection :a_set;
EXEC SQL allocate descriptor 'desc1';
EXEC SQL select set_col into :a_set from table1;
EXEC SQL prepare set_id from
      'select * from table(?)';


EXEC SQL declare set_curs cursor for set_id;
EXEC SQL open set_curs using :a_set;
EXEC SQL describe set_id using sql descriptor 'desc1';do
{
      EXEC SQL fetch set_curs using sql descriptor 'desc1';
      ...
      EXEC SQL get descriptor 'desc1' :set_count = count;
      for (i = 1; i <= set_count; i++)
      {
         EXEC SQL get descriptor 'desc1' value :i
            :element_type = TYPE;
         switch
         {
            case SQLINTEGER:
               EXEC SQL get descriptor 'desc1' value :i
                  :element_value = data;
            ...
         } /* end switch */
      } /* end for */
} while (SQLCODE == 0);

EXEC SQL close set_curs;
EXEC SQL free set_curs;
EXEC SQL free set_id;
EXEC SQL deallocate collection :a_set;
EXEC SQL deallocate descriptor 'desc1';