The varchar data type

The varchar data type is the 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, 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;

VARCHAR size macros

HCL OneDB™ includes the varchar.h header file with the libraries. This file defines the names and macro functions shown in the following table.

Table 1. 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

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. The following figure shows the main() function for the varchar.ec demonstration program.

Figure 1: 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()
{
    mint vc_code;
    mint max, min;
    mint hv_length;

    EXEC SQL BEGIN DECLARE SECTION;
      mint 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");
}

When the IFX_PAD_VARCHAR environment variable is set to 1, the client sends the VARCHAR data type with padded trailing spaces. When this environment is not set (the default), the client sends the VARCHAR data type value without trailing spaces. The IFX_PAD_VARCHAR environment variable must be set only at the client side and is supported only with Version 9.53 and 2.90 or later and HCL OneDB Version 9.40 or later.