Example Overview
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.
Example : Single Row SELECT
EXEC SQL
SELECT *
INTO :structure1
FROM table1
WHERE field3 = ’ABCDEF’;
Example : Multiple Row SELECT
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;
Example : Single Row SELECT That Returns Only Some Columns
EXEC SQL
SELECT field1,
field3
INTO :field1,
:field3
FROM table1
WHERE field2 = 99;
Example : Single Row SELECT Using Qualified Variable References
EXEC SQL
SELECT field1,
field3
INTO :structure1.field1,
:structure1.field3
FROM table1
WHERE field2 = 99;
Example : Single Row INSERT Using a Structure for Field Values
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.