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

Declaring and Using Host Variables

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.

To use a host variable in an SQL statement

  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.

Declaring a Host Variable

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:

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

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

Host-Variable Names

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.

Initial Host-Variable Values

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

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.

Scope of Host Variables

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:

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.


Figure 1-4
Declaring Host Variables Inside and Outside a Code Block

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:

Sample Host-Variable Declarations

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


Figure 1-5
Declaring Host Variables with the EXEC SQL Syntax

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


Figure 1-6
Declaring Host Variables with the Dollar Sign ($) Notation

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

Assigning a Value to a Host Variable

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

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.

SQL Identifiers

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

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:

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:

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.

Delimited Identifiers

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:

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:

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.


Figure 1-7
Using Delimited Identifiers for a Cursor Name

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):

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:

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

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

Figure 1-8
Escaped Cursor Name String As It Is Processed

Processor After Processing
ESQL/C preprocessor \"\"abc
C Compiler ""abc
ANSI-compliant database server "abc

The following restrictions apply to delimited identifiers:

Null Values in Host Variables

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:

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.

Using Host Variables in Data Structures

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

Arrays of Host Variables

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:

you can use the following syntax:

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.

C Structures as Host Variables

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:

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

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:

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

C typedef Statements as Host Variables

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.

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.

Pointers as Host Variables

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.


Figure 1-9
Declaring a Character Pointer to Input Data

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


Figure 1-10
Declaring an Integer Pointer to Input Data

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.

Function Parameters as Host Variables

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.


Figure 1-11
Using EXEC SQL to Declare Host Variables as Parameters to a Kernighan and Ritchie-Style Function Declaration

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.

Figure 1-12
Using the Dollar Sign ($) to Declare Host Variables as Parameters to a Function

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.


Figure 1-13
Using EXEC SQL to Declare Host Variables as Parameters to ANSI-Style Function Declaration

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:

Using Host Variables in Windows Environments

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

Declaring Variables with Non-ANSI Storage-Class Modifiers

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

Element Purpose Restrictions Syntax
modifier name Text that you want to pass to the C compiler for translation. This text is usually the name of the storage-class modifier. The modifier must be valid for your C compiler or be a name that you define in your program. See your C compiler documentation.
variable name Identifier name of the ESQL/C host variable None. See Declaring a Host Variable.
variable type Data type of the ESQL/C host variable The type must be a valid C or ESQL/C data type. See Declaring a Host Variable.

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:

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:

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:

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:

Using Indicator Variables

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:

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.

Declaring Indicator Variables

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:

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:

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.

Associating an Indicator Variable with a Host Variable

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

Indicating Null Values

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

Indicating Truncated Values

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:

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.

An Example Using Indicator Variables

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:

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


Figure 1-15
Using Indicator Variables with
EXEC SQL and
the Colon (:) Symbol

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.


Figure 1-16
Using Indicator Variables with
the Dollar Sign ($) Notation


INFORMIX-ESQL/C Programmer's Manual, Version 9.21
Copyright © 1999, Informix Software, Inc. All rights reserved