SQL Programming Manual for Pascal

Host Variables and Parameters
HP NonStop SQL Programming Manual for Pascal—528614-001
2-9
Using Host Variables
•
Using SETSCALE with the SELECT statement for retrieving a value
A program retrieves the value in the PARTS.PRICE column for a disk controller
and stores the value in host variable :HOSTVAR3. The value has a scale of 2.
EXEC SQL
SELECT PARTS.PRICE INTO SETSCALE ( :HOSTVAR3, 2 )
FROM =PARTS
WHERE PARTS.PARTDESC = "DISK CONTROLLER" ;
IF HOSTVAR3/100 > 50000 THEN
{ print message that manager approval }
{ is required for purchase }
If the price of the disk controller is $60,000.00 in the database, :HOSTVAR3 has the
value 6,000,000 on output.
•
Using SETSCALE with the SELECT statement for comparisons
A program retrieves the part description for the part with a price of $999.50. The
price value is stored in host variable :HOSTVAR4 and supplied to SQL in the
search condition. The retrieved value is stored in host variable :HVSTORE.
{ Assign 99950 to :HOSTVAR4 }
EXEC SQL
SELECT PARTS.PARTDESC INTO :HVSTORE
FROM =PARTS
WHERE PARTS.PRICE = SETSCALE ( :HOSTVAR4, 2 );
Using Date-Time and INTERVAL Data in Host Variables
NonStop SQL provides a set of special data types for specifying date values, time
values, and intervals (or durations) of time. For the complete date-time syntax, see the
SQL/MP Reference Manual. Table 2-2 shows the date-time and INTERVAL data types.
To communicate date-time values between Pascal and SQL statements, you declare a
Pascal FSTRING array in the Declare Section. Then you use the TYPE AS clause to
tell NonStop SQL to interpret the value in the FSTRING host variable as a date-time or
INTERVAL value. Some sample TYPE AS clauses are:
TYPE AS DATETIME YEAR TO HOUR
Table 2-2. Date-Time and INTERVAL Data Types
Data Type Description
DATETIME Represents a date and time, from year to microsecond. (Logical subsets,
such as MONTH TO DAY, are allowed.)
DATE Represents a date, and is a synonym for DATETIME YEAR TO DAY.
TIME Represents a time, and is a synonym for DATETIME HOUR TO SECOND.
TIMESTAMP Represents a date and time and is a synonym for DATETIME YEAR TO
FRACTION(6).
INTERVAL Represents a duration of time as a year-month or day-time INTERVAL.