1.6.6 Setting a null default value in an embedded variable

When data is retrieved into an embedded variable using the FETCH, SELECT, EXECUTE, or EXECUTE IMMEDIATE statement, and if the retrieved value is the null value, a negative value is assigned to the indicator variable. When this occurs, the embedded variable assumes the value that existed before the SQL statement was issued. A default value can be set in an embedded variable by using the null-value default-setting function on the embedded variable. An indicator variable does not need to be specified for null-value data. The null-value default-setting function can be used only for fetching data.

When using the default-setting function, the user needs to have set PDDFLNVAL option in the client environment definition process at the time the UAP is executed. For details about the client environment definition process, refer to the HiRDB Version 9 UAP Development Guide.

The following table lists the default null values that can be set in an embedded variable.

Table 1-21 Default null values that can be set in an embedded variable

CategoryData typeDefault value
Numeric dataINTEGER0
SMALLINT
DECIMAL
LARGE DECIMAL
FLOAT
SMALLFLT
Character dataCHARACTER(n)Default character set or a character set other than UTF-16Space, n bytes
UTF16 character setSpace, n[Figure] 2 characters
VARCHAR(n)Default character set or a character set other than UTF-16Space, 1 byte#1
UTF-16 character setSpace, 1 character
National character dataNCHAR(n)Space, n characters#2
NVARCHAR(n)Space, 1 byte#2
Mixed character dataMCHAR(n)Space, n bytes
MVARCHAR(n)Space, 1 byte
Date dataDATE01/01/01
Time dataTIME0:0:0
Time stamp dataTIMESTAMP01/01/01 0:0:0#3
Date interval dataINTERVAL YEAR TO DAY0 days, 0 months, 0 years
Time interval dataINTERVAL HOUR TO SECOND0 seconds, 0 minutes, 0 hours
Large-object data#4BLOB(n)Data of length 0 bytes
Binary data#4BINARY(n)Data of length 0 bytes

Note: The data elements in simple structures and repetition structures are also defined according to this table. If all data in a repetition structure is NULL, the current number of elements will be 0.

#1: In the result of a WRITE specification, this will be an IP address.
#2: Depends on the character code used in HiRDB.
Example: X'8140' in shift JIS

If a space conversion level is specified in PDSPACELVL in the client environment definition or in the pd_space_level operand in the system common definition, any spaces in an embedded variable will also be subject to the conversion. Therefore, an NCHAR space can be converted into n x 2 bytes, an NVARCHAR space can be converted into 2 bytes.

#3: If a fractional second precision is specified, the specified digit positions are zero-filled.
#4: If a locator is used, a value that identifies data greater than 0 bytes in length on the server is assigned to the embedded variable of the locator.