A variable in an SQL statement specifies a value that can be changed when the SQL statement is executed. There are several types of variables used in SQL statements:
A host variable is a COBOL data item, an RPG field, or a PLI, REXX, C++, or C variable that is referenced in an SQL statement. Host variables are defined by statements of the host language. For more information about how to refer to host structures in C, C++, COBOL, PL/I, and RPG, see Host structures. For more information about host variables in REXX, see the Embedded SQL Programming book.
A host variable in an SQL statement must identify a host variable described in the program according to the rules for declaring host variables.
All host variables used in an SQL statement should be declared in an SQL declare section in all host languages other than Java™, REXX, and RPG. Variables do not have to be declared in REXX. In Java and RPG, there is no declare section, and host variables may be declared throughout the program. No variables may be declared outside an SQL declare section with names identical to variables declared inside an SQL declare section. An SQL declare section begins with BEGIN DECLARE SECTION and ends with END DECLARE SECTION.
For further information about using host variables, see the Embedded SQL Programming book.
A variable in the INTO clause of a FETCH, a SELECT INTO, a SET variable, a GET DESCRIPTOR, or a VALUES INTO statement identifies a host variable to which a value from a result column is assigned. A variable in the GET DIAGNOSTICS statement identifies a host variable to which a diagnostic value is assigned. A host variable in a CALL or in an EXECUTE statement can be an output argument that is assigned a value after execution of the procedure, an input argument that provides an input value for the procedure, or both an input and output argument. In all other contexts a variable specifies a value to be passed to the database manager from the application program.
Non-Java variable references: The general form of a variable reference in all languages other than Java is:
Each host-identifier must be declared in the source program. The variable designated by the second host-identifier is called an indicator variable and must have a data type of small integer.
The purposes of the indicator variable are to:
For example, if :V1:V2 is used to specify an insert or update value, and if V2 is negative, the value specified is the null value. If V2 is not negative the value specified is the value of V1.
Similarly, if :V1:V2 is specified in a CALL, FETCH, SELECT INTO, or VALUES INTO statement and the value returned is null, V1 is undefined, and V2 is set to a negative value. The negative value is:
If the value returned is not null, that value is assigned to V1 and V2 is set to zero (unless the assignment to V1 requires string truncation, in which case, V2 is set to the original length of the string). If an assignment requires truncation of the seconds part of time, V2 is set to the number of seconds.
If the second host-identifier is omitted, the host variable does not have an indicator variable. The value specified by the host variable :V1 is always the value of V1, and null values cannot be assigned to the variable. Thus, this form should not be used unless the corresponding result column cannot contain null values. If this form is used and the column contains nulls, the database manager will return an error at run-time (SQLSTATE 23502).
An SQL statement that references host variables in C, C++, ILE RPG, and PL/I, must be within the scope of the declaration of those host variables. For host variables referenced in the SELECT statement of a cursor, that rule applies to the OPEN statement rather than to the DECLARE CURSOR statement.
The CCSID of a string host variable is either:
Java variable references: The general form of a host variable reference in Java is:
In Java, indicator variables are not used. Instead, instances of a Java class can be set to a null value. Variables defined as Java primitive types cannot be set to a null value.
If IN, OUT, or INOUT is not specified, the default depends on the context in which the variable is used. If the Java variable is used in an INTO clause, OUT is the default. Otherwise, IN is the default. For more information on Java variables, see IBM® Developer Kit for Java.
Using the PROJECT table, set the host variable PNAME (VARCHAR(26)) to the project name (PROJNAME), the host variable STAFF (DECIMAL(5,2)) to the mean staffing level (PRSTAFF), and the host variable MAJPROJ (CHAR(6)) to the major project (MAJPROJ) for project (PROJNO) 'IF1000'. Columns PRSTAFF and MAJPROJ may contain null values, so provide indicator variables STAFF_IND (SMALLINT) and MAJPROJ_IND (SMALLINT).
SELECT PROJNAME, PRSTAFF, MAJPROJ INTO :PNAME, :STAFF :STAFF_IND, :MAJPROJ :MAJPROJ_IND FROM PROJECT WHERE PROJNO = 'IF1000'
In dynamic SQL statements, parameter markers are used instead of variables. A parameter marker is a question mark (?) that represents a position in a dynamic SQL statement where the application will provide a value; that is, where a variable would be found if the statement string were a static SQL statement. The following examples shows a static SQL that uses host variables and a dynamic statement that uses parameter markers:
INSERT INTO DEPT VALUES( :HV_DEPTNO, :HV_DEPTNAME, :HV_MGRNO:IND_MGRNO, :HV_ADMRDEPT) INSERT INTO DEPT VALUES( ?, ?, ?, ? )
For more information about parameter markers, see Parameter Markers.
Regular LOB variables, LOB locator variables (see References to LOB locator variables) and LOB file reference variables (see References to LOB file reference variables), can be defined in the following host languages:
Where LOBs are allowed, the term variable in a syntax diagram can refer to a regular variable, a locator variable, or a file reference variable. Since these variables are not native data types in host programming languages, SQL extensions are used and the precompilers generate the host language constructs necessary to represent each variable.
When it is possible to define a variable that is large enough to hold an entire LOB value and the performance benefit of delaying the transfer of data from the server is not required, a LOB locator is not needed. However, it is often not acceptable to store an entire LOB value in temporary storage due to host language restrictions, storage restrictions, or performance requirements. When storing a entire LOB value at one time is not acceptable, a LOB value can be referred to by a LOB locator and portions of the LOB value can be accessed.
A LOB locator variable is a variable that contains the locator representing a LOB value on the application server, which can be defined in the following host languages:
See Manipulating large objects with locators for information on how locators can be used to manipulate LOB values.
A locator variable in an SQL statement must identify a LOB locator variable described in the program according to the rules for declaring locator variables. This is always indirectly through an SQL statement. For example, in C:
static volatile SQL TYPE IS CLOB_LOCATOR *loc1;
The term locator-variable, as used in the syntax diagrams, shows a reference to a LOB locator variable. The meta-variable locator-variable can be expanded to include a host-identifier the same as that for host-variable.
Like all other variables, a LOB locator variable can have an associated indicator variable. Indicator variables for LOB locator variables behave in the same way as indicator variables for other data types. When a null value is returned from the database, the indicator variable is set and the variable is unchanged. When the indicator variable associated with a LOB locator is null, the value of the referenced LOB is null. This means that a locator can never point to a null value.
If a locator variable does not currently represent any value, an error occurs when the locator variable is referenced.
At transaction commit or any transaction termination, all LOB locators that were acquired by the transaction are released.
It is the application programmer's responsibility to guarantee that any LOB locator is only used in SQL statements that are executed at the same application server that originally generated the LOB locator. For example, assume that a LOB locator is returned from one application server and assigned to a LOB locator variable. If that LOB locator variable is subsequently used in an SQL statement that is executed at a different application server, unpredictable results will occur.
A LOB file reference variable is used for direct file input and output for a LOB, which can be defined in the following host languages:
Since these are not native data types, SQL extensions are used and the precompilers generate the host language constructs necessary to represent each variable.
A file reference variable represents (rather than contains) the file, just as a LOB locator represents, rather than contains, the LOB data. Database queries, updates, and inserts may use file reference variables to store or to retrieve single column values. The file referenced must exist at the application requester.
Like all other variables, a file reference variable can have an associated indicator variable. Indicator variables for file reference variables behave in the same way as indicator variables for other data types. When a null value is returned from the database, the indicator variable is set and the variable is unchanged. When the indicator variable associated with a file reference variable is null, the value of the referenced LOB is null. This means that a file reference variable can never point to a null value.
The length attribute of a file reference variable is assumed to be the maximum length of a LOB.
File reference variables are currently supported in the root (/), QOpenSys, and UDFS file systems. When a file is created, it is given the CCSID of the data that is being written to the file. Currently, mixed CCSIDs are not supported. To use a file created with a file reference variable, the file should be opened in binary mode.
For more information about file reference variables, see the SQL Programming book.
A host structure is a COBOL group, PL/I, C, or C++ structure, or RPG data structure that is referenced in an SQL statement. Host structures are defined by statements of the host language, as explained in the Embedded SQL Programming book. As used here, the term host structure does not include an SQLCA or SQLDA.
The form of a host structure reference is identical to the form of a host variable reference. The reference :S1:S2 is a host structure reference if S1 names a host structure. If S1 designates a host structure, S2 must be either a small integer variable, or an array of small integer variables. S1 is the host structure and S2 is its indicator array.
A host structure can be referenced in any context where a list of host variables can be referenced. A host structure reference is equivalent to a reference to each of the host variables contained within the structure in the order which they are defined in the host language structure declaration. The nth variable of the indicator array is the indicator variable for the nth variable of the host structure.
In C, for example, if V1, V2, and V3 are declared as variables within the structure S1, the statement:
EXEC SQL FETCH CURSOR1 INTO :S1;
is equivalent to:
EXEC SQL FETCH CURSOR1 INTO :V1, :V2, :V3;
If the host structure has m more variables than the indicator array, the last m variables of the host structure do not have indicator variables. If the host structure has m fewer variables than the indicator array, the last m variables of the indicator array are ignored. These rules also apply if a reference to a host structure includes an indicator variable or if a reference to a host variable includes an indicator array. If an indicator array or indicator variable is not specified, no variable of the host structure has an indicator variable.
In addition to structure references, individual host variables in the host structure or indicator variables in the indicator array can be referenced by qualified names. The qualified form is a host identifier followed by a period and another host identifier. The first host identifier must name a host structure, and the second host identifier must name a host variable within that host structure.
The general form of a host variable or host structure reference is:
A host-variable in an expression must identify a host variable (not a structure) described in the program according to the rules for declaring host variables.
The following C example shows a references to host structure, host indicator array, and a host variable:
struct { char empno[7];
struct { short int firstname_len;
char firstname_text[12];
} firstname;
char midint,
struct { short int lastname_len;
char lastname_text[15];
} lastname;
char workdept[4];
} pemp1;
short ind[14];
short eind
struct { short ind1;
short ind2;
} indstr;
.....
strcpy(pemp1.empno,"000220");
.....
EXEC SQL
SELECT *
INTO :pemp1:ind
FROM corpdata.employee
WHERE empno=:pemp1.empno;
In the example above, the following references to host variables and host structures are valid:
:pemp1 :pemp1.empno :pemp1.empno:eind :pemp1.empno:indstr.ind1
In PL/I, C++, and C, a host structure array is a structure name having a dimension attribute. In COBOL, it is a one-dimensional table. In RPG, it is an occurrence data structure. In ILE RPG, it can also be a data structure with the keyword DIM. A host structure array can only be referenced in the FETCH statement when using a multiple-row fetch, or in an INSERT statement when using a multiple-row insert. Host structure arrays are defined by statements of the host language, as explained in the Embedded SQL Programming book.
The form of a host structure array is identical to the form of a host variable reference. The reference :S1:S2 is a reference to host structure array if S1 names a host structure array. If S1 designates a host structure, S2 must be either a small integer host variable, an array of small integer host variables, or a two dimensional array of small integer host variables. In the following example, S1 is the host structure array and S2 is its indicator array.
EXEC SQL FETCH CURSOR1 FOR 5 ROWS INTO :S1:S2;
The dimension of the host structure and the indicator array must be equal.
If the host structure has m more variables than the indicator array, the last m variables of the host structure do not have indicator variables. If the host structure has m fewer variables than the indicator array, the last m variables of the indicator array are ignored. If an indicator array or variable is not specified, no variable of the host structure array has an indicator variable.
The following diagram specifies the syntax of references to an array of host structures:
Arrays of host structures are not supported in REXX.