Example Overview - Preprocessor2 for Embedded SQL

Teradata Preprocessor2 for Embedded SQL Programmer Guide

Product
Preprocessor2 for Embedded SQL
Release Number
15.10
Language
English (United States)
Last Update
2018-10-07
dita:id
B035-2446
lifecycle
previous
Product Category
Teradata Tools and Utilities

The examples in this section show referencing a structure for output. Assume the following table resides in a Teradata Database:

CREATE TABLE TABLE1, NO FALLBACK,
                     NO BEFORE JOURNAL,
                     NO AFTER JOURNAL
   (FIELD1  CHAR(20),
    FIELD2 INTEGER,
    FIELD3 CHAR(6),
    FIELD4 DECIMAL(13,2))
UNIQUE PRIMARY INDEX (FIELD1)
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)
END-EXEC

The fields in the table would 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: The precompiler uses the elementary items as sending/receiving variables whenever a host structure name is used. If the above structure is defined as:

   DCL 01   STRUCTURE1,
         02 FIELD1      CHAR(20),
         02 FIELD2         FIXED BIN(31),
         02 FIELD3,
            03 FIELD3A  CHAR(2),
            03 FIELD3B  CHAR(2),
            03 FIELD3C  CHAR(2),
         02 FIELD4         FIXED DEC(13,2);
    

and specify the following single row SELECT statement:

   EXEC SQL
         SELECT *
         INTO  :STRUCTURE1
         FROM  TABLE1
         WHERE FIELD3 = ’ABCDEF’;

the precompiler generates code such that PL/I attempts these assignments:

   TABLE1.FIELD1 -> FIELD1  of STRUCTURE1
   TABLE1.FIELD2 -> FIELD2  of STRUCTURE1
   TABLE1.FIELD3 -> FIELD3A of STRUCTURE1
   TABLE1.FIELD4 -> FIELD3B of STRUCTURE1

Data moved into FIELD3A is truncated, and moving data into FIELD3B causes an error.