1.6.1 Embedded variables and indicator variables

Organization of this subsection
(1) Format
(2) Function, uses, and specifiable locations
(3) Relationships between the data types of embedded and indicator variables and data descriptions in various languages
(4) Qualifying embedded variables and indicator variables
(5) Relationships between embedded variables and SQL data types

(1) Format

:embedded-variable[:indicator-variable]

(2) Function, uses, and specifiable locations

The following table lists the functions, uses, and specifiable locations of embedded variables and indicator variables.

Table 1-14 Functions, uses, and specifiable locations of embedded variables and indicator variables

FunctionUseSpecifiable locations
Embedded variableIndicator variable
Receipt of a column value as a retrieval resultReceives a value (other than the null value).#1Used in conjunction with an embedded variable to receive a column value, including the null value. In such a case, the indicator variable indicates whether the value read into the embedded variable is the null value. When character data, national character data, mixed character data, or large object data is received, the indicator variable indicates whether the data has been received correctly in the embedded variable.INTO clause of SELECT, FETCH, EXECUTE, or EXECUTE IMMEDIATE statement
Altering a literal valueSpecifies a value (other than the null value).#1When used in conjunction with an embedded variable to specify the null value, indicates whether the value of the embedded variable to be passed to SQL is the null value.In place of a literal when the value of the literal specified in the SQL statement is modified at the time of execution.
Specifying a value for a ? parameterSpecifies a value (other than the null value).#1When used in conjunction with an embedded variable to specify the null value, indicates whether the value of the embedded variable to be passed to SQL is the null value.To specify a value in the ? parameter specified in the SQL that is prepared in the PREPARE statement or that is prepared and executed in the EXECUTE IMMEDIATE statement, specify this item in the USING clause of the EXECUTE, OPEN, or EXECUTE IMMEDIATE statement.#2
Altering an embedded variableSpecifies another embedded variable in place of an embedded variable in a SELECT statement that is specified in a cursor declaration.--USING clause of OPEN statement
Specifying an SQL character stringSpecifies an SQL character string to prepare and execute an SQL statement that was generated when the UAP was executed.--PREPARE or EXECUTE IMMEDIATE statement
Specifying an authorization identifier and passwordIndicates to HiRDB the user executing the UAP.--Authorization identifier and password in CONNECT statement

--: Not applicable.

#1: To handle the null value, an indicator variable must also be specified; see 1.6.5 Setting a value for an indicator variable below for the values of indicator variables.

#2: An indicator variable cannot be specified in the USING clause of the OPEN statement.

(3) Relationships between the data types of embedded and indicator variables and data descriptions in various languages

When you are coding embedded variables and indicator variables in a UAP, data can be exchanged between an SQL and the UAP without causing a data conversion if certain relationships hold between the data types and data descriptions involved. For those relationships, see the HiRDB Version 9 UAP Development Guide.

(4) Qualifying embedded variables and indicator variables

(a) COBOL language

COBOL allows the qualifying of embedded variables and indicator variables with a group item.

Group item qualification format:

:[variable-name-1.] variable-name-2

Any qualified embedded variable or indicator variable should be specified so that the result of qualification is unique. Names that need not be qualified can be qualified. If there are several possible combinations of qualifiers that result in a unique qualification, any of such combinations can be used.

variable-name-1 is the group item to which variable-name-2 is subordinate.

(b) C language

C allows the qualifying of a member of a structure with a structure or a pointer to a structure.

Structure qualification format:

: structure-name.member-name

Pointer qualification format:

: pointer-name-> member-name

(5) Relationships between embedded variables and SQL data types

The following table lists the assignment relationships between UAP embedded variables and SQL data types.

Table 1-15 Assignment relationships between UAP embedded variables and SQL data types

Embedded variable data typeSQL data type
Character dataNational character dataMixed character data
Character dataY--Y
National character data--Y--
Mixed character dataY--Y

Y: Can be assigned.

--: May be assigned; care must be exercised because assignment may occur regardless of the data type (see Note 2 and Note 3).

Note 1
Character data, national character data, and mixed character data include fixed-length and variable-length formats.
Note 2
If the data does not fill the field, the unused part of the field (on the right) is filled with spaces as follows.
  • When a UAP embedded variable is assigned to an SQL data type:
    The field is filled with spaces from the SQL data type. If a character set is specified, the space from that character set is used. For the national character data type, a double-byte space is used.
  • When an SQL data type is assigned to a UAP embedded variable:
    The field is filled with spaces from the UAP embedded variable. If a character set is specified, the space from that character set is used. For the national character data type, a double-byte space is used.
However, when data is assigned to the national character data type, the national character spaces in that data are also converted if a space conversion level is set in the pd_space_level operand of the system common definition or in PDSPACELVL in the client environment definition. As a result, the national character spaces may be converted into two single-byte spaces.
Note 3
If there are both regular spaces and one-byte spaces in national character data, operations such as a comparison that includes spaces may not execute as expected.
Note 4
One-byte characters should not be assigned to national character data; if an attempt is made to do so, the results cannot be predicted.
Note 5
When the LIKE predicate is used, special pattern characters must be in accordance with the SQL data type specification.
Note 6
If you specify utf-8 or chinese-gb18030 as the character code classification in the pdntenv command (pdsetup command in the UNIX edition), you cannot assign to an embedded variable that uses the national character data type. Similarly, an assignment cannot be made from the national character data type of an embedded variable to the character data type or the mixed character data type of SQL.