15.10 - Example Overview - Preprocessor2 for Embedded SQL

Teradata Preprocessor2 for Embedded SQL Programmer Guide

prodname
Preprocessor2 for Embedded SQL
vrm_release
15.10
category
Programming Reference
featnum
B035-2446-035K

Examples of referencing a structure for output are shown below. Assume that the following table is defined on the server:

EXEC SQL
CREATE TABLE TABLE1,    NO FALLBACK,
                        NO BEFORE JOURNAL,
                        NO AFTER JOURNAL
   (field1   CHAR(20),
    field2   INTEGER,
    field3   CHAR(6),
    field4   FLOAT)
UNIQUE PRIMARY INDEX (FIELD1) ;

A typical host structure definition for this table takes the following form:

struct {
      char field1[21];
      long field2;
      char field3[7];
      float field4;
      } structure1;

When character string variables are declared, the length of the array must be one character longer than the length of the corresponding table column definition. The extra space is required for the terminating null.

EXEC SQL
      SELECT *
      INTO   :structure1
      FROM   table1
      WHERE field3 = ’ABCDEF’;
EXEC SQL
      DECLARE c1 CURSOR FOR
      SELECT *
      FROM   table1;
EXEC SQL
      OPEN c1;
EXEC SQL
      FETCH c1 INTO :structure1;  (loop for FETCH)
EXEC SQL
      CLOSE c1;
EXEC SQL
      SELECT   field1,
               field3
      INTO     :field1,
               :field3
      FROM     table1
      WHERE    field2 = 99;
EXEC SQL
      SELECT   field1,
               field3
      INTO     :structure1.field1,
               :structure1.field3
      FROM     table1
      WHERE    field2 = 99;
EXEC SQL
      INSERT INTO table1
        VALUES (:structure1);
The fields in the table receive the following assignments:
table1.field1 <- field1 of structure1
table1.field2 <- field2 of structure1
table1.field3 <- field3 of structure1
table1.field4 <- field4 of structure1

Note: PP2 uses the elementary items as sending/receiving variables whenever a host structure name is used.

If the structure is defined as:

struct {
         char   field1[21];
         long   field2;
         struct {
                 char field3a[3];
                 char field3b[3];
                 char field3c[3];
                } field3;
         float  field4;
       } structure1;

and the single row SELECT statement as:

EXEC SQL
         SELECT *
         INTO   :structure1
         FROM    table1
         WHERE   field3 = ’ABCDEF’;

PP2 generate codes such that C attempts the following assignments:

table1.field1 -> field1  of structure1
table1.field2 -> field2  of structure1
table1.field3 -> field3a of structure1
table1.field4 -> field3b of structure1

Data moved into field 3a is truncated with a ‘\0’ terminating null character. Moving data into field3b causes an error.