iQuery - Retrieve SQL Variable (RTVSQLVAR)

Where allowed to run:
  • Batch program (*BPGM)
  • Interactive program (*IPGM)
  • Batch ILE CL module (*BMOD)
  • Interactive ILE CL module (*IMOD)
Threadsafe: No
Parameters
Examples
Error messages

The SQL iQuery Retrieve SQL Global Variable (RTVSQLVAR) command extracts the value of an SQL Global Variable and returns its value to the CL variable specified on the RTNVAR parameter. The CL variable may be character or numeric (*CHAR or *DEC). When a Decimal Global Variable is retrieved, the CL return variable may be *CHAR or *DEC. However when a *CHAR or *VARCHAR global variable is returned, it should be returned to a *CHAR CL variable. CL does not support TYPE(*DATE) at this time, so SQL DATE Global Variables should be retrieved into TYPE(*CHAR) LEN(10) CL variables.

Restrictions:

Top

Parameters

Keyword Description Choices Notes
SQLVAR SQL Variable name Qualified object name Required, Positional 1
Qualifier 1: SQL Variable name Simple name
Qualifier 2: Library (Schema) Simple name, *LIBL, *CURLIB
RTNVAR CL var to receive value Not restricted Required, Positional 2
Top

SQL Variable to retrieve (SQLVAR)

Specify the name of the SQL variable to be returned. The SQL name or the System name of the variable may be specified (up to 128 characters).

This is a required parameter.

Qualifier 1: SQL Variable to retrieve

simple-name
Specify the name of the SQL variable to be returned. The SQL name or the System name of the variable may be specified (up to 128 characters).

Qualifier 2: Library (Schema)

*LIBL
The current SQL PATH is used to search for the variable.
*CURLIB
The current library is used to qualify the SQL variable.
simple-name
Specify the name of any library/schema (up to 128 characters) to be used as the qualified SQL Schema that contains the SQL Variable.
Top

CL var to receive value (RTNVAR)

Specifies name of the CL variable that will receive the SQL Variable's value. When a Decimal SQL Variable is retrieved, this parameter may be either a *CHAR or *DEC CL variable. The SQL Variable is mapped ("Cast") to the format of the CL variable. When a Character or VarChar or other non-numeric SQL variable is retrieved, this parameter should be a *CHAR CL variable.

This is a required parameter.

CL Variable
Specify the name of the CL variable that will recieve the SQL global variable's value.
Top

Examples for RTVSQLVAR

Example 1: Simple Command Example

DCL &BALANCE TYPE(*DEC) LEN(9 2)
RTVSQLVAR  SQLVAR(ACCOUNTS_RECEIVABLE/V_BALANCEDUE) RTVVAR(&BALANCE)

This command retrieves the value for the V_BALANCEDUE SQL variable and stores it in the &BALANCE CL variable.

Example 2: Alpha SQL Variable Example

DCL &COMPANY TYPE(*CHAR) LEN(30)
RUNiQRY   SQL('Create Variable QGPL.V_COMPANY VARCHAR(50) +
               DEFAULT 'Chicago Division of American Software Co.')
RTVSQLVAR  SQLVAR(V_COMPANY_NAME) RTNVAR(&COMPANY)

This example creates the V_COMPANY_NAME variable in QGPL and then uses RTVSQLVAR to retrieve the V_COMPANY_NAME variable's value into the CL variable named &COMPANY. This example illustrates two things: (1) Creating variables and (2) Retrieving them in CL. Normally the SQL Variable would have been created external to "this" CL program.

Top

Error messages for RTVSQLVAR

*ESCAPE Messages

CPF9801
Object &2 in library &3 not found.
CPF9802
Not authorized to object &2 in &3.
CPF9803
Cannot allocate object &2 in library &3.
CPF9807
One or more libraries in library list deleted.
CPF9808
Cannot allocate one or more libraries on library list.
CPF9810
Library &1 not found.
CPF9811
Program &1 in library &2 not found.
CPF9812
File &1 in library &2 not found.
CPF9820
Not authorized to use library &1.
CPF9830
Cannot assign library &1.
CPF9899
Error occurred during processing of command.
Top