![]() |
|
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.
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:
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:
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:
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:
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.
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:
Figure 1-5 shows an example of how to use the EXEC SQL syntax to declare host variables.
Figure 1-6 shows an example of how to use the dollar sign ($) notation to declare host variables.
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:
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:
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.
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 IdentifiersIf 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.
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
Processor | After Processing |
---|---|
ESQL/C preprocessor | \"\"abc |
C Compiler | ""abc |
ANSI-compliant database server | "abc |
The following restrictions apply to delimited 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:
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:
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.
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.
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.
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-10 shows how to use an integer pointer to input data to an INSERT statement.
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.
|
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.
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:
This section describes the following topics about ESQL/C host variables that are unique to the Windows environments:
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-14ESQL/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:
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:
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.
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.
You associate an indicator variable with its host variable in one of the following two ways:
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.
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.
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 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.