INFORMIX-ESQL/C Programmer's Manual Programming with INFORMIX-ESQL/C

In an ESQL/C application, the SQL statements can refer to the contents of host variables. A host variable is an ESQL/C program variable that you use to transfer information between the ESQL/C program and the database.

You can use host variables in ESQL/C expressions in the same way that you use literal values.

1. Declare the host variable in the C program.
2. Assign a value to the host variable.
3. Specify the host variable in an embedded SQL statement.

You must define the data storage that a host variable needs before you can use that variable in an ESQL/C program. To assign an identifier to the variable and associate it with a data type, you declare the variable.

You declare host variables within the ESQL/C program as C variables, with the same basic syntax as C variables. To identify the variable as a host variable, you must declare it in either of the following ways:

• Put the declarations in an ESQL declare section:
EXEC SQL BEGIN DECLARE SECTION;
-- put host variable declarations here
EXEC SQL END DECLARE SECTION;

Make sure you terminate the statements EXEC SQL BEGIN DECLARE SECTION and EXEC SQL END DECLARE SECTION with semicolons.

• Preface each declaration with a dollar sign ($).

Important: Using the EXEC SQL BEGIN DECLARE SECTION and EXEC SQL END DECLARE SECTION keywords conforms to ANSI standards.

Within the declaration itself, you must specify the following information:

• The name of the host variable
• The data type of the host variable
• The initial value of the host variable (optional)
• The scope of the host variable (which the placement of the declaration within the program determines)

For examples of the EXEC SQL and dollar sign ($) formats for host-variable declarations, see Sample Host-Variable Declarations.

The name of the host variable must conform to the naming conventions of the C language. In addition, you must follow any limitations that your C compiler imposes. In general, a C variable must begin with a letter or an underscore (_) and can include letters and digits and underscores.

Warning: Many variable names used in the implementation of the ESQL/C product begin with an underscore. To avoid conflicting with internal ESQL/C variable names, avoid using an underscore for the first character of a variable name.

C variable names are case sensitive, so the variables hostvar and HostVar are distinct. (For more information, see Handling Case Sensitivity in Embedded SQL Statements.)

You can use non-ASCII (non-English) characters in ESQL/C host-variable names if your client locale supports these non-ASCII characters. For more information on how the client locale affects host-variable names, see the Informix Guide to GLS Functionality.

Tip: Good programming practice requires that you create a naming convention for host-variable names.

Host-Variable Data Types

Because a host variable is a C variable, you must assign a C data type to the variable when you declare it. Likewise, when you use a host variable in an SQL statement, you also associate it with an SQL data type.

For more information about the relationship between SQL data types and C data types, ../sqlr/intro.htmler to Chapter 3, INFORMIX-ESQL/C Data Types. In addition, the Informix Guide to SQL: Reference contains information on the SQL data types.

You can also declare host variables as many of the more complex C data types, such as pointers, structures, typedef expressions, and function parameters. For more information, see Using Host Variables in Data Structures.

ESQL/C allows you to declare host variables with normal C initializer expressions. Some valid examples of C initializers follow:

EXEC SQL BEGIN DECLARE SECTION;

	int  varname  =  12;

	long  cust_nos[8]  =  {0,0,0,0,0,0,0,9999};
	char descr[100] = "Steel eyelets; Nylon cording.";

EXEC SQL END DECLARE SECTION;

The ESQL/C preprocessor does not check initializer expressions for valid C syntax; it simply copies them to the C source file. The C compiler diagnoses any errors.

The scope of reference, or simply the scope, of a host variable is that portion of the program in which the host variable can be accessed. The placement of the ESQL/C declaration statement determines the scope of the variable as follows:

• If the declaration statement is inside a program block, the variable is local to that program block.

Only statements within that program block can access the variable.

• If the declaration statement is outside a program block, the variable is modular.

All program blocks that occur after the declaration can access the variable.

Host variables that you declare within a block of code are local to that block. You define a block of code with a pair of curly braces ({ }).

For example, the host variable blk_int in Figure 1-4 is valid only in the block of code between the curly braces, whereas p_int is valid both inside and outside the block.

EXEC SQL BEGIN DECLARE SECTION;

          int p_int;

EXEC SQL END DECLARE SECTION;


EXEC SQL select customer_num into :p_int from customer

	where lname = "Miller";


{

	EXEC SQL BEGIN DECLARE SECTION;

		int blk_int;

	EXEC SQL END DECLARE SECTION;



	blk_int = p_int;
	

	EXEC SQL select customer_num into :blk_int from customer

		where lname = "Miller";


}

You can nest blocks up to 16 levels. The global level counts as level one.

The following C rules govern the scope of ESQL/C host variables as well:

• A host variable is an automatic variable unless you explicitly define it as an external or static variable on unless it is defined outside of any function.
• A host variable that a function declares is local to that function and masks a definition with the same name outside the function.
• You cannot define a host variable more than once in the same block of code.

Figure 1-5 shows an example of how to use the EXEC SQL syntax to declare host variables.

EXEC SQL BEGIN DECLARE SECTION;

          char      *hostvar;          /* pointer  to  a  character  */

          int        hostint;            /*  integer  */

          double  hostdbl;            /*  double  */

          char      hostarr[80];    /*  character  array  */

          struct  {

                int  svar1;

                int  svar2;
                

                }  hoststruct;          /*  structure  */

EXEC SQL END DECLARE SECTION;

Figure 1-6 shows an example of how to use the dollar sign ($) notation to declare host variables.

        $char      *hostvar;

        $int        hostint;

        $double  hostdbl;

        $char      hostarr[80];



        $struct  {

                int  svar1;

                int  svar2;
                

        }  hoststruct;

For information on how to use a host variable in an SQL statement, see Specifying Host Variables.

You can use host variables to contain the following types of information:

• SQL identifiers. SQL identifiers include names of parts of the database such as tables, columns, indexes, and views.
• Data. Data is information that the database server fetches from or stores in the database. This information can include null values. A null value indicates that the value of the column or variable is unknown.

Host variables can appear within an SQL statement as ../sqls/intro.htmltax allows. (For information about the syntax of SQL statements, see the Informix Guide to SQL: Syntax.) However, you must precede the host-variable name with a symbol to distinguish it from regular C variables. For more information, see Specifying Host Variables.

An SQL identifier is the name of a database object. The following objects are examples of SQL identifiers:

• Parts of the database schema such as tables, columns, views, indexes, synonyms, and stored procedure names
• Dynamic ESQL/C structures such as cursors and statement IDs

As syntax allows, you can use a host variable within an embedded SQL statement to hold the name of an SQL identifier.

For information on the sizes and naming conventions for SQL identifiers, see the Identifier segment in the Informix Guide to SQL: Syntax.

Using Long Identifiers with Version 9.2

Beginning with Version 9.2, Informix Dynamic Server and Informix Dynamic Server with Universal Data Option, allow identifiers of up to 128 characters in length, and user names up to 32 characters in length. Other versions of Informix database servers support an identifier length of 18 characters and a user-name length of 8 characters.

The database server uses the following two criteria to determine whether the client program can receive long identifiers:

• The internal version number of the client program
• The setting of the ifx_longid environment variable

If the IFX_LONGID variable is set to a value other than 1 or 0 (zero), the database server makes the determination based solely on the version number. If the version number is greater than or equal to 9.20, the database server considers the client program able to receive long identifiers.

You can override the version criteria by setting the IFX_LONGID environment variable. If the version number is greater than or equal to 9.20 but the program is not able to handle long identifiers or long user names, you can set IFX_LONGID to 0 to inform the database server that the client program cannot receive long identifiers. You can set IFX_LONGID to 1 to specify that your program is capable of handling long identifiers, regardless of the internal version number.

Important: If you set the IFX_LONGID environment variable for the database server all client programs must adhere to the setting.

For more information on the IFX_LONGID environment variable, ../sqlr/intro.htmler to the Informix Guide to SQL: Reference.

Client programs that meet the following conditions can use long identifiers and long user names without recompiling:

• Have version greater than 9.20
• Use shared libraries (that is, program was compiled without the -static option)

For more information on how to use shared libraries, refer to Specifying Versions of Informix General Libraries.

If the database server truncates a long identifier or long user name, it sets a the SQLSTATE variable to `01004' and sets the sqlwarn1 flag to `W' in the SQL Communications Area (sqlca). For more information, refer to Chapter 11, Exception Handling.

If an identifier name does not conform to naming conventions, you must use a delimited identifier. A delimited identifier is an SQL identifier that is enclosed in double quotes (" ").

Important: When you use double quotes (" ") to delimit identifiers, you conform to ANSI standards; single quotes (' ') delimit strings.

Use delimited identifiers when your program must specify some identifier name that would otherwise be syntactically invalid. Examples of possible invalid identifiers include:

• an identifier that is the same as an SQL reserved word.

(For a list of SQL reserved words, see the description of identifiers in the Informix Guide to SQL: Syntax.)

• an identifier that contains nonalphabetic characters.

To use delimited identifiers, you must compile and run your ESQL/C program with the DELIMIDENT environment variable set. You can set DELIMIDENT at either of the following phases:

• At compile time, the ESQL/C preprocessor allows quoted strings in areas of the SQL syntax where identifiers are valid.
• At runtime, the database server accepts quoted strings in dynamic SQL statements where identifiers are valid.

Database utilities such as dbexport and DB-Access also accept delimited identifiers.

Important: When you use the DELIMIDENT environment variable, you can no longer use double quotes (" ") to delimit strings. If you want to indicate a quoted string, enclose the text with single quotes (' ').

Delimited identifiers are case sensitive. All database object names that you place within quotes maintain their case. Keep in mind that ESQL/C restricts identifier names to a maximum of 128 characters.

Figure 1-7 shows a delimited identifier that specifies nonalphabetic characters in both a cursor name and a statement ID.

EXEC SQL BEGIN DECLARE SECTION;

	char curname1[10];

	char stmtname[10];

EXEC SQL END DECLARE SECTION;



stcopy("%#!", curname1);

stcopy("(_=", stmtname);

EXEC SQL prepare :stmtname from 

	'select customer_num from customer';

EXEC SQL declare :curname1 cursor for $stmtname;

EXEC SQL open :curname;

In Figure 1-7, you can also list the cursor name or statement ID directly in the SQL statement. For example, the following PREPARE statement is also valid (with DELIMIDENT set):

EXEC SQL prepare "%#!" from 

	'select customer_num from customer';

If you set DELIMIDENT, the SELECT string in the preceding PREPARE statement must be enclosed in single quotes for the preprocessor to treat it as a string. If you enclose the statement in double quotes, the preprocessor treats it as an identifier.

To declare a cursor name that contains a double quote, you must use escape characters in the delimited identifier string. For example, to use the string "abc" as a cursor name, you must escape the initial quote in the cursor name:

EXEC SQL BEGIN DECLARE SECTION;

	char curname2[10];

	char stmtname[10];

EXEC SQL END DECLARE SECTION;



stcopy("\"abc\"", curname2);

EXEC SQL declare :curname2 cursor for :stmtname;

In the preceding example, the cursor name requires several escape characters:

• The backslash (\) is the C escape character. You need it to escape the double quote.

Without the escape character, the C compiler would interpret the double quote as the end of the string.

• The cursor name must contain two double quotes.

The first double quote escapes the double quote and the second double quote is the literal double quote. The ANSI standard states that you cannot use a backslash to escape quotes. Instead, you must escape the quote in the cursor name with another quote.

Figure 1-8 shows the string that contains the cursor name as it is processed.

The following restrictions apply to delimited identifiers:

• You cannot use a delimited identifier for a database name.

This restriction prevents the creation of a database name such as " " and avoids conflict with the syntax for INFORMIX-SE database names.

• You cannot use a delimited identifier for a storage identifier, for instance, the name of a dbspace.

The DELIMIDENT environment variable applies only to database identifiers.

A null value represents unknown or not applicable values. This value is distinct from all legal values in any given data type. The representation of null values depends on both the computer and the data type. Often, the representation does not correspond to a legal value for the C data type. Do not attempt to perform arithmetic or other operations on a host variable that contains a null value.

A program must, therefore, have some way to recognize a null value. To handle null values, ESQL/C provides the following features:

• The risnull() and rsetnull() library functions enable you to test whether a host variable contains a null value and to set a host variable to a null value, respectively.

For a description of these library functions, see Appendix B, ESQL/C Function Library.

• Indicator variables are special ESQL/C variables that you can associate with host variables that hold values for database columns that allow null values.

The value of the indicator variable can show whether the associated host variable contains a null value. For more information, see Using Indicator Variables.

Null Values in ANSI-Compliant Databases

In an ANSI-compliant database, a host variable that is used in an INSERT statements or in the WHERE clause of any SQL statement must be null terminated.

ESQL/C supports the use of host variables in the following data structures:

• Arrays
• C structures (struct)
• C typedef statements
• Pointers
• Function parameters

ESQL/C supports the declaration of arrays of host variables. You must provide an integer value as the size of the array when you declare the array. An array of host variables can be either one or two dimensional.

You can use elements of an array within ESQL/C statements. For example, if you provide the following declaration:

EXEC SQL BEGIN DECLARE SECTION;

	long  customer_nos[10];

EXEC SQL END DECLARE SECTION;

you can use the following syntax:

for  (i=0;  i<10;  i++)

{

	EXEC SQL fetch  customer_cursor  into  :customer_nos[i];
	

}

You can also use the array name alone within some SQL statements if the array is of type CHAR. For information on specific statements, consult the Informix Guide to SQL: Syntax.

ESQL/C supports the declaration of a C structure (struct) as a host variable. You can use the components of the structure within ESQL/C statements.

The following definition of the cust_rec variable serves as a host variable for the first three columns of the customer table in the stores7 database:

EXEC SQL BEGIN DECLARE SECTION;

	struct  customer_t

		{

		int	c_no;

		char	fname[32];

		char	lname[32];

		}  cust_rec;

EXEC SQL END DECLARE SECTION;

The following INSERT statement specifies the components of the cust_rec host variable in its VALUES clause:

EXEC SQL insert  into  customer  (customer_num, fname, lname)

	values  (:cust_rec.c_no,  :cust_rec.fname,

	:cust_rec.lname);

If an SQL statement requires a single host variable, you must use the structure component name to specify the host variable. Informix requires structure component names in the SET clause of an UPDATE statement.

In SQL statements that allow a list of host variables, you can specify the name of the C structure and ESQL/C expands the name of the structure variable to each of its component elements. You can use this syntax with SQL statements such as the FETCH statement with an INTO clause or the INSERT statement with a VALUES clause.

The following INSERT statement specifies the entire cust_rec structure in its VALUES clause:

EXEC SQL insert into customer (customer_num, fname, lname) 

	values (:cust_rec);

This insert performs the same task as the insert that specifies the individual component names of the cust_rec structure.

ESQL/C supports the C typedef statements and allows the use of typedef names in declaring the types of host variables. For example, the following code creates the smallint type as a short integer and the serial type as a long integer. It then declares a row_nums variable as an array of serial variables and a variable counter as a smallint.

EXEC SQL BEGIN DECLARE SECTION;

	typedef  short  smallint;  

	typedef  long  serial;



	serial  row_nums  [MAXROWS];

	smallint  counter;

EXEC SQL END DECLARE SECTION;

You cannot use a typedef statement that names a multidimensional array, or a union, or a function pointer, as the type of a host variable.

You can use a pointer as a host variable as long as your program uses the pointer to input data to an SQL statement. For example, Figure 1-9 shows how you can associate a cursor with a statement and insert values into a table.

EXEC SQL BEGIN DECLARE SECTION;

	char *s;

	char *i;

EXEC SQL END DECLARE SECTION;



/* Code to allocate space for two pointers not shown */



s = "select * from cust_calls";

i = "NS";


EXEC SQL prepare x from :s;

EXEC SQL insert into state values (:i, 'New State');

Figure 1-10 shows how to use an integer pointer to input data to an INSERT statement.

EXEC SQL BEGIN DECLARE SECTION;

	short *i;

	int *o;

	short *s;

EXEC SQL END DECLARE SECTION;



short i_num = 3;

int o_num = 1002;

short s_num = 8;



i = &i_num;

o = &o_num;

s = &s_num;



EXEC SQL connect to 'stores7';

EXEC SQL insert into items values (:*i, :*o, :*s, 'ANZ', 5, 
125.00);

EXEC SQL disconnect current; 

If you use a host variable that is a pointer to char to receive data from a SELECT statement, you receive a compile-time warning and your results might be truncated.

You can use host variables as parameters to functions. You must precede the name of the host variable with the parameter keyword to declare it as a function parameter. For example, Figure 1-11 shows a code fragment with a Kernighan and Ritchie-style prototype declaration that expects three parameters, two of which are host variables.

f(s, id, s_size)

EXEC SQL BEGIN DECLARE SECTION;

	PARAMETER char s[20];

	PARAMETER int id;

EXEC SQL END DECLARE SECTION;

int s_size;

{

	select fname into :s from customer 

		where customer_num = :id;


}

You can also declare parameter host variables with the dollar sign ($) notation. For example, Figure 1-12 shows the function header in Figure 1-11, with the dollar sign ($) notation.

f(s, id, s_size)

$parameter char s[20];

$parameter int id;

int s_size;

You can declare parameters in an ANSI-style prototype function declaration as host variables as well. You can also put all parameters to a prototype function declaration inside the EXEC SQL declare section, even if some of the parameters cannot be used as host variables. Figure 1-13 on page 1-30 shows that the function pointer f can be included in the EXEC SQL declare section, even though it is not a valid host-variable type and cannot be used as a host variable.

int * foo(

EXEC SQL BEGIN DECLARE SECTION;

	PARAMETER char s[20],

	PARAMETER int id,

	PARAMETER int (*f) (double)
EXEC SQL END DECLARE SECTION;

)

{

	select fname into :s from customer 

		where customer_num = :id;


}

The functionality that allows inclusion of function parameters inside of the EXEC SQL declare section is in compliance with the requirement that any valid C declaration syntax must be allowed inside the EXEC SQL declare sections to use common header files for C and ESQL/C source files. For more information on how to use common header files between C and ESQL/C source files, see Defining Host Variables Based on C #defines and typedefs.

Important: If you want to define ESQL/C host variables that are ANSI-style parameters, you must use the EXEC SQL BEGIN DECLARE SECTION and the EXEC SQL END DECLARE SECTION syntax. You cannot use the $BEGIN DECLARE and $END DECLARE syntax. This restriction is because SQL statements that begin with the dollar sign ($) notion must end with a semicolon (;). However, ANSI syntax requires that each parameter in a parameter list should not end with a semicolon terminator, but with a comma (,) delimiter.

The following limitations apply to using host variables as function parameters:

• You cannot declare a parameter variable inside a block of C code.
• You cannot use the parameter keyword in declarations of host variables that are not part of a function header. If you do, you receive unpredictable results.

This section describes the following topics about ESQL/C host variables that are unique to the Windows environments:

• How to declare host variables with non-ANSI storage-class modifiers
• How global ESQL/C variables are declared

The ANSI C standards define a set of storage-class specifiers for variable declarations. C compilers in Windows environments often support non-ANSI storage-class specifiers. To provide support for these non-ANSI storage-class specifiers in ESQL/C host-variable declarations, the ESQL/C preprocessor supports the form of the ANSI syntax that Figure 1-14 shows.

Figure 1-14

ESQL/C Syntax for Non-ANSI Storage-Class Specifiers

For example, the Microsoft Visual C++ compiler supports the declspec compiler directive to enable you to declare extended storage-class attributes. This compiler directive has the following syntax:

__declspec(attribute) var_type var_name;

In this example, attribute is a supported keyword (such as thread, dllimport, or dllexport), var_type is the data type of the variable, and var_name is the variable name.

To enable you to declare ESQL/C host variables as extended storage-class variables, the ESQL/C preprocessor supports the declspec directive with the following syntax:

@("__declspec(attribute)") var_type var_name;

In this example, attribute, var_type, and var_name are the same as in the previous example. You might find it convenient to declare a macro for the declspec syntax. The following example declares threadCount as an instance-specific integer variable of the thread-extended storage class:

#define DLLTHREAD __declspec(thread)


EXEC SQL BEGIN DECLARE SECTION;

	@("DLLTHREAD") int threadCount;

EXEC SQL END DECLARE SECTION;

This example creates the DLLTHREAD macro to simplify the declaration of thread-extended storage-class attributes. You can declare similar macros to simplify declaration of variables to be exported (or imported) to the dynamic link library (DLL), as follows:

#define DLLEXPORT __declspec(dllexport);


EXEC SQL BEGIN DECLARE SECTION;

	@("DLLEXPORT") int winHdl;

EXEC SQL END DECLARE SECTION;

When an SQL statement returns a value, it returns it in the host variable for the specified column. In some cases, you can associate an indicator variable with the host variable to obtain additional information about the value that is returned. If you specify an indicator variable, ESQL/C sets it in addition to returning the value to the host variable.

The indicator variable provides additional information in the following situations:

• If the host variable is associated with a database column or an aggregate function that allows null values, the indicator variable can specify whether the value is null.
• If the host variable is a character array and the column value is truncated by the transfer, the indicator variable can specify the size of the returned value.

The following sections describe how to declare an indicator variable and associate it with a host variable, and also how ESQL/C sets an indicator variable to specify the two preceding conditions.

You declare indicator variables in the same way as host variables, between BEGIN DECLARE SECTION and END DECLARE SECTION statements as the following example shows:

EXEC SQL BEGIN DECLARE SECTION;

	-- put indicator variable declarations here

EXEC SQL END DECLARE SECTION;

For more information, see Declaring a Host Variable.

Indicator variables can be any valid host-variable data type except DATETIME or INTERVAL. Usually, you declare an indicator variable as an integer. For example, suppose your program declares a host variable called name. You can declare a short integer-indicator variable called nameind, as the following example shows:

EXEC SQL BEGIN DECLARE SECTION;

	char    name[16];

	short nameind;

EXEC SQL END DECLARE SECTION;

You can use non-ASCII (non-English) characters in ESQL/C indicator-variable names if your client locale supports these non-ASCII characters. For more information on how the client locale affects host-variable names, see the Informix Guide to GLS Functionality.

You associate an indicator variable with its host variable in one of the following two ways:

• Prefix the indicator variable with a colon (:) and place the keyword INDICATOR between the host variable name and the indicator variable name as follows:
:hostvar INDICATOR :indvar
• Place a separator symbol between the host variable name and the indicator variable name. The following separator symbols are valid:
• A colon (:)
:hostvar:indvar
• A dollar sign ($)
$hostvar$indvar

You can use a dollar sign ($) instead of a colon (:), but the colon makes the code easier to read.

You can have one or more white-space characters between the host variable and indicator variable. For example, both of the following formats are valid to specify an indicator variable, hostvarind, on the hostvar host variable:

$hostvar:hostvarind
$hostvar :hostvarind

When an ESQL/C statement returns a null value to a host variable, the value might not be a meaningful C value. Your program can take one of the following actions:

• If you have defined an indicator variable for this host variable, ESQL/C sets the indicator variable to -1.

Your program can check the indicator variable for a value of -1.

• If you did not define an indicator variable, the runtime behavior of ESQL/C depends on how you compiled the program:
• If you compile the program with the -icheck preprocessor option, ESQL/C generates an error and sets sqlca.sqlcode to a negative value when the database server returns a null value. (See Syntax of the esql Command.)
• If you compile the program without the -icheck option, ESQL/C does not generate an error when the database server returns a null value. In this case, you can use the risnull() function to test the host variable for a null value.

If the value returned to the host variable is not null, ESQL/C sets the indicator variable to 0. If the SQL operation is not successful, the value of the indicator variable is not meaningful. Therefore, you should check the outcome of the SQL statement before you check for a null value in the host variable. For information on exception handling, refer to Chapter 11, Exception Handling.

The NULL keyword of an INSERT statement allows you to insert a null value into a table row. As an alternative to the NULL keyword in an INSERT statement, you can use a negative indicator variable with the host variable.

When you return aggregate function values into a host variable, keep in mind that when the database server performs an aggregate function on an empty table, the result of the aggregate operation is the null value. The only exception to this rule is the COUNT(*) aggregate function, which returns a zero (0) in this case.

Important: If you activate the DATASKIP feature of the database server, an aggregate function also returns null if all fragments are off-line or if all the fragments that are on-line are empty.

The DATASKIP feature is not available for INFORMIX-SE.

When an SQL statement returns a non-null value into a host-variable character array, it might truncate the value to fit into the variable. If you define an indicator variable for this host variable, ESQL/C:

• sets the SQLSTATE variable to "01004" to signal the occurrence of truncation.

(For more information on SQLSTATE, see List of SQLSTATE Class Codes.) ESQL/C also sets sqlwarn1 of the sqlca.sqlwarn structure to W.

• sets the associated indicator variable equal to the size in bytes of the SQL host variable before truncation.

If you do not define an indicator variable, ESQL/C still sets SQLSTATE and sqlca.sqlwarn to signal the truncation. However, your program has no way to determine how much data was truncated.

If the database server returns a value that is neither truncated nor null, ESQL/C sets the indicator variable to 0.

The code segments in Figure 1-15 and Figure 1-16 on page 1-37 show examples of how to use indicator variables with host variables. Both examples use indicator variables to perform the following tasks:

• Determine if truncation has occurred on a character array

If you define lname in the customer table with a length that is longer than 15 characters, nameind contains the actual length of the lname column. The name host variable contains the first 15 characters of the lname value. (The string name must be terminated with a null character.) If the last name of the company representative with customer_num = 105 is shorter than 15 characters, ESQL/C truncates only the trailing blanks.

• Check for a null value

If company has a null value for this same customer, compind has a negative value. The contents of the character array comp cannot be predicted.

Figure 1-15 shows an ESQL/C program that uses the EXEC SQL syntax for the SQL statements.

EXEC  SQL  BEGIN  DECLARE  SECTION;

	char      name[16];

	char      comp[20];

	short    nameind;

	short    compind;

EXEC  SQL  END  DECLARE  SECTION;


EXEC  SQL select  lname,  company

	into  :name INDICATOR :nameind,  :comp INDICATOR :compind

	from  customer

	where  customer_num  =  105;

Figure 1-15 uses the INDICATOR keyword to associate the main and indicator variables. This method complies with the ANSI standard.

Figure 1-16 shows an ESQL/C program that uses the dollar sign ($) format for the SQL statements.

$char      name[16];

$char      comp[20];

$short    nameind;

$short    compind;


$select  lname,  company

            into  $name$nameind,  $comp$compind

            from  customer

            where  customer_num  =  105;