Informix-ESQL/C Programmer's Manual

Informix-ESQL/C Programmer's Manual
Chapter 3: Working with Character and String Data Types

Home Contents Index Master Index New Book

Fetching and Inserting Character 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.

GLS

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.

Fetching VARCHAR Data

Figure 3-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 3-5

Source Destination Result
VARCHAR
char
If the source is longer, truncate and null terminate the value, and set any indicator variable. If the destination is longer, pad the value with trailing spaces and null terminate it.

VARCHAR
fixchar
If the source is longer, truncate the value and set any indicator variable. If the destination is longer, pad the value with trailing spaces.

VARCHAR
string
If the source is longer, truncate and null terminate the value, and set any indicator variable. If the destination is longer, null terminate the value.

VARCHAR
lvarchar
If the source is longer, truncate and set any indicator variable. If the destination is longer, null terminate it.

Converting the VARCHAR Data Type to ESQL/C Character Data Types Figure 3-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.

Figure 3-6
Examples of VARCHAR Conversion During a Fetch

(1 of 2)

Source Type Contents Length Destination Type Contents Indicator
VARCHAR(9)
Fairfield
9
char(5)
Fair\0
9

VARCHAR(9)
Fairfield
9
char(12)
Fairfield++\0
0

VARCHAR(12)
Fairfield+++
12
char(10)
Fairfield\0
12

VARCHAR(10)
Fairfield+
10
char(4)
Fai\0
10

VARCHAR(11)
Fairfield++
11
char(14)
Fairfield++++\0
0

VARCHAR(9)
Fairfield
9
fixchar(5)
Fairf
9

VARCHAR(9)
Fairfield
9
fixchar(10)
Fairfield+
0

VARCHAR(10)
Fairfield+
10
fixchar(9)
Fairfield
10

VARCHAR(10)
Fairfield+
10
fixchar(6)
Fairfi
10

VARCHAR(10)
Fairfield+
10
fixchar(11)
Fairfield++
0

VARCHAR(9)
Fairfield
9
string(4)
Fai\0
9

VARCHAR(9)
Fairfield
9
string(12)
Fairfield\0
0

VARCHAR(12)
Fairfield+++
12
string(10)
Fairfield\0
12

VARCHAR(11)
Fairfield++
11
string(6)
Fairf\0
11

VARCHAR(10)
Fairfield++
10
string(11)
Fairfield\0
0

VARCHAR(10)
Fairfield+
10
lvarchar(11)
Fairfield+
0

VARCHAR(9)
Fairfield
9
lvarchar(5)
Fair\0
9

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 3-7 shows the conversion of VARCHAR data when an application inserts it from host variables of char, fixchar, lvarchar, and string character data types.

Figure 3-7

Source Destination 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.

Converting ESQL/C Character Data Types to the VARCHAR Data Type

GLS

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 3-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.

Figure 3-8
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

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.

Fetching lvarchar Data

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".

Inserting lvarchar Data

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.

ANSI

Fetching and Inserting with an ANSI-Compliant Database

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.

Informix-ESQL/C Programmer's ManualChapter 3: Working with Character and String Data Types Home Contents Index Master Index New Book

Fetching and Inserting Character Data Types

Informix-ESQL/C Programmer's Manual
Chapter 3: Working with Character and String Data Types

Home Contents Index Master Index New Book