INFORMIX-ESQL/C Programmer's Manual Working with Character and String Data Types

You can transfer character data between CHAR and VARCHAR columns and character (char, string, fixchar, varchar, or lvarchar) host variables with either of the following operations:

• A fetch operation transfers character data from a CHAR or VARCHAR column to a character host variable.
• An insert or update operation transfers character data from a character host variable to a CHAR, VARCHAR, or LVARCHAR column.

If you use locale-sensitive character data types (NCHAR or NVARCHAR), you can also transfer character data between NCHAR or NVARCHAR columns and character host variables. For more information about how to declare host variables for the NCHAR and NVARCHAR data types, see the Informix Guide to GLS Functionality.

When an application uses a character host variable to fetch or insert a CHAR value, ESQL/C must ensure that the character value fits into the host variable or database column.

An application can fetch data from a database column of type CHAR or VARCHAR into a character (char, string, fixchar, varchar, or lvarchar) host variable. If the column data does not fit into the character host variable, ESQL/C truncates the data. To notify the user of the truncation, ESQL/C performs the following actions:

• It sets the sqlca.sqlwarn.sqlwarn1 warning flag to W and the SQLSTATE variable to 01004.
• It sets any indicator variable that is associated with the character host variable to the size of the character data in the column.

For more information about indicator variables, see Using Indicator Variables.

An application can insert data from a character host variable (char, string, fixchar, varchar, or lvarchar) into a database column of type CHAR. When an application inserts a character value into a CHAR database column, ESQL/C pads the value with blanks up to the size of the column. If the column size is less than the length of the host-variable value, ESQL/C truncates the value. In other words, ESQL/C always pads or truncates a character value to the size of that column when it inserts the value into a CHAR database column.

Although char, varchar, lvarchar, and string host variables contain null terminators, ESQL/C never inserts these characters into a database column. (Host variables of type fixchar should never contain null characters.)

If you use the locale-sensitive character data type, NCHAR, you can insert a value from a character host variable into an NCHAR column. Insertion into NCHAR columns follows the same behavior as insertion into CHAR columns. For more information about how to declare host variables for the NCHAR data type, see the Informix Guide to GLS Functionality.

Do not use the fixchar data type for host variables that insert character data into ANSI-compliant databases. For more information, see Fetching and Inserting with an ANSI-Compliant Database.

When an application uses a character host variable to fetch or insert a VARCHAR value, ESQL/C must ensure that the character value fits into the host variable or database column. When ESQL/C calculates the length of a source item, it does not count trailing spaces. The following sections describe how ESQL/C performs the conversion of VARCHAR data to and from char, fixchar, and string character data types.

These conversions also apply to NVARCHAR data. For more information on the NVARCHAR data type, see the Informix Guide to GLS Functionality.

Figure 4-5 shows the conversion of VARCHAR data when an application fetches it into host variables of char, fixchar, lvarchar, and string character data types.

Figure 4-6 shows examples of conversions from VARCHAR column data to character host variables that ESQL/C might perform during a fetch. In this figure, a plus (+) symbol represents a space character and the value in the Length column includes any null terminators.

Inserting VARCHAR Data

When an application inserts a value from a char, varchar, lvarchar, or string host variable into a VARCHAR column, ESQL/C preserves any trailing blanks, as long as the maximum size of the column is adequate. ESQL/C does not, however, add trailing blanks. If the column size is less than the length of the host-variable value, ESQL/C truncates the value.

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

Figure 4-7 shows the conversion of VARCHAR data when an application inserts it from host variables of char, fixchar, lvarchar, and string character data types.

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 Informix Guide to GLS Functionality.

Figure 4-8 shows examples of conversions from character host variables to VARCHAR column data that ESQL/C might perform during an insert. In this figure, a plus (+) symbol represents a space character.

Fetching and Inserting lvarchar Data

When an application uses an lvarchar host variable to fetch or insert a data value, ESQL/C must ensure that the value fits into the host variable or database column.

An application can fetch data from a database column of type LVARCHAR into a character (char, string, fixchar, varchar, or lvarchar) host variable. If the column data does not fit into the host variable, ESQL/C truncates the data. To notify the user of the truncation, ESQL/C performs the following actions:

• It sets the sqlca.sqlwarn.sqlwarn1 warning flag to W and the SQLSTATE variable to 01004.
• It sets any indicator variable that is associated with the character host variable to the size of the character data in the column.

For more information about indicator variables, see Using Indicator Variables.

An application can insert data from a character host variable (char, string, fixchar, varchar, or lvarchar) into a database column of type LVARCHAR. When an application inserts a character value into an LVARCHAR database column and the column size is less than the length of the host-variable value, ESQL/C truncates the value.

If the host variable you use for the insert is a char or varchar, the database server casts the type to lvarchar.

When you write data to an LVARCHAR column, the database server imposes a limit of 2 kilobytes on the column. If the host variable is an lvarchar data type and the data exceeds 2 kilobytes, the database server returns an error. If the column has an input support function, it must consume any data beyond 2 kilobytes, if necessary, to prevent the database server from returning the error.

For an ANSI-compliant database, when you use a character host variable in an INSERT statement or in the WHERE clause of an SQL statement (SELECT, UPDATE, or DELETE), the character value in the host variable must be null terminated. Therefore, use the following data types for character host variables:

• char, string, or varchar
• lvarchar

For example, the following insertion is valid because the first and last host variables are of type char, which is null terminated:

EXEC SQL BEGIN DECLARE SECTION;

	char first[16], last[16];

EXEC SQL END DECLARE SECTION;


stcopy("Dexter", first);

stcopy("Haven", last);

EXEC SQL insert into customer (fname, lname)

	values (:first, :last);

The stcopy() function copies the null terminator into the host variable and the char data type retains the null terminator.

Do not use the fixchar data type for host variables because it does not include a null terminator on the string. For an ANSI-compliant database, the database server generates an error under either of the following conditions:

• If you try to insert a string that is not null terminated.
• If you use a string that is not null terminated in a WHERE clause.