SQL Programming Manual for TAL

Host Variables and Parameters
HP NonStop SQL Programming Manual for TAL—527887-001
2-4
Using Corresponding SQL and TAL Data Types
Data Conversion
NonStop SQL performs the conversion from SQL data types to TAL data types and
from TAL data types to SQL data types as follows:
•
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. The SQL date-time
and INTERVAL types are compatible only with TAL STRING types (and the STRING
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 Types as
Host Variables on page 2-15.
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
short, 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.
Examples of host variable declarations using the default data types are:
EXEC SQL BEGIN DECLARE SECTION;
! SQL recognizes address as a SMALLINT host variable
INT address;
! SQL recognizes name as a CHAR(20) host variable
STRING .name[0:19];
!SQL recognizes message as a VARCHAR(200) host variable
STRUCT .message;
BEGIN
INT msg^length;
STRING text[0:199];
END;
EXEC SQL END DECLARE SECTION;