SQL Programming Manual for Pascal

Host Variables and Parameters
HP NonStop SQL Programming Manual for Pascal—528614-001
2-8
Using Host Variables
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.
Consider these points when you use the SETSCALE function:
•
At the upper boundary scale for each data type, you must ensure that the value
output 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
outputs 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, the
following 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.
The following examples assume that you have a DEFINE for the PARTS table so that
the DEFINE name =PARTS represents the 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 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) ) ;
•
Using SETSCALE with the UPDATE statement
A program updates the PARTS.PRICE column for a disk controller to $158.34. The
value is multiplied by 100 and stored in host variable :HOSTVAR2.
{ Assign 15834 to :HOSTVAR2 }
EXEC SQL
UPDATE =PARTS SET PARTS.PRICE = SETSCALE (:HOSTVAR2, 2)
WHERE PARTS.PARTDESC = "DISK CONTROLLER" ;