SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
L-40
Considerations—LOAD
some of the ASCII printable characters, in which case using one of those
characters as the NULL attribute works correctly.
For DECIMAL fields, the same thing is true, because each byte of a DECIMAL
field normally contains the ASCII code for one of the characters "0" through
"9", so it is easy to choose a value for the NULL attribute that will not yield a
null value that is the same as any expected value for the field.
For binary fields, it is sometimes difficult to choose a value for the NULL
attribute that does not yield an expected value of the field when that NULL
attribute value is placed in each byte of the field. In the example of the BINARY
16 field, above, if the NULL attribute is chosen to be 1, that gives a field value
of 257 when 1 is put into both bytes of the field. If 257 is a value that the field
could contain in normal operation of the application, NULL 1 is not a good
choice for the NULL attribute. Often, numeric fields have a limited range of
values that are expected to be in the field and that allows you to pick a NULL
attribute value which yields an out-of-range value for the field when the NULL
attribute value is placed in each byte of the field. For example, if the normal
range of values for a BINARY 16 field is 0 through 32767, a NULL attribute
value of 255 is a good choice, because putting 255 into both bytes of the field
gives a value of -1 for the field, which is not one of the normal values expected
for the field.
If a binary field has no restriction on the values it might contain, this convention
for defining a null value cannot be used for that field.
To avoid these limitations for specifying the NULL attribute value, use the
SQLNULLABLE attribute supported by DDL. Support for this attribute is
enabled in LOAD only if the SQLNULLABLE option is specified for the LOAD
command. Otherwise, LOAD ignores this attribute. Use this option only for SQL
to Enscribe and Enscribe to SQL load operations.
For SQL to Enscribe load operations, LOAD adds null indicators for all the
Enscribe fields that have the SQLNULLABLE attribute set in the DDL definition.
For LOAD operations without the TARGETREC option, the null indicators are
added for all the Enscribe fields having the corresponding SQL column as
nullable.
For Enscribe to SQL load operations, LOAD reads null indicators for all the
Enscribe fields that have the SQLNULLABLE attribute set in the DDL definition.
For LOAD operations without the SOURCEREC option, the null indicators are
read for all the Enscribe fields having the corresponding SQL column as
nullable.
In all these cases, the null indicator is of two bytes with all bits set to one to
indicate a null value, and all bits set to zero to indicate a nonnull value.