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
Category | Data type | Default value | |
---|---|---|---|
Numeric data | INTEGER | 0 | |
SMALLINT | |||
DECIMAL | |||
LARGE DECIMAL | |||
FLOAT | |||
SMALLFLT | |||
Character data | CHARACTER(n) | Default character set or a character set other than UTF-16 | Space, n bytes |
UTF16 character set | Space, n![]() | ||
VARCHAR(n) | Default character set or a character set other than UTF-16 | Space, 1 byte#1 | |
UTF-16 character set | Space, 1 character | ||
National character data | NCHAR(n) | Space, n characters#2 | |
NVARCHAR(n) | Space, 1 byte#2 | ||
Mixed character data | MCHAR(n) | Space, n bytes | |
MVARCHAR(n) | Space, 1 byte | ||
Date data | DATE | 01/01/01 | |
Time data | TIME | 0:0:0 | |
Time stamp data | TIMESTAMP | 01/01/01 0:0:0#3 | |
Date interval data | INTERVAL YEAR TO DAY | 0 days, 0 months, 0 years | |
Time interval data | INTERVAL HOUR TO SECOND | 0 seconds, 0 minutes, 0 hours | |
Large-object data#4 | BLOB(n) | Data of length 0 bytes | |
Binary data#4 | BINARY(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.
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.