Arrays and structures as indicator variables

For tables with a large number of columns you can use arrays and structures as a set of host variables that is referenced in a SQL statement. An indicator variable is always a 2-byte integer (short).

Example 1 This is an example of declaring indicator arrays:

EXEC SQL BEGIN DECLARE SECTION;

   /* Destination variables for fetches, using an */
   /* array.*/
   struct _hostvar {
      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;
   } host_var1;

   /* An indicator array for all variables. */
   short indic_var[7];

EXEC SQL END DECLARE SECTION;

Example 2 This is an example of declaring indicator structures:

EXEC SQL BEGIN DECLARE SECTION;
   /* Destination variables for fetches, using a */
   /* struct.*/
   struct _hostvar {
      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;
   } host_var1;

   /* An indicator structure for above variables. */
   struct _indicvar {
      short i_titleid;
      short i_title;
      short i_pubname;
      short i_pubcity;
      short i_pubstate;
      short i_notes;
      short i_purchase;
   } indic_var1;

EXEC SQL END DECLARE SECTION;

Example 3 This is an example of executing a query on indicator arrays or indicator structures:

EXEC SQL
SELECT titleid, title, pubname, city, state, notes,
        purchases
   INTO :host_var1 INDICATOR :indic_var1
   FROM T1, T2
   WHERE ....

When using structs and arrays as indicator variables:

• The number of elements in the indicator array or struct must be exactly the same as the number of elements in the host variable structure. A mismatch causes cpre or cpre64 to stop processing, and code is not generated.

• The columns in the SELECT list must match by sequence, and datatype, the chosen structure name in the INTO list. A mismatch causes ct_bind() runtime errors and stops processing.

• INDICATOR is an optional keyword, and can be omitted. However, the “:” before the hostvar struct and the indicator array or struct is required.

You cannot mix singleton host variables or singleton indicator variables with hostvar structures, and indicator arrays or structures.