VALUES INTO

Start of changeThe VALUES INTO statement assigns one or more values to variables.End of change

Invocation

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

Authorization

Start of changeThe privileges that are held by the authorization ID of the statement must include at least one of the following privileges or authorities:
  • The SELECT privilege on every table and view identified in the statement
  • Ownership of every table and view identified in the statement
  • READ and WRITE privileges on any global variables that are identified in the statement
  • Ownership of any global variables that are identified in the statement
  • DBADM authority for the database (tables only)
  • DATAACCESS authority
  • SYSADM authority
  • SYSCTRL authority (catalog tables only)
End of change

Start of changeFor an assignment to a global variable, the privilege set must include at least one of the following:End of change

Start of change
  • The WRITE privilege on the variable
  • Ownership of the variable
  • DATAACCESS authority
  • SYSADM authority
End of change

Start of changePrivilege set: The privilege set is the privileges that are held by the owner of the package.End of change

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

Syntax

Read syntax diagramSkip visual syntax diagramVALUES1CURRENT PACKAGESETCURRENT PACKAGE PATHCURRENT SERVER(,expression2NULL)INTO,target-variablearray-variable[array-index]3
target-variable
Read syntax diagramSkip visual syntax diagramglobal-variable-namehost-variable-nameSQL-parameter-nameSQL-variable-name
Notes:
  • 1 When the target is not an SQL parameter or an SQL variable that is used in a native SQL procedure or compiled SQL function, these special registers can be referenced only as a source value in this form of the syntax for this statement.
  • 2 If expression is an array-constructor, it must not be the only source value specification in the list.
  • 3 The number of source value specifications (expression, NULL, or DEFAULT) on the right side of the equal sign must match the number of target specifications on the left side of the statement.

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.
Start of changeINTO target-variable or array-variable[array-index]End of change
Start of changeIdentifies 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.

global-variable-name
Identifies the global variable that is the assignment target.
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.
array-variable [array-index]
Specifies an array element that is the target of the assignment.

An array element must not be specified as the target for an assignment if common-table-expression is also specified in the statement.

array-variable
Specifies an array variable.
[array-index]
An expression that specifies which element in the array is the target of the assignment.

For an ordinary array, the array index expression must be castable to INTEGER, and must not be the null value. The index value must be between 1 and the maximum cardinality that is defined for the array.

For an associative array, the array index expression must be castable to the index data type of the associative array, and must not be the null value.

array-index must not be:

  • An expression that references the CURRENT DATE, CURRENT TIME, or CURRENT TIMESTAMP special register
  • A nondeterministic function
  • A function that is defined with EXTERNAL ACTION
  • A function that is defined with MODIFIES SQL DATA
  • A sequence expression
End of change

Notes

Start of changeAssignment to targets:End of change
Start of changeStart of changeThe nth target identified by the INTO clause corresponds to the nth column of the result table of the cursor. The data type of the target must be compatible with its corresponding value. If the value is numeric, the target must have the capacity to represent the whole part of the value. For a datetime value, the target must be a character string variable of a minimum length as defined in String representations of datetime values. When the value that is to be assigned is null, an indicator variable must be specified for the target variable.End of change

Start of changeAssignments are made in sequence through the list. Each assignment to a target is made according to the rules described in Language elements. If the number of targets is less than the number of values in the row, the SQLWARN3 field of the SQLCA is set to 'W'. There is no warning if there are more targets than the number of result columns. If a null value is assigned to a target variable, an indicator variable must be provided. If an assignment error occurs, the value is not assigned to the target and no more values are assigned to targets. Any values that have already been assigned to targets remain assigned. However, if LOB values are involved, there is a possibility that the corresponding target was modified, but the variable's contents are unpredictable.End of change

Start of changeIf more than one assignment is included in the same assignment statement, all expressions are evaluated before the assignments are performed. For example, a reference to a variable in an expression always uses the value of the variable prior to any assignment in the assignment statement.End of change

Start of changeNormally, you use LOB locators to assign and retrieve data from LOB columns. However, because of compatibility rules, you can also use LOB locators to assign data to targets with other data types. For more information on using locators, see Saving storage when manipulating LOBs by using LOB locators.End of change

End of change
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;
Start of changeExample 4: Using a VALUES INTO statement, retrieve the value of INTVAR1 into an element in array MYINTARRAY1, which is indexed by the value of the expression INTCOL2+MYINTVAR+1.Start of change
VALUES INTVAR1 INTO MYINTARRAY1[INTCOL2+MYINTVAR+1];
End of changeEnd of change