VALUES INTO

The VALUES INTO statement produces a result table consisting of at most one row and assigns the values in that row to host variables.

Invocation

This statement can only be embedded in an application program. It is an executable statement that cannot be dynamically prepared.

Authorization

If an expression includes a function, the authorization ID of the statement must include at least one of the following for each user-defined function:

• The EXECUTE privilege on the function
• Administrative authority

The authorization ID of the statement has the EXECUTE privilege on a function when:

• It is the owner of the function,
• It has been granted the EXECUTE privilege on the function, or
• It has been granted the system authorities of *OBJOPR and *EXECUTE on the function.

If a row-subselect is specified, see Queries for an explanation of the authorization required for each subselect.

Syntax

.-,----------------. V | >>-VALUES--+-+-expression--------------+-------------------+---INTO-----host-variable---+-> | +-NULL--------------------+ | | '-(--scalar-subselect--)--' | | .-,----------------------------. | | V | | '-(--+----+-expression--------------+--+-+---)--' | +-NULL--------------------+ | | '-(--scalar-subselect--)--' | '-row-subselect---------------------' >--------------------------------------------------------------><

Description

VALUES

Introduces a single row consisting of one of more columns.

expression

Specifies the new value of the host variable. The expression is any expression of the type described in "Expressions". It must not include a column name. Host structures are not supported.

NULL

Specifies that the new value for the host variable is the null value.

scalar-subselect

A subselect that returns a single result row and a single result column. The result column value is assigned to the corresponding host-variable. If the result of the subselect is no rows, then the null value is assigned. An error is returned if there is more than one row in the result.

row-subselect

A subselect that returns a single result row. The result column values are assigned to each corresponding host-variable. If the result of the subselect is no rows, then null values are assigned. An error is returned if there is more than one row in the result.

INTO

Introduces a list of host variables and host structures. The first value in the result row is assigned to the first host variable in the list, the second value to the second host variable, and so on. The data type of each host variable must be compatible with its corresponding result column. Each assignment is made according to the rules described in Assignments and Comparisons. If there are fewer host variables than values, the value 'W' is assigned to the SQLWARN3 field of the SQLCA. (See Appendix B, SQL Communication Area.) Note that there is no warning if there are more variables than the number of result columns. If the value is null, an indicator variable must be provided. If an assignment error occurs, the value is not assigned to the variable, and no more values are assigned to variables. Any values that have already been assigned to variables remain assigned.

If an error occurs as the result of an arithmetic expression in the expression or SELECT list of the subselect (division by zero, or overflow) or a character conversion error occurs, the result is the null value. As in any other case of a null value, an indicator variable must be provided. The value of the host variable is undefined. In this case, however, the indicator variable is set to -2. Processing of the statement continues as if the error had not occurred. (However, this error causes a positive SQLCODE.) If you do not provide an indicator variable, a negative value is returned in the SQLCODE field of the SQLCA. It is possible that some values have already been assigned to host variables and will remain assigned when the error occurs.

host-variable, ...

Identifies one or more host structures or host variables that must be declared in accordance with the rules for declaring host structures and host variables, see References to Host Variables. In the operational form of INTO, a host structure is replaced by a reference to each of its variables.

Notes

If an error occurs, no value is assigned to the current host variable. However, if LOB values are involved, there is a possibility that the corresponding host variable was modified, but the variable's contents are unpredictable.

If the specified host variable is character and is not large enough to contain the result, 'W' is assigned to SQLWARN1 in the SQLCA. The actual length of the result is returned in the indicator variable associated with the host-variable, if an indicator variable is provided.

If the specified host variable is a C NUL-terminated host variable and is not large enough to contain the result and the NUL-terminator:

• If the *CNULRQD option is specified on the CRTSQLCI or CRTSQLCPPI command (or CNULRQD(*YES) on the SET OPTION statement), the following occurs:
  • The result is truncated.
  • The last character is the NUL-terminator.
  • The value 'W' is assigned to SQLWARN1 in the SQLCA.
• If the *NOCNULRQD option on the CRTSQLCI or CRTSQLCPPI command (or CNULRQD(*NO) on the SET OPTION statement) is specified, the following occurs:
  • The NUL-terminator is not returned.
  • The value 'N' is assigned to SQLWARN1 in the SQLCA.

Examples

Example 1

Assign the value of the CURRENT PATH special register to host variable HV1.

   EXEC SQL VALUES CURRENT PATH INTO :HV1;

Example 2

Assume that LOB locator LOB1 is associated with a CLOB value. Assign a portion of the CLOB value to host variable DETAILS using the LOB locator.

   EXEC SQL VALUES (SUBSTR(:LOB1,1,35)) INTO  :DETAILS;