SQL Programming Manual for TAL

Host Variables and Parameters
HP NonStop SQL Programming Manual for TAL—527887-001
2-13
Using Host Variables
Using the FIXED Data Type. This method is the simplest method to handle scale;
follow these guidelines when you use it:
•
The value stored in the FIXED host variable must fit into the database column.
FIXED variables are stored in 64 bits and can hold 19 digits. You must ensure that
the host variable never contains a value greater than will fit in the column;
otherwise, NonStop SQL reports an error.
For example, if the SQL column is defined as type NUMERIC (7,3), ensure that
values in the corresponding host variable of type FIXED(3) are never greater than
9999.999 or smaller than -9999.999.
•
If you use FIXED host variables, the SQL executor must use quadrupleword
arithmetic to evaluate expressions.
Using the SETSCALE Function. If you must ensure that a database column size
matches the host variable size (for example, you might be transferring data between a
NonStop SQL database and an Enscribe file), use a TAL numeric data type that
corresponds to the data type of the SQL column. You can use the INVOKE directive
and then use the SETSCALE function to communicate the scale between the TAL and
SQL statements.
If you use the SETSCALE function and your program must also use the host variables
in calculations, include the necessary code to scale the values in the host variables.
The SETSCALE function has this syntax:
: host-variable
is a numeric host variable.
INDICATOR : indicator-variable
is an indicator variable associated with the host variable. For more information
about using indicator variables to specify or read null values, see Using Date-Time
and INTERVAL Data Types as Host Variables on page 2-15.
scale
is an integer that designates the scale of :host-variable. The values allowed for
scale depend on the size of :host-variable as follows:
SETSCALE (: host-variable
[ [INDICATOR] : indicator-variable ] , scale )
Size TAL Type Values Allowed
2-byte integer INT 0 through 5 decimal digits
4-byte integer INT (32) 0 through 10 decimal digits
8-byte integer FIXED 0 through 18 decimal digits