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

Character Data Types

ESQL/C supports four data types that can hold character data that you retrieve from and send to the database. If you use a character data type (such as the SQL data types CHAR and VARCHAR) for your database column, you can choose any of the following data types for your host variable:

The C character data type: char
One of the ESQL/C predefined data types: fixchar, string, varchar
The lvarchar data type

GLS

If you use locale-sensitive character data types (NCHAR or NVARCHAR), you have the same choice of character data types for your associated 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.

The following two conditions determine which character data type to use:

Whether you want ESQL/C to terminate the character data with the null character
Whether you want ESQL/C to pad the character data with trailing blanks

Figure 3-1 summarizes the attributes of each of the character data types.

Figure 3-1
ESQL/C Character Data Types

ESQL/C Character Data Type Null Terminated Contains Trailing Blanks
char
4

4

fixchar

4

string
4

varchar
4

4

lvarchar
4

This section describes the characteristics of these data types and the differences among them.

The char Data Type

The char data type is the C data type that holds character data. When an application reads a value from a CHAR column into a host variable of type char, ESQL/C pads this value with trailing blanks up to the size of the host variable. It leaves just one place for the null character that terminates the host array. The behavior is the same if an application reads a value from a VARCHAR (or NVARCHAR) column into a host variable of the char data type.

Declare a char data type with a length of [n + 1] (where n is the size of the column with values that you want read) to allow for the null terminator. Use the following syntax to declare a host variable of the char data type:

EXEC SQL BEGIN DECLARE SECTION;

    char ch_name[n + 1];

EXEC SQL END DECLARE SECTION;

The fixchar Data Type

The fixchar data type is an ESQL/C data type that holds character data that does not append a null terminator. When an application reads a value from a CHAR column into a host variable of type fixchar, ESQL/C pads this value with trailing blanks up to the size of the host variable. ESQL/C does not append any null character. The behavior is the same if an application reads a value from a VARCHAR (or NVARCHAR) column into a host variable of the fixchar data type.

Important: Do not use the fixchar data type with VARCHAR (or NVARCHAR) data. With a fixchar, even if the length of the data is shorter than the size of the fixchar, the database server stores all n characters of the fixchar, including any blanks at the end of the string. Unless the blanks have significance, storing them defeats the space savings that the VARCHAR data type provides.

Declare a fixchar host variable as an array with n components (where n is the size of the column with values that you want read). Use the following syntax to declare a host variable of the fixchar data type:

EXEC SQL BEGIN DECLARE SECTION;

    fixchar fch_name[n];

EXEC SQL END DECLARE SECTION;

Important: You can copy a null-terminated C string into a fixchar variable if space is available for the null character. However, this is not good practice. When the database server inserts this value into a column, it also inserts the null terminator. As a result, later searches of the table might fail to find the value.

The string Data Type

The string data type is an ESQL/C data type that holds character data that is null terminated and does not contain trailing blanks. When an application reads a value from a CHAR column into a host variable of the string data type, it strips the value of any trailing blanks and appends a null terminator. The behavior is the same if an application reads a value from a VARCHAR column into a host variable of the string data type.

The one exception to this rule is if the CHAR or VARCHAR column contains an empty string. To distinguish an empty string from a null string, the database server stores an empty string as a single blank character. A string host variable stores an empty string as a single blank followed by a null terminator. If the lname column in the customer table contains an empty string, the following SELECT statement puts the string "b\0" (where b represents a blank and \0 is the null terminator) into the buffer host variable.

EXEC SQL BEGIN DECLARE SECTION;

	string buffer[16];

EXEC SQL END DECLARE SECTION;
.

.

.


EXEC SQL select lname into :buffer from customer

	where customer_num = 102;

Declare the string data type with a length of [n + 1] (where n is the size of the column with values that you want read) to allow for the null terminator. In the preceding code fragment, the lname column in the customer table is 15 bytes so the buffer host variable is declared as 16 bytes. Use the following syntax to declare a host variable of the string data type:

EXEC SQL BEGIN DECLARE SECTION;

    string  str_name[n + 1];

EXEC SQL END DECLARE SECTION;

The varchar Data Type

The varchar data type is an ESQL/C data type that holds character data of varying lengths. When an application reads a value from a CHAR column into a host variable of type varchar, ESQL/C preserves any trailing blanks and terminates the array with a null character. The behavior is the same if an application reads a value from a VARCHAR column into a host variable of the varchar data type.

Declare the varchar data type with a length of [n +1] (where n is the maximum size of the column with values that you want read) to allow for the null terminator. Use the following syntax to declare a host variable of the varchar data type:

EXEC SQL BEGIN DECLARE SECTION;

    varchar varc_name[n + 1];

EXEC SQL END DECLARE SECTION;

Informix includes the varchar.h header file with the ESQL/C libraries. This file defines the names and macro functions shown in Figure 3-2.

Figure 3-2
VARCHAR Size Macros

Name of Macro Description
MAXVCLEN
The maximum number of characters that you can store in a VARCHAR column. This value is 255.

VCLENGTH(s)
The length to declare the host variable.

VCMIN(s)
The minimum number of characters that you can store in the VARCHAR column. Can range from 1 to 255 bytes but must be smaller than the maximum size of the VARCHAR.

VCMAX(s)
The maximum number of characters that you can store in the VARCHAR column. Can range from 1 to 255 bytes.

VCSIZ(min, max)
The encoded size value, based on min and max, for the VARCHAR column.

These macros are useful when your program uses dynamic SQL. After a DESCRIBE statement, the macros can manipulate size information that the database server stores in the LENGTH field of the system-descriptor area (or the sqllen field of the sqlda structure). Your database server stores size information for a VARCHAR column in the syscolumns system catalog table.

The varchar.ec demonstration program obtains collength from the syscolumns system catalog table for the cat_advert column (of the stores7 database). It then uses the macros from varchar.h to display size information about the column. This sample program is in the varchar.ec file in the demo directory. Figure 3-3 shows the main() function for the varchar.ec demonstration program.

/*
* varchar.ec *

The following program illustrates the use of VARCHAR macros to
obtain size information.
*/

EXEC SQL include varchar;

char errmsg[512];

main()
{
int vc_code;
int max, min;
int hv_length;

EXEC SQL BEGIN DECLARE SECTION;
int vc_size;
EXEC SQL END DECLARE SECTION;

printf("VARCHAR Sample ESQL Program running.\n\n");

EXEC SQL connect to 'stores7';
chk_sqlcode("CONNECT");

printf("VARCHAR field 'cat_advert':\n");
EXEC SQL select collength into $vc_size from syscolumns
where colname = "cat_advert";
chk_sqlcode("SELECT");
printf("\tEncoded size of VARCHAR (from syscolumns.collength) = %d\n",
vc_size);

max = VCMAX(vc_size);
printf("\tMaximum number of characters = %d\n", max);

min = VCMIN(vc_size);
printf("\tMinimum number of characters = %d\n", min);

hv_length = VCLENGTH(vc_size);
printf("\tLength to declare host variable = char(%d)\n", hv_length);

vc_code = VCSIZ(max, min);
printf("\tEncoded size of VARCHAR (from VCSIZ macro) = %d\n", vc_code);

printf("\nVARCHAR Sample Program over.\n\n");
}

Figure 3-3
The varchar.ec Demo Program

The lvarchar Data Type

The lvarchar data type is an ESQL/C data type that holds character data of varying lengths. The lvarchar data type is implemented as a variable length user-defined type that is similar to the varchar data type except that it can support strings of greater than 256 bytes and has the following two uses:

To hold a value for an LVARCHAR column in the database.

LVARCHAR

lvarchar

VARCHAR

lvarchar

To represent the string or external format of opaque data types.

For more information, see

"Accessing the External Format of an Opaque Type"

Important: You cannot retrieve or store smart large objects (CLOB or BLOB data types) from or to an lvarchar host variable.

To declare an lvarchar host variable for a character column (CHAR, VARCHAR, or LVARCHAR), use the lvarchar keyword as the variable data type, as the following syntax shows.

Element Purpose Restrictions
variable name
Name of an lvarchar variable of a specified size.

variable size
Number of bytes to allocate for an lvarchar variable of specified size.
Integer value can be between 1 and 32,768 (32 kilobytes).

*variable name
Name of an lvarchar pointer variable for data of unspecified length.
Not equivalent to a C char pointer (char *). Points to an internal ESQL/C representation for this type. You must use the ifx_var() functions to manipulate data. For more information, see "The lvarchar pointer and var binary Library Functions".

Figure 3-4 shows declarations for three lvarchar variables that hold values for LVARCHAR columns.

Figure 3-4
Sample lvarchar Host Variables

Important: To declare an lvarchar host variable for the external format of an opaque data type, use the syntax described in "Declaring lvarchar Host Variables".

An lvarchar Host Variable of a Fixed Size

If you do not specify the size of an lvarchar host variable, the size is equivalent to a 1-byte C-language char data type. If you specify a size, the lvarchar host variable is equivalent to a C-language char data type of that size. When you specify a fixed-size lvarchar host variable, any data beyond the specified size will be truncated when the column is fetched. Use an indicator variable to check for truncation.

Because an lvarchar host variable of a known size is equivalent to a C-language char data type, you can use C-language character string operations to manipulate them.

The lvarchar Pointer Host Variable

When the lvarchar host variable is a pointer, the size of the data that the pointer references can range up to 2 gigabytes. The lvarchar pointer host variable is designed to insert or select user-defined or opaque types that can be represented in a character string format.

You must use the ifx_var() functions to manipulate an lvarchar pointer host variable.

For more information on the ifx_var() functions and on how to use the ESQL/C lvarchar host variable with opaque data types, see Chapter 10, "Working with INFORMIX-Universal Server Opaque Data Types."

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