Structure Variables
You can use a structure variable in two different ways. First, you can use the structure as a simple variable, implying that all its members are used. This would be appropriate in the embedded SQL select, fetch, and insert statements. Second, you can use a member of a structure to refer to a single element. Of course, this member must be a scalar value (integer, floating-point or character string).
The syntax for referring to a complete structure is the same as referring to a simple variable:
:structurename
Syntax Notes:
• The structurename refers to a main or nested structure. It can be an element of an array of structures. Any variable reference that denotes a structure is acceptable. For example:
:emprec /* A simple structure */
:struct_array[i] /* An element of an array of structures */
:struct.minor2.minor3 /* A nested structure at level 3 */
• To use the final structure of the reference as a collection of variables, it must have no nested structures or arrays. The preprocessor enumerates all the members of the structure, which must have scalar values. The preprocessor generates code as though the program had listed each structure member in the order in which it was declared.
The following example uses the employee.dcl file generated by DCLGEN, to retrieve values into a structure.
Example: Structure variable usage
exec sql begin declare section;
exec sql include 'employee.dcl'; /* See above for
description */
exec sql end declare section;
exec sql select *
into :emprec
from employee
where eno = 123;
The example above generates code as though the following statement had been issued instead:
exec sql select *
into :emprec.eno, :emprec.ename, :emprec.age,
:emprec.job, :emprec.sal, :emprec.dept
from employee
where eno = 123;
The following example fetches the values associated with all the columns of a cursor into a record:
exec sql begin declare section;
exec sql include 'employee.dcl'; /* See above for
description */
exec sql end declare section;
exec sql declare empcsr cursor for
select *
from employee
order by ename;
...
exec sql fetch empcsr into :emprec;
The next example inserts values by looping through a locally declared array of structures whose elements have been initialized:
exec sql begin declare section;
exec sql declare person table
(pname char(30),
page integer1,
paddr varchar(50));
struct person_
{
char name[31];
short age;
char addr[51];
} person[10];
int i;
exec sql end declare section;
...
for (i = 0; i < 10; i++)
{
exec sql insert into person
values (:person[i]);
}
The insert statement in the example above generates code as though the following statement had been issued instead:
exec sql insert into person
values (:person[i].name, :person[i].age,
:person[i].addr);
Structure Member Usage
The syntax embedded SQL uses to refer to a structure member is the same as in C:
:structure.member{.member}
Syntax Notes:
• The structure member in the above statement must be a scalar value (integer, floating-point or character string). There can be any combination of arrays and structures, but the last object referenced must be a scalar value. Thus, the following references are all legal:
:employee.sal /* Member of a structure */
:person[3].name /* Element member of an array */
:structure.mem2.mem3.age /* Deeply nested member */
• Any array elements referred to within the structure reference, and not at the very end of the reference, are not checked by the preprocessor. Consequently, both of the following references are accepted, even though one must be wrong, depending on whether person is an array:
:person[1].age
:person.age
Structure references can also include pointers to structures. The arrow operator (->) denotes these structures. The preprocessor treats the arrow operator exactly like the dot operator and does not check that the arrow is used when referring to a structure pointer and that the dot is used when referring to a structure variable.
For example, the preprocessor accepts both of the following references to a structure, even though only the second one is legal C:
Struct
{
char *name;
int number;
} people[10], *one_person;
:people[i]->name /* Should use the dot operator */
:one_person->name /* Correct use of pointer
qualifier */
In general, the preprocessor supports unambiguous and direct references to structure members, as in the following example:
:ptr1->struct2.mem3[ind4]->arr5[ind6][ind7]
In this case, the last object denoted, arr5[ind6][ind7], must specify a scalar-valued object. References to structure variables cannot contain grouping parentheses. For example, assuming you declare struct1 correctly, the following reference causes a syntax error on the left parenthesis:
:(struct1.mem2)->num3
The only exception to this rule occurs when grouping a reference to the first and main member of a structure by starting the reference with a left parenthesis followed by an asterisk. Note that the two operators, "(" and "*" must be bound together without separating spaces, as in the following example:
:(*ptr1)->mem2
The following example uses the emprec structure that DCLGEN generates to put values into the empform form.
Example: Structure member usage
exec sql begin declare section;
struct emprec_ {
short eno;
char ename[21];
short age;
short job;
double sal;
short dept;
} emprec;
exec sql end declare section;
...
exec frs putform empform
(eno = :emprec.eno, ename = :emprec.ename,
age = :emprec.age, job = :emprec.job,
sal = :emprec.sal, dept = :emprec.dept);
Enumerated Variable (Envm) Usage
The syntax for referring to an enumerated variable or enumerated literal is the same as referring to a simple variable:
:enum_name;
Enumerated variables are treated as integer variables when referenced and you can use them to retrieve data from and assign data to Ingres. The enumerated literals are treated as declarations of integer constants and follow the same rules as integer constants declared with the # define statement. Use enumerated literals only to assign data to Ingres.
The following program fragment demonstrates a simple example of the enumerated type color.
Example: Enumerated variable usage
exec sql begin declare section;
exec sql declare clr table (num integer,color integer);
typedef enum {RED, WHITE, BLUE} color;
color col_var, *col_ptr;
static COLOR col_arr[3] = {BLUE, WHITE, RED};
int i;
exec sql end declare section;
/* Mapping from color to string */
static char *col_to_str_arr[3] = {"RED","WHITE", "BLUE"};
# define ctos(c) col_to_str_arr[(int)c]
/* Fill rows with color array */
for (i = 0; i < 3; i++)
exec sql insert into clr values (:i+1, :col_arr[i]);
/*
** Retrieve the rows - demonstrating a COLOR variable
** and pointer, and arithmetic on a stored COLOR value.
** Results are:
** [1] BLUE, RED
** [2] WHITE, BLUE
** [3] RED, WHITE
*/
col_ptr = &col_arr[0];
exec sql select num, color, color+1
into :i, :col_var, :*col_ptr
from clr;
exec sql begin;
printf("[%d] %s, %s\n", i, ctos(col_var),
ctos(*col_ptr%3));
exec sql end;
Varying Length String Variable (Varchar or Varbyte) Usage
The syntax for referring to a varchar (or varbyte) variable is the same as referring to a simple variable:
:varchar_name;
Syntax Notes:
• When using a variable declared with the varchar (or varbyte) storage class, you cannot reference the two members of the structure individually but only the structure as a whole. For example, the following declaration and select statement are legal:
varchar struct {
short buf_size;
char buf[100];
} vch;
select data into :vch from objects;
But the following statement generates an error on the use of the member "buf_size":
select data, length(data)
into :vch, :vch.buf_size
from objects;
• When you use the variable to retrieve Ingres data, the 2-byte length field is assigned the length of the data, and the data is copied into the fixed length character array. The data is not null-terminated. You can use a varchar (or varbyte) variable to retrieve data in the select, fetch, inquire_sql, getform, finalize, unloadtable, getrow, and inquire_frs statements.
• When you use the variable to set Ingres data, the program must assign the length of the data (in the character array) to the 2-byte length field. You can use a varchar (or varbyte) variable to set data in the insert, update, putform, initialize, loadtable, putrow, and set_frs statements.