|
|
|
Embedded SQL allows the use of host language variables for many elements of embedded SQL statements. Host language variables can be used to transfer data between the database and the program or to specify the search condition in a where clause.
In embedded SQL statements, host language variables can be used to specify the following elements:
A host language variable can be a simple variable or a structure. All host language variables must be declared to embedded SQL before using them in embedded SQL statements.
For a full discussion of the use of host language variables in embedded SQL, see the Embedded SQL Companion Guide.
|
|
|
Host language variables must be declared to SQL before using them in any embedded SQL statements. Host language variables are declared to SQL in a declaration section that has the following syntax:
exec sql begin declare section;
host language variable declaration
exec sql end declare section;
A program can contain multiple declaration sections. The preprocessor treats variables declared in each declaration section as global to the embedded SQL program from the point of declaration onward, even if the host language considers the declaration to be in local scope.
The variable declarations are identical to any variable declarations in the host language, however, the data types of the declared variables must belong to a subset of host language data types that embedded SQL understands.
The DBMS Server automatically handles the conversion between host language numeric types and SQL numeric types, as well as the conversion between host language character string types and SQL character string types. To convert data between numeric and character types, use one of the explicit conversion functions described in Default Type Conversion in the chapter "Understanding the Elements of SQL Statements." For a list of the data types acceptable to embedded SQL and a discussion of data type conversion, see the Embedded SQL Companion Guide.
Note: Host language variables that are not declared to SQL are not processed by the ESQL preprocessor and therefore can include data types that the preprocessor does not understand.
|
|
|
The embedded SQL include statement allows external files to be included in your source code. The syntax of the include statement is:
exec sql include filename
This statement is commonly used to include an external file containing variable declarations.
For example, assuming you have a file called, myvars.dec, that contains a group of variable declarations, use the include statement in the following manner:
exec sql begin declare section;
exec sql include 'myvars.dec';
exec sql end declare section;
This is the functional equivalent of listing all the declarations in the myvars.dec file in the declaration section itself.
|
|
|
After host language variables are declared, use them in your embedded statements. In embedded SQL statements, host language variables must be preceded by a colon, for example:
exec sql select ename, sal
into :name, :salary
from employee
where eno = :empnum;
The into clause contains two host language variables, name and salary, and the where clause contains one, empnum.
A host language variable can have the same name as a database object, such as a column. The preceding colon distinguishes the variable from an object of the same name.
If no value is returned (for example, no rows qualified in a query), the contents of the variable are not modified.
|
|
|
To simplify data transfer in and out of database tables, embedded SQL allows the usage of variable structures in the select, fetch, update, and insert statements. Structures must be specified according to the rules of the host language and must be declared in an embedded SQL declare section. For structures to be used in select, insert, and update statements, the number, data type, and order of the structure elements must correspond to the number, data type, and order of the table columns in the statement.
For example, if you have a database table, employee, with the columns, ename (char(20)) and eno (integer), you can declare the following variable structure:
emprec,
ename character_string(20),
eno integer;
and issue the following select statement:
exec sql select *
into :emprec.ename, :emprec.eno
from employee
where eno = 23;
It is also legal to specify only the structure name in the statement. If this is done, each variable structure must correspond to the table specified in the statement. The number, data type, and order of the structure elements must correspond to the number, data type, and order of the table columns in the statement.
exec sql select *
into :emprec
from employee
where eno = 23;
The embedded SQL preprocessor expands the structure name into the names of the individual members. Placing a structure name in the into clause has the same effect as enumerating all members of the structure in the order in which they were declared.
A structure can also be used to insert values in the database table. For example:
exec sql insert into employee (ename,eno)
values (:emprec);
For details on the declaration and use of variable structures, see the Embedded SQL Companion Guide.
|
|
|
The dclgen utility (declaration generator utility) is a structuregenerating utility that maps the columns of a database table into a structure that can be included in a variable declaration.
The dclgen utility can be invoked from the operating system level by executing the following command:
dclgen language dbname tablename filename structurename
Defines the embedded SQL host language.
Defines the name of the database containing the table.
Defines the name of the database table.
Defines the output file generated by dclgen containing the structure declaration.
Defines the name of the generated host language structure.
This command creates the declaration file, filename, containing a structure corresponding to the database table. The file also includes a declare table statement that serves as a comment and identifies the database table and columns from which the structure was generated. Once the file has been generated, use an embedded SQL include statement to incorporate it into the variable declaration section. For details, see Declare Table.
For details on the dclgen utility, see the Embedded SQL Companion Guide or the Command Reference Guide.
|
|
|
An indicator variable is a 2-byte integer variable associated with a host language variable in an embedded SQL statement. Indicator variables enable an application to:
|
|
|
Like other host language variables, an indicator variable must be declared to embedded SQL in a declare section.
In an embedded SQL statement, the indicator variable is specified immediately after the host language variable, with a colon separating the two:
host_variable:indicator_variable
Or you can use the optional keyword indicator in the syntax:
host_variable indicator :indicator_variable
When used to detect or assign a null, indicator variables are commonly termed null indicator variables.
Specify indicator variables in association with host language variables that contain the following data:
For example, the following example associates null indicators with variables representing column values:
exec sql select ename, esal
into :name:name_null, :salary:sal_null
from employee;
|
|
|
When a null value is retrieved into a host language variable that has an associated indicator variable, the indicator variable is set to -1 and the value of the host language variable is not changed. If the value retrieved is not a null, the indicator variable is set to 0 and the value is assigned to the host language variable. If the value retrieved is null and the program does not supply a null indicator, an error results.
Null indicator variables can be associated with the following:
The following example illustrates the use of a null indicator when retrieving data. This example issues a fetch statement and updates a roster while checking for null phone numbers indicated by the variable, phone_null.
exec sql fetch emp_cursor into :name,
:phone:phone_null, :id;
if (phone_null = -1) then
update_roster(name, 'N/A', id);
else
update_roster(name, phone, id);
end if;
|
|
|
Use an indicator variable with a host language variable to specify the assignment of a null to a database column. (An indicator variable can also be used to assign a null to a form field or a table field column.) When the DBMS Server assigns the value from a host language variable to one of these objects, it checks the value of the host language variable's associated indicator variable.
If the indicator variable value is -1, a null is assigned to the object and ignores the value in the host language variable. If the indicator variable is any value other than -1, the value of the host language variable is assigned to the object.
If the indicator value is -1 and the object type is not nullable (such as a column created with the not null clause), an error results.
The following example demonstrates the use of an indicator variable and the null constant with the insert statement. For a description of the null constant, see Nulls in the chapter "SQL Data Types."
read name, phone number, and id from terminal;
if (phone = ' ') then
phone_null = -1;
else
phone_null = 0;
end if;
exec sql insert into newemp (name, phone, id,
comment)
values (:name, :phone:phone_null, :id, null);
This second example retrieves data from a form and updates the data in the database:
exec frs getform empform (:name:name_null = name,
:id:id_null = id);
exec sql update employee
set name = :name:name_null, id = :id:id_null
where current of emp_cursor;
Use null indicators to assign nulls in:
All constant expressions in the above clauses can include the keyword null. Wherever an indicator variable can be used to assign a null, you can use the keyword, null.
|
|
|
When a character string is retrieved into a host language variable too small to hold the string, the data is truncated to fit. (If the data was retrieved from the database, the sqlwarn1 field of the SQLCA is set to 'W'.)
If the host language variable has an associated indicator variable, the indicator is set to the original length of the data. For example, the following statement sets the variable, char_ind, to 6 because it is attempting to retrieve a 6-character string into a 3-byte host language variable, char_3.
exec sql select 'abcdef' into :char_3:char_ind;
Note: If a long varchar or long byte column is truncated into a host language variable, the indicator variable is set to 0. The maximum size of a long varchar or long byte column (two gigabytes) is too large a value to fit in an indicator variable.
|
|
|
You can use host structures with the select, fetch, and insert statements, wherever these statements allow host language variables to be used. An array of indicator variables can be used in conjunction with a host structure to detect whether a null has been assigned to an element of the host structure.
An indicator array is an array of 2-byte integers, and is typically declared in the same declare section as its associated host language variable structure. Each element of the indicator array acts as an indicator variable to the correspondingly ordered member of the host structure.
The following example declares a host language variable structure, emprec, and an associated indicator array, empind.
emprec
ename character(20),
eid integer,
esal float;
empind array(3)of short_integer;
The following example illustrates the use of a host structure and indicator array in an embedded statement:
exec sql select name, id, sal
into :emprec:empind
from employee
where number = 12;
In the preceding example, if the value of the employee id column is null, a value of -1 is assigned to the second element of the empind array.