SQL/MX 2.x Database and Application Migration Guide (G06.23+, H06.04+, J06.03+)

Converting SQL/MP Applications to SQL/MX
Applications
HP NonStop SQL/MX Database and Application Migration Guide—540435-005
10-44
Indicator Variables
Host Variables With Date-Time Data Types
In NonStop SQL/MP, you must declare date-time and interval host variables as
character arrays and then use the TYPE AS clause to cause NonStop SQL/MP to
interpret the values as date-time or interval values.
In SQL/MX Release 2.x, you can declare host variables with standard date-time and
interval data types. For SQL/MP DATETIME data types that are not equivalent to
DATE, TIME, or TIMESTAMP, you must declare host variables as character arrays.
NonStop SQL/MX does not support the TYPE AS clause. Therefore, use the CAST
function for input to or output from date-time columns when using host variables that
are character arrays. For more information, see the
SQL/MX Programming Manual for
C and COBOL.
In NonStop SQL/MP, you use the CAST function to associate a character or numeric
data type with a dynamic input parameter. You use the TYPE AS clause in the
parameter specification to associate a date-time or interval data type with a parameter.
NonStop SQL/MX replaces the SQL/MP TYPE AS clause with the CAST function. In
NonStop SQL/MX, you can convert data to the data type you specify by using the
CAST function for any expression, whether or not you are using a parameter.
Indicator Variables
As in NonStop SQL/MP, the primary use of the indicator variable in NonStop SQL/MX
is to indicate whether the value supplied is null. If the source value is null, the value of
the indicator variable is set to less than zero.
However, in NonStop SQL/MX, the indicator variable has another use. If an indicator
variable is associated with a target character host variable in the INTO clause of a
SELECT or FETCH statement, and the length of the target variable is smaller than the
value returned, the truncated string is placed into the host variable, the indicator
variable is set to the length of the source string, and a warning message is issued.
Otherwise, if the length of the target variable is sufficient, the indicator variable is set to
0.
For example, suppose the columns PARTDESC and QTY_AVAILABLE in the PARTS
table allow null. After the SELECT statement executes, this C example tests the
indicator variable for null or a truncated value:
EXEC SQL SELECT partnum, partdesc, price, qty_available INTO
:parts_rec.partnum,
:parts_rec.partdesc
INDICATOR :parts_rec.partdesc_i,
:parts_rec.price,
:parts_rec.qty_available
INDICATOR :parts_rec.qty_available_i,
FROM sales.parts
WHERE partnum = 300380 AND partnum = 2402 ;
...
if (parts.qty_available_i < 0) handle_null_value();