DECLARE VARIABLE

The DECLARE VARIABLE statement is used to assign a subtype or CCSID other than the default to a host variable.

Invocation

This statement can only be embedded in an application program. It is not an executable statement. It must not be specified in Java™ or REXX.

Authorization

None required.

Syntax

Description

host-variable

Names a character, graphic, or XML string host variable defined in the program. An indicator variable cannot be specified for the host-variable. The host-variable definition may either precede or follow a DECLARE VARIABLE statement that refers to that variable.

FOR BIT DATA

Specifies that the values of the host-variable are not associated with a coded character set and, therefore, are never converted. The CCSID of a FOR BIT DATA host variable is 65535. FOR BIT DATA cannot be specified for graphic or XML host-variables.

FOR SBCS DATA

Specifies that the values of the host variable contain SBCS (single-byte character set) data. FOR SBCS DATA is the default if the CCSID attribute of the job at the application requester is not DBCS-capable or if the length of the host variable is less than 4. The CCSID of FOR SBCS DATA is determined by the CCSID attribute of the job at the application requester. FOR SBCS DATA cannot be specified for graphic or XML host-variables.

FOR MIXED DATA

Specifies that the values of the host variable contain both SBCS data and DBCS data. FOR MIXED DATA is the default if the CCSID attribute of the job at the application requester is DBCS-capable and the length of the host variable is greater than 3. The CCSID of FOR DBCS DATA is determined by the CCSID attribute of the job at the application requester. FOR MIXED DATA cannot be specified for graphic or XML host-variables.

CCSID integer

Specifies that the values of the host variable contain data of CCSID integer. If the integer is an SBCS CCSID, the host variable is SBCS data. If the integer is a mixed data CCSID, the host variable is mixed data. For character host variables, the CCSID specified must be an SBCS or mixed CCSID.

If the variable has a graphic string data type, the CCSID specified must be a DBCS, UTF-16, or UCS-2 CCSID. For a list of valid CCSIDs, see CCSID values. Consider specifying CCSID 1200 or 13488 to indicate UTF-16 or UCS-2 data. If a CCSID is not specified, the CCSID of the graphic string variable will be the associated DBCS CCSID for the job.

If the variable has an XML data type, the CCSID specified must be an SBCS, mixed, or Unicode CCSID. The CCSID must be compatible with the XML AS datatype. If a CCSID is not specified, the CCSID value as specified by the SQL_XML_DATA_CCSID QAQQINI setting is used. See XML Values for more information.

For file reference variables, the CCSID specifies the CCSID of the path and file name, not the data within the file.

DATE

Specifies that the values of the host variable contain data that is a date.

TIME

Specifies that the values of the host variable contain data that is a time.

TIMESTAMP

Specifies that the values of the host variable contain data that is a timestamp.

Notes

Placement restrictions: The DECLARE VARIABLE statement can be specified anywhere in an application program that SQL statements are valid with the following exceptions:

If the host language is COBOL or RPG, the DECLARE VARIABLE statement must occur before an SQL statement that refers to a host variable specified in the DECLARE VARIABLE statement.
If DATE, TIME, or TIMESTAMP is specified for a NUL-terminated character string in C, the length of the C declaration will be reduced by one.

Precompiler rules: The following situations result in an error message during precompile:

A reference is made to a variable that does not exist.
A reference is made to a numeric variable.
A reference is made to a variable that has been referred to already.
A reference is made to a variable that is not unique.
A reference is made to an ILE RPG variable that is defined as UCS-2.
A reference is made to an ILE COBOL variable that is defined as NATIONAL.
The DECLARE VARIABLE statement occurs after an SQL statement where the SQL statement and the DECLARE VARIABLE statement refer to the same variable.
The FOR BIT DATA, FOR SBCS DATA, or FOR MIXED DATA clause is specified for a graphic or XML host variable.
A SBCS or mixed CCSID is specified for a graphic host variable.
A DBCS, UTF-16, or UCS-2 CCSID is specified for a character host variable.
A DBCS CCSID is specified for an XML host variable.
DATE, TIME, or TIMESTAMP is specified for a host variable that is not character.
The length of a host variable used for DATE, TIME, or TIMESTAMP is not long enough for the minimum date, time, or timestamp value.

Example

In this example, declare C program variables fred and pete as mixed data, and jean and dave as SBCS data with CCSID 37.

void main () 
  {
     EXEC SQL BEGIN DECLARE SECTION;
     char fred[10];
     EXEC SQL DECLARE :fred VARIABLE  FOR MIXED DATA;
     
     decimal(6,0) mary;
     char pete[4];
     EXEC SQL DECLARE :pete VARIABLE FOR MIXED DATA;
     
     char jean[30];
     char dave[9];
     EXEC SQL DECLARE :jean, :dave VARIABLE  CCSID 37;
     EXEC SQL END DECLARE SECTION;
     EXEC SQL INCLUDE SQLCA;   
   ...

  }