1.5.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 8 UAP Development Guide.

The default-setting function cannot be used in remote database access.

Table 1-20 shows default null values that can be set in an embedded variable.

Table 1-20 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)Space, n bytes
VARCHAR(n)Space, 1 byte1
National character dataNCHAR(n)Space, n characters2
NVARCHAR(n)Space, 1 byte2
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:03
Date interval dataINTERVAL YEAR TO DAY0 days, 0 months, 0 years
Time interval dataINTERVAL HOUR TO SECOND0 seconds, 0 minutes, 0 hours
Large-object data4BLOB(n)Data of length 0 bytes
Binary data4BINARY(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.