1.5.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) Search using a variable-length character string variable of a character string item whose actual length is 0
(6) Relationships between embedded variables and SQL data types

(1) Format

:embedded-variable[:indicator-variable]

(2) Function, uses, and specifiable locations

Table 1-12 shows the functions of embedded variables and indicator variables, their uses, and where in a program they can be specified.

Table 1-12 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 or not 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 or not 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 or not 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 or not 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.[Figure]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.[Figure]PREPARE or EXECUTE IMMEDIATE statement
Specifying an authorization identifier and passwordIndicates to HiRDB the user executing the UAP.[Figure]Authorization identifier and password in CONNECT statement
Specifying an RD-node nameSpecifies the RD-node at the connection destination.[Figure]RD-node name in CONNECT statement with RD-node specification, DISCONNECT statement with RD-node specification, or SET CONNECTION statement

[Figure]: Not applicable.

1 To handle the null value, an indicator variable must also be specified; see 1.5.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 8 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) Search using a variable-length character string variable of a character string item whose actual length is 0

In the case of remote database access, if a distributed client that does not support 0 as the actual length of a variable-length character string (including national and mixed) variable uses a variable-length character string variable to search a character string whose actual length is 0, spaces of actual length 1 will be set as the search result.

(6) Relationships between embedded variables and SQL data types

Table 1-13 shows the substitution relationships between UAP embedded variables and SQL data types.

Table 1-13 Substitution relationships between UAP embedded variables and SQL data types

Embedded variable's data typeSQL's data type
Character dataNational character dataMixed character data
Character dataY[Figure]Y
National character data[Figure]Y[Figure]
Mixed character dataY[Figure]Y

Y: Can be substituted.

[Figure]: May be substituted; care must be exercised because substitution 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
The following rules apply when the data item is short and the data has trailing spaces:
  • If character data or mixed character data in the embedded variable is substituted into national character data in the SQL data, the system fills out the national character data with trailing spaces.
  • If national character data in the embedded variable is substituted into character data or mixed character data in the SQL data, the system fills out the character data or mixed character data with trailing one-byte spaces.
  • If character data or mixed character data in the SQL data is substituted into national character data in the embedded variable, the system fills out the national character data with trailing one-byte spaces.
  • If national character data in the SQL data is substituted into character data or mixed character data in the embedded variable, the system fills out the character data or mixed character data with trailing one-byte spaces.
  • If character data or mixed character data in the embedded variable is substituted into character data or mixed character data in the SQL data, the system fills out the character data or mixed character data in the SQL data with trailing one-byte spaces.
  • If national character data in the embedded variable is substituted into national character data in the SQL data, the system fills out the national character data in the SQL data with trailing one- or two-byte spaces (depending on the language).
  • If character data or mixed character data in the SQL data is substituted into character data or mixed character data in the embedded variable, the system fills out the character data or mixed character data in the embedded variable with trailing one- or two-byte spaces (depending on the language).
  • If national character data in the SQL data is substituted into national character data in the embedded variable, the system fills out the national character data in the embedded variable with trailing one- or two-byte spaces (depending on the language). If, however, a space conversion level is set in PDSPACELVL in the client environment definition or in the pd_space_level operand in the system common definition, the added national character spaces will also be subject to the specified conversion. As a result, the national character spaces may be converted into double-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 substituted into 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 the character code type UTF-8 is specified in the pdntenv command (pdsetup command in the UNIX version), data cannot be assigned from the character data type or the mixed character data type of SQL to the national character data type of an embedded variable. 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.