Named SQLDA

A named SQLDA consists of a header and one or more item descriptor areas. The header contains information concerning the entire descriptor area, and each item descriptor area describes a column in the result row.

• Before using an SQLDA, you need to allocate it.

  EXEC SQL ALLOCATE DESCRIPTOR identifier;

• When you no longer need the SQLDA, deallocate it in time.

  EXEC SQL DEALLOCATE DESCRIPTOR identifier;

• To use a descriptor area, declare it using the INTO clause.

  EXEC SQL FETCH NEXT FROM mycursor INTO SQL DESCRIPTOR mydesc;

  If the result set is empty, the descriptor area still contains the metadata from the query.

• For a prepared query that has not been executed, you can use DESCRIBE to obtain the metadata of its result set.

  EXEC SQL BEGIN DECLARE SECTION; 
      char *sql_stmt = "SELECT * FROM table1"; 
  EXEC SQL END DECLARE SECTION;  
  
      EXEC SQL PREPARE stmt1 FROM :sql_stmt; 
      EXEC SQL DESCRIBE stmt1 INTO SQL DESCRIPTOR mydesc;

  In DESCRIBE and FETCH statements, the INTO and USING keywords are used similarly: they produce a result set and metadata in a descriptor area.

• Retrieve the value in a descriptor area from the header and store it into a host variable.

  EXEC SQL GET DESCRIPTOR name :hostvar = field;

• Currently, only one header descriptor area COUNT is defined, which tells how many item descriptor areas exist (that is, how many columns are contained in the result). The host variable must be of the integer type. Retrieve a specific value from the item descriptor area.

  EXEC SQL GET DESCRIPTOR name VALUE num :hostvar = field;

  num can be a character integer or a host variable that contains an integer. Possible data types are as follows:

  • CARDINALITY (integer): number of rows in the result set
  • DATA: actual data item (therefore, the data type of this field depends on the query)
  • DATETIME_INTERVAL_CODE (integer): When TYPE is 9, DATETIME_INTERVAL_CODE will have a value of 1 for DATE, 2 for TIME, 3 for TIMESTAMP, 4 for TIME WITH TIME ZONE, or 5 for TIMESTAMP WITH TIME ZONE.
  • INDICATOR (integer): indicator (indicating a null value or a value truncation)
  • LENGTH (integer): data length in characters
  • NAME(string): column name
  • OCTET_LENGTH (integer): length of the character representation of the data in bytes
  • PRECISION (integer): precision (for the numeric type)
  • RETURNED_LENGTH (integer): data length in characters
  • RETURNED_OCTET_LENGTH (integer): length of the character representation of the data in bytes
  • SCALE (integer): ratio (for the numeric type)
  • TYPE (integer): numeric code of the data type of the column
• Retrieve the column value and store it in a host variable.

  EXEC SQL GET DESCRIPTOR mydesc  VALUE num :hostvar = field

  num can be a character integer or a host variable that contains an integer. Possible columns are as follows:

  • DATA
  • Actual data item (the data type of this column depends on the query)
  • NAME(string)
  • Field name
• Manually create a descriptor area to provide input parameters for a query or cursor.

  EXEC SQL SET DESCRIPTOR name VALUE numfield = :hostvar;

• Retrieve multiple rows of records in a FETCH statement and use a host variable of the array type to store data.

  EXEC SQL BEGIN DECLARE SECTION; 
      int id[5]; 
  EXEC SQL END DECLARE SECTION;  
      EXEC SQL FETCH 5 FROM mycursor INTO SQL DESCRIPTOR mydesc;  
      EXEC SQL GET DESCRIPTOR mydesc VALUE 1 :id = DATA;