New array indicator feature in ESQL/C

With current ESQL/C preprocessors, it is not possible to use an array as an indicator variable. It only allows you to use indicator variables that tie to (nullable) host variables. Furthermore, each indicator variable must be coded for each nullable column in every embedded SQL statement of that application.

The new array indicator feature in ESQL/C removes these restrictions, allowing you to use an array of shorts for the indicators combined with a structure. This holds the host variables that can be subsequently referenced in a SQL statement.

The following example describes how to declare the new array indicator feature:

EXEC SQL BEGIN DECLARE SECTION;
/* Destination variables for fetches, using a struct. */

struct _vararr {
    int  m_titleid;

    char m_title[65];

    char m_pubname[41];

    char m_pubcity[21];

    char m_pubstate[3];

    char m_notes[201];

    float	 m_purchase;

} var_array;

/* An indicator array for all variables. */

short i_notes[7];

EXEC SQL END DECLARE SECTION;

The following example describes how to execute a query of the new array indicator:

EXEC SQL

SELECT titleid, title, pubname, city, state, notes,
        purchases

INTO  :var_array INDICATOR :i_notes

FROM  T1, T2

WHERE .....

Where:

• INTO

  :var_array is the reference to the hostvar structure that holds the program’s column variables at fetch time.

• INDICATOR

  INDICATOR is an optional keyword, and can be omitted. However, :i_notes in the example is the reference to the indicator array, and the : is mandatory.

  The indicator array is referenced by name, and no individual elements must be used. The number of elements in the indicator array must also match the number of host variables in the var_array structure.

• SELECT

  SELECT is the (column) select list from the selected tables. The select list must match by position and datatype the host variables in the INTO list. Failure to match the SELECT and INTO list causes runtime errors in the application.

  Though the example is based on a two-table join, SELECT and INTO must still match.

  Alternatively, for single-table queries, entering * expands to the same number of columns and datatypes as is depicted in INTO.

  SELECT * can have an adverse effect on (network) performance, particularly if the select list expands to a large number of columns.