SQL Programming Manual for Pascal

Host Variables and Parameters
HP NonStop SQL Programming Manual for Pascal—528614-001
2-4
Declaring Host Variables
•
When a host variable serves as an input variable (supplies a value to the
database), the system first converts the value that the variable contains to a
compatible SQL data type and then uses the value in the SQL operation.
•
When a host variable serves as an output variable (receives a value from a
database), the system converts the value to the data type of the host variable.
NonStop SQL supports conversion within character types and numeric types, but it
does not support conversion between character and numeric types. SQL date-time and
INTERVAL types are compatible only with Pascal FSTRING types (and the FSTRING
host variable used to store the date-time or INTERVAL value must have a TYPE AS
clause). For more information, see Using Date-Time and INTERVAL Data in Host
Variables on page 2-9.
For conversion between character strings of different lengths, NonStop SQL pads the
receiving string on the right with blanks as necessary. If the receiving string is too
small, NonStop SQL truncates the right part of the longer string and returns a warning
code in the SQLCODE variable.
If an input value is too large for an SQL column, NonStop SQL returns error 8300 (file-
system error encountered). If you are using the SQLCADISPLAY procedure to obtain
an error message, SQLCADISPLAY also returns file-system error number 1031.
For conversion between numeric types, NonStop SQL converts data between signed
and unsigned types and between types with different precisions. You can use the
SETSCALE function to communicate a number’s scale to and from a database. The
scale information is valid only in the context of the SQL statement; for the Pascal
statements, your program must handle scaling.
Declaring Host Variables with INVOKE
You can use the INVOKE directive to declare host variables that correspond to the
columns in an SQL table. The INVOKE directive converts the column names to Pascal
identifiers and writes a Pascal data description for each column. If the column is
defined to allow null values, INVOKE also produces indicator variables that your
program can use to indicate whether or not the column value is null.
The following example shows a sample CREATE TABLE statement for a table
containing all the SQL data types, a sample INVOKE statement, and the generated
Pascal record description.
CREATE TABLE statement defining the table:
CREATE TABLE $VOL.SUBV1.PTABLE
( A_CHAR CHAR (10) NOT NULL,
B_VARCHAR VARCHAR (10) NOT NULL,
C_NUM4_S NUMERIC (4) SIGNED NOT NULL,
D_NUM4_U NUMERIC (4) UNSIGNED NOT NULL,
E_NUM9_S NUMERIC (9,2) SIGNED NOT NULL,
F_NUM9_U NUMERIC (9,2) UNSIGNED NOT NULL,
G_NUM18_S NUMERIC (18,2) SIGNED NOT NULL,
H_SMALL_S SMALLINT SIGNED NOT NULL,