VALUES INTO

The VALUES INTO statement assigns one or more values 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

The privileges that are held by the authorization ID of the owner of the plan or package must include at least one of the following for every table and view identified in the statement:

The SELECT privilege on the table or view
Ownership of the table or view
DBADM authority for the database (tables only)
DATAACCESS authority
SYSADM authority
SYSCTRL authority (catalog tables only)

Authorization is required for any expressions that are used in the statement. For more information, see Expressions.

Syntax


>>-VALUES--+-+-CURRENT PACKAGESET---+-+--INTO------------------->
           | +-CURRENT PACKAGE PATH-+ |         
           | +-CURRENT SERVER-------+ |         
           | +-expression-----------+ |         
           | '-NULL-----------------' |         
           |   .-,--------------.     |         
           |   V                |     |         
           '-(---+-expression-+-+-)---'         
                 '-NULL-------'                 

   .-,---------------.   
   V                 |   
>----target-variable-+-----------------------------------------><

target-variable

|--+-host-variable-name-+---------------------------------------|
   +-SQL-parameter-name-+   
   '-SQL-variable-name--'

Description

VALUES

Introduces a single row that consists of one or more columns. If more than one value is specified, the list of values must be enclosed within parentheses.

expression: The expression is any expression of the type described in Expressions. In a native SQL routine, the expression can contain a reference to the CURRENT PACKAGESET, CURRENT PACKAGE PATH, or CURRENT SERVER special registers if target-variable is an SQL parameter or SQL variable. The expression must not include a column name.
NULL: The null value. NULL can only be specified for host variables that have an associated indicator variable.

INTO

Identifies one or more targets for the assignment of output values. The number of targets in the INTO clause must equal the number of values that are to be assigned. The first value in the result row is assigned to the first target in the list, the second value to the second target, and so on. A target variable must not be specified more than once in the INTO clause. Each assignment to a target is made in sequence through the list, according to the rules described in Assignment and comparison.

The value 'W' is assigned to the SQLWARN3 field of the SQLCA if the number of targets is less than the number of result column values.

If an error occurs on any assignment, the value is not assigned to the target, and no more values are assigned to the specified targets. Any values that have already been assigned remain assigned.

host-variable-name: Identifies the host variable that is the assignment target. For LOB output values, the target can be a regular host variable (if it is large enough), a LOB locator variable, or a LOB file reference variable.
SQL-parameter-name: Identifies the parameter that is the assignment target.
SQL-variable-name: Identifies the SQL variable that is the assignment target. SQL variables must be declared before they are used.

Notes

Default encoding scheme:: The default encoding scheme for the data is the value in the bind option ENCODING, which is the option for application encoding. If this statement is used with functions such as LENGTH or SUBSTRING that are operating on LOB locators, and the LOB data that is specifies by the locator is in a different encoding scheme from the ENCODING bind option, LOB materialization and character conversion occur. To avoid LOB materialization and character conversion, select the LOB data from the SYSIBM.SYSDUMMYA, SYSIBM.SYSDUMMYE, or SYSIBM.SYSDUMMYU sample table.

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: Assign the value of the CURRENT MEMBER special register to host variable MEM.

   EXEC SQL VALUES(CURRENT MEMBER)
            INTO :MEM;

Example 3: 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;

If the LOB data that is specified by the LOB locator LOB1 is in a different encoding scheme from the value of the ENCODING bind option, and you want to avoid LOB materialization and character conversion, use the following statement instead of the VALUES INTO statement:

   EXEC SQL SELECT SUBSTR(:LOB1,1,35)
            INTO :DETAILS
            FROM SYSIBM.SYSDUMMYU;