SQL Programming Manual for TAL

Host Variables and Parameters
HP NonStop SQL Programming Manual for TAL—527887-001
2-14
Using Host Variables
The SETSCALE function causes NonStop SQL to use :host-variable in the context of
SQL statements as if :host-variable were declared with a scale of scale. For example,
the sequence SETSCALE (hostvar, n) causes NonStop SQL to interpret the host
variable hostvar as:
hostvar * 10 - n
If the value in :host-variable is to be entered into a database using an INSERT or
UPDATE statement, the program must assign a value to :host-variable that allows for
the scale. For example, if the program is representing a price of $123.45, then the
program should assign 12345 to :host-variable and use SETSCALE to specify a scale
of 2.
If the value is retrieved from the database using a SELECT statement, NonStop SQL
assigns a value to :host-variable that allows for the scale. For example, if the value
123.45 is stored in the database, then the value 12345 is returned to :host-variable
when the program specifies SETSCALE with a scale of 2 in the SELECT statement. If
the value 123 is stored in the database and the program specifies SETSCALE with a
scale of 2, the value 12300 is returned to :host-variable.
These examples use a TACL DEFINE named =PARTS for the PARTS table.
•
Using SETSCALE with the INSERT statement
A program creates a new row with the value 98.34 in the PARTS.PRICE column after
storing the value in the host variable :HOSTVAR1. The value is multiplied by 100 and
stored in :HOSTVAR1.
! Assign 9834 to :hostvar1
EXEC SQL
INSERT INTO =parts (price)
VALUES ( SETSCALE (:hostvar1, 2) ) ;
Caution. Consider these points when you use the SETSCALE function:
•
At the upper boundary scale for each data type, you must ensure that the value returned
from the database fits into the host variable. For example, if the host variable is specified to
SETSCALE with a scale of 5 (the upper boundary on scale for a 2-byte integer) and the
value 0.70000 is stored in the database, NonStop SQL sends a value of 70000, which
does not fit in a variable of type INT.
•
To use SETSCALE in an expression, you must apply SETSCALE to each host variable
individually rather than to the result of the expression. For example, this expression adds
two prices with a scale of 2 decimal places:
SETSCALE (:price1, 2) + SETSCALE (:price2, 2)
•
Remember to allow room in host variables for digits to the left of the decimal point. For
example, if the value in the database is 12.345, and you specify a scale of 5, the output
value, 1234500, will not fit in an INT host variable.