Insert VARCHAR data

When an application inserts a value from a char, varchar, lvarchar, or string host variable into a VARCHAR column, also inserts any trailing blanks. does not, however, add trailing blanks.

If the value is longer than the maximum size of the column, the database server truncates the value if the database is non-ANSI. No warning is generated when this truncation occurs. If the database is ANSI and the value is longer than the maximum column size then the insert fails and this error is returned: 
-1279: Value exceeds string column length.

Although char, varchar, lvarchar, and string host variables contain null terminators, never inserts these characters into a database column. (Host variables of type fixchar must never contain null characters.) If an application inserts a char, varchar, lvarchar, or string value into a VARCHAR column, the database server tracks the end of the value internally.

The following table shows the conversion of VARCHAR data when an application inserts it from host variables of char, fixchar, lvarchar, and string character data types.
Table 1. Converting ESQL/C character data types to the VARCHAR data type
Source type Destination type Result
char VARCHAR If the source is longer than the max VARCHAR, truncate the value and set the indicator variable. If the max VARCHAR is longer than the source, the length of the destination equals the length of the source (not including the null terminator of the source).
fixchar VARCHAR If the source is longer than the max VARCHAR, truncate the value and set the indicator variable. If the max VARCHAR is longer than the source, the length of the destination equals the length of the source.
string VARCHAR If the source is longer than the max VARCHAR, truncate the value and set the indicator variable. If the max VARCHAR is longer than the source, the length of the destination equals the length of the source (not including the null terminator of the source).
lvarchar VARCHAR If the source is longer than the max VARCHAR, truncate the value and set the indicator variable. If the max VARCHAR is longer than the source, the length of the destination equals the length of the source.

If you use the locale-sensitive character data type, NVARCHAR, you can insert a value from a character host variable into an NVARCHAR column. Insertion into NVARCHAR columns follows the same behavior as insertion into VARCHAR columns. For more information about how to declare host variables for the NVARCHAR data type, see the HCL OneDB™ GLS User's Guide.

The following table shows examples of conversions from character host variables to VARCHAR column data that might perform during an insert. In this figure, a plus (+) symbol represents a space character.
Table 2. Examples of VARCHAR conversion during an insert
Source Type Contents Length Destination type Contents Length
char(10) Fairfield\0 10 VARCHAR(4) Fair 4
char(10) Fairfield\0 10 VARCHAR(11) Fairfield 9
char(12) Fairfield++\0 12 VARCHAR(9) Fairfield 9
char(13) Fairfield+++\0 13 VARCHAR(6) Fairfi 6
char(11) Fairfield+\0 11 VARCHAR(11) Fairfield+ 10
fixchar(9) Fairfield 9 VARCHAR(3) Fai 3
fixchar(9) Fairfield 9 VARCHAR(11) Fairfield 9
fixchar(11) Fairfield++ 11 VARCHAR(9) Fairfield 9
fixchar(13) Fairfield++++ 13 VARCHAR(7) Fairfie 7
fixchar(10) Fairfield+ 10 VARCHAR(12) Fairfield+ 10
string(9) Fairfield\0 9 VARCHAR(4) Fair 4
string(9) Fairfield\0 9 VARCHAR(11) Fairfield 9