Variable Usage
C variables that you declare in an embedded SQL declaration section can substitute for most elements of embedded SQL statements that are not keywords. Of course, the variable and its data type must make sense in the context of the element. When you use a C variable in an embedded SQL statement, precede it with a colon. You must further verify that the statement using the variable is in the scope of the variable's declaration. As an example, the following select statement uses the variables namevar and numvar to receive data, and the variable idno as an expression in the where clause.
Example: C variables usage
exec sql select ename, eno
into :namevar, :numvar
from employee
where eno = :idno;
into :namevar, :numvar
from employee
where eno = :idno;
Various rules and restrictions apply to the use of C variables in embedded SQL statements. The following sections describe the usage syntax of different categories of variables and provide examples of such use.
Simple Variables
The following syntax refers to a simple scalar-valued variable (integer, floating-point or character string):
:simplename
Syntax Notes:
• If you use the variable to send values to the database, or a field on a form, it can be any scalar-valued variable or # define constant, enumerated variable or enumerated literal.
• If you use the variable to receive values from the database or a field on a form, it can only be a scalar-valued variable or enumerated variable. Character strings that you declare as:
char *character_string_pointer;
or:
char character_string_buffer[];
are considered scalar-valued variables and must not include any indirection when referenced. External compiled forms that are declared as:
extern int *compiled_formname; (Linux)
should not include any indirection when referenced in the addform statement:
exec frs addform :compiled_formname;
The following program fragment demonstrates a typical message handling routine. It passes two scalar-valued variables as parameters: "buffer", a character string, and "seconds", an integer variable.
Example: Simple variables usage
Print_Message(buffer, seconds)
exec sql begin declare section;
char *buffer;
short seconds;
exec sql end declare section;
{
exec frs message :buffer;
exec frs sleep :seconds;
...
}
exec sql begin declare section;
char *buffer;
short seconds;
exec sql end declare section;
{
exec frs message :buffer;
exec frs sleep :seconds;
...
}
Note: Ingres supports Unicode using 2-byte Universal Character Set (UCS-2), representing Unicode code points in 16 bits (two octets). Embedded C for Ingres allows for variables of the C data type wchar_t to contain Ingres Unicode data. The C Standard does not specify a size for the wchar_t data type, however, if the compilation platform uses at least 16 bits for the data type wchar_t, it can be used for Ingres embedded C programs. When Ingres updates variables of the type wchar_t, only the low 16 bits are used; any extra high bits are set to zero. When Ingres reads values from wchar_t variables, only the low 16 bits are used and any extra high bits are ignored.
Array Variables
The following syntax refers to an array variable:
:arrayname [subscript] {[subscript]}
Syntax Notes:
• You must subscript the variable, because only scalar-valued elements (integers, floating-point and character strings) are legal SQL values.
• When you reference the array, the number of indices is noted but the embedded SQL preprocessor does not parse the subscript values. Consequently, even though the preprocessor confirms that you used the correct number of array indirections, the preprocessor accepts illegal subscript values. You must make sure that the subscript is legal. For example, the preprocessor accepts both of the following references, even though only the first is correct:
float salary_array[5];
:salary_array[0]
:salary_array[+-1-+]A character string, declared as an array of characters, is not considered an array and cannot be subscripted in order to reference a single character. In fact, single characters are illegal string values, as all character string values must be null-terminated.
:salary_array[0]
:salary_array[+-1-+]A character string, declared as an array of characters, is not considered an array and cannot be subscripted in order to reference a single character. In fact, single characters are illegal string values, as all character string values must be null-terminated.
For example, if the following variable were declared:
static char abc[3] = {'a', 'b', 'c'};
you could not access the character "a" with the reference:
:abc[0]
To perform such a task, declare the variable as an array of three single character strings:
static char *abc[3] = {"a","b","c"};
• As with standard C, any variable that can be denoted with array subscripting can also be denoted with pointers. This is because the preprocessor only records the number of indirection levels used when referencing a variable. The indirection level is the sum of the number of pointer operators preceding the variable reference name and the number of array subscripts following the name. For example, if a variable is declared as an array:
int age_set[2];
it can be referenced as either an array:
:age_set[0]
or a pointer:
:*age_set
• Do not precede references to elements of an array with the ampersand operator (&) to denote the address of the element.
• Any arrays of indicator variables that you use with structure assignments must not include subscripts.
The following example uses the variable "i" as a subscript. This variable does not need to be declared in the declaration section, as it is not parsed.
Example: Variable as subscript usage
exec sql begin declare section;
char *formnames[3={"empform","deptform","helpform"};
exec sql end declare section;
int I;
for (i = 0; i < 3; i++)
exec frs forminit :formnames[i];
char *formnames[3={"empform","deptform","helpform"};
exec sql end declare section;
int I;
for (i = 0; i < 3; i++)
exec frs forminit :formnames[i];
Pointer Variables
The following syntax refers to a pointer variable:
:*{*}pointername
Syntax Notes:
• Refer to the variable indirectly, because only scalar-valued elements (integers, floating-point, and character strings) are legal SQL values.
• When you declare the variable, the preprocessor notes the number of preceding asterisks. Later references to the variable must have the same indirection level. The indirection level is the sum of the number of pointer operators (asterisks) preceding the variable declaration name and the number of array subscripts following the name.
• A character string, declared as a pointer to a character, is not considered a pointer and cannot be subscripted in order to reference a single character. As with arrays, single characters are illegal string values because any character string value must be null-terminated. For example, assuming the following declaration:
char *abc = "abc";
you could not access the character "a" with the reference:
:*abcExternal compiled forms that you declare as:
Linux:
extern int *compiled_formname; 
These external compiled forms must not include any indirection when referenced in the addform statement.
• As with standard C, any variable that you can denote with pointer indirection can also be denoted with array subscripting. This is true because the preprocessor only records the number of indirection levels used when referencing a variable. For example, if you declare a variable as a pointer:
int *age_pointer;
it can be referenced as either a pointer:
:*age_pointer;
or an array:
:age_pointer[0];
The following example uses a pointer to insert integer values into a database table.
Example: Pointer variable usage
exec sql begin declare section;
int *numptr;
exec sql end declare section;
static int numarr[6] = {1, 2, 3, 4, 5, 0};
for (numptr = numarr; *numptr; numptr++)
exec sql insert into items (number) values (:*numptr);
int *numptr;
exec sql end declare section;
static int numarr[6] = {1, 2, 3, 4, 5, 0};
for (numptr = numarr; *numptr; numptr++)
exec sql insert into items (number) values (:*numptr);
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 */
: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 include 'employee.dcl'; /* See above for
description */
exec sql end declare section;
exec sql select *
into :emprec
from employee
where eno = 123;
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;
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;
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]);
}
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);
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 */
: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
: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 */
{
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);
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;
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;
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;
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.
Indicator Variables Usage
The syntax for referring to an indicator variable is the same as for a simple variable, except that an indicator variable is always associated with a host variable:
:host_variable:indicator_variable;
or
:host_variable indicator :indicator_variable;
Syntax Notes:
• The indicator variable can be a simple variable, an array element or a structure member that yields a short integer. For example:
short ind_var, *ind_ptr, ind_arr[5];
:var_1:ind_var
:var_2:*ind_ptr
:var_3:ind_arr[2]
:var_1:ind_var
:var_2:*ind_ptr
:var_3:ind_arr[2]
• If the host variable associated with the indicator variable is a structure, the indicator variable should be an array of short integers. In this case, the array should not be dereferenced with a subscript.
• When you use an indicator array, the first element of the array corresponds to the first member of the structure, the second element to the second member, and so on. Array elements begin at subscript 0, and not at 1 as in other languages.
The following example uses the employee.dcl file that DCLGEN generated to retrieve values into a structure and null indicators into the empind array:
exec sql begin declare section;
exec sql include 'employee.dcl';
/* See above for description */
short empind[10];
exec sql end declare section;
exec sql select *
into :emprec:empind
from employee;
exec sql include 'employee.dcl';
/* See above for description */
short empind[10];
exec sql end declare section;
exec sql select *
into :emprec:empind
from employee;
The above example generates code as though the following statement had been issued:
exec sql select *
into:emprec.eno:empind[0], :emprec.ename:empind[1],
:emprec.age:empind[2], :emprec.job:empind[3],
:emprec.sal:empind[4], :emprec.dept:empind[5],
from employee;
into:emprec.eno:empind[0], :emprec.ename:empind[1],
:emprec.age:empind[2], :emprec.job:empind[3],
:emprec.sal:empind[4], :emprec.dept:empind[5],
from employee;
Varchar Variables for Logical Key Data Types
It is recommended that you use varchar variables to retrieve or insert Ingres logical key data types instead of char(8) or char(16) compatible variables. If logical key data contain embedded nulls, the Ingres runtime system may not be able to detect the end-of-string terminator on char variables; using varchar will eliminate this confusion between null-terminated strings and null data. System maintained logical keys are very likely to contain binary data including null bytes; therefore, you should always use a varchar variable when dealing with system maintained logical keys.
Example: Varchar variable usage
exec sql begin declare section;
exec sql declare keytab table
(tkey table_key with system_maintained,
okey object_key with sytem_maintained,
row integer);
exec sql declare savetab table
(tsave table_key not system_maintained,
osave object_key not system_maintained);
#define tablen 8 /* Table_key length */
#define objlen 16 /* Object_key length */
varchar struct
{
short obj_len;
char obj_data[OBJLEN]];
} objvar;
varchar struct
{
short tab_len;
char tab_data[TABLEN];
} tabvar;
int indx;
short tabind, objind;
exec sql end declare section;
. .
exec sql insert into keytab (row) values (1);
/*
** Retrieve the table key and object key values
** that were just inserted by the system. Then
** INSERT the table key and object key values into
** another table with non-system maintained logical keys.
*/
exec sql declare keytab table
(tkey table_key with system_maintained,
okey object_key with sytem_maintained,
row integer);
exec sql declare savetab table
(tsave table_key not system_maintained,
osave object_key not system_maintained);
#define tablen 8 /* Table_key length */
#define objlen 16 /* Object_key length */
varchar struct
{
short obj_len;
char obj_data[OBJLEN]];
} objvar;
varchar struct
{
short tab_len;
char tab_data[TABLEN];
} tabvar;
int indx;
short tabind, objind;
exec sql end declare section;
. .
exec sql insert into keytab (row) values (1);
/*
** Retrieve the table key and object key values
** that were just inserted by the system. Then
** INSERT the table key and object key values into
** another table with non-system maintained logical keys.
*/
exec sql inquire_sql (:tabvar:tabind = table_key,
:objvar:objind= object_key);
if (tabind == -1 || objind == -1)
printf ("No logical key values available.\n");
else
exec sql insert into savetab (tsave, osave)
values (table_key(:tabvar), object_key(:objvar));
/*
** Select data from a table that contains logical key
** data types.
*/
exec sql select tsave, osave into :tabvar, :objvar
from savetab;
exec sql begin;
/*Print out the table key value in Hex */
printf (" Table key value = 0x");
for (indx = 0; indx < tabvar.tab_len; indx++)
{
printf ("%02x", (unsigned char)
tabvar.tab_data[indx]);
}
printf ("\n");
exec sql end;
:objvar:objind= object_key);
if (tabind == -1 || objind == -1)
printf ("No logical key values available.\n");
else
exec sql insert into savetab (tsave, osave)
values (table_key(:tabvar), object_key(:objvar));
/*
** Select data from a table that contains logical key
** data types.
*/
exec sql select tsave, osave into :tabvar, :objvar
from savetab;
exec sql begin;
/*Print out the table key value in Hex */
printf (" Table key value = 0x");
for (indx = 0; indx < tabvar.tab_len; indx++)
{
printf ("%02x", (unsigned char)
tabvar.tab_data[indx]);
}
printf ("\n");
exec sql end;
How Function Arguments are Declared
If you intend to use function arguments in ESQL statements, you must declare the variable to the ESQL/C compiler. In non-ANSI style C functions, you can declare function arguments directly.
Example: Function arguments usage (non-ANSI style functions)
void myfunct(arg1, arg2)
exec sql begin declare section;
int arg1;
exec sql end declare section;
int arg2;
exec sql begin declare section;
int arg1;
exec sql end declare section;
int arg2;
In ANSI style functions, you cannot use the function argument variable directly. You must declare a local variable for use in ESQL statements, and copy the value from the function argument to the variable.
Example: Function arguments usage (ANSI style functions)
void myANSIfunct(int arg1, int arg2)
exec sql begin declare section;
int localarg1;
exec sql end declare section;
int localarg2;
localarg1 = arg1;
/* Now use localarg1 in your ESQL statements */
...
exec sql begin declare section;
int localarg1;
exec sql end declare section;
int localarg2;
localarg1 = arg1;
/* Now use localarg1 in your ESQL statements */
...
Last modified date: 04/03/2024