Informix-ESQL/C Programmer's Manual

Informix-ESQL/C Programmer's Manual
Chapter 1: Programming with INFORMIX-ESQL/C

Home Contents Index Master Index New Book

Declaring and Using Host Variables in SQL Statements

In an ESQL/C application that uses embedded SQL statements, the SQL statements can refer to the contents of host variables. A host variable is a C program variable that you use in an embedded SQL statement. Informix uses the term host variable because you declare the variable in the host program, the C program. The SQL statements are guests in the host program. They use host variables to transfer information between the C host 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, you must follow these steps in your ESQL/C program:

1. Declare the host variable within the C program.

2. Assign a value to the host variable.

3. Specify the host variable in an embedded SQL statement.

EXEC SQL BEGIN DECLARE SECTION;

	int  varname  =  12;

	long  cust_nos[8]  =  {0,0,0,0,0,0,0,9999};

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.

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:

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 braces ({ }).

For example, the host variable blk_int in Figure 1-8 is valid only in the block of code between the 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";
.
.
.

}

Figure 1-8
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 that govern regular C variables 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.
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.

Sample Host-Variable Declarations

Figure 1-9 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-9
Declaring Host Variables with the EXEC SQL Syntax

Figure 1-10 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;

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

For information on how to use a host variable in an SQL statement, see page 1-21.

Assigning a Value to a Host Variable

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. Null values are special values that indicate an unknown value.

Host variables can appear within an SQL statement as syntax 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:

Parts of the database schema such as tables, columns, views, indexes, synonyms, and stored procedure names
Dynamic database 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.

The identifier name can consist of letters, numbers, and underscores (_). The first character must be a letter. In most contexts, an identifier can have from 1 to 18 characters. (For more information on naming conventions for SQL identifiers, see the Identifier segment in the Informix Guide to SQL: Syntax.)

If an identifier name does not conform to these 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 18 characters.

Figure 1-11 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;

Figure 1-11
Using Delimited Identifiers for a Cursor Name

In Figure 1-11, you could 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];

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.

ANSI

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

Figure 1-12
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:

You cannot use a delimited identifier for a database name.

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

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

DELIMIDENT

only

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:

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

Chapter 2, "INFORMIX-ESQL/C Data Types."

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"

Using Host Variables in Data Structures

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

Arrays
C structures (struct)
C typedef expressions
Pointers
Function parameters

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:

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.

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:

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.

C typedef Expressions as Host Variables

ESQL/C supports standard C typedef expressions and allows their use as 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 expression that names a multidimensional array or a union as 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-13 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-13
Declaring a Character Pointer to Input Data

Figure 1-14 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;

Figure 1-14
Declaring an Integer Pointer to Input Data

If you use a pointer that is a host variable 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-15 shows a code fragment with Kernighan & 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;
.

.

.


}

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

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

f(s, id, s_size)
$parameter char s[20];
$parameter int id;
int s_size;

Figure 1-16
Using $ 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. It is also possible to 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-17 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 * f(
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;
.
.
.

}

Figure 1-17
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 of 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 "Running the C Preprocessor Before the ESQL/C Preprocessor".

Important: If you want to define ESQL/C host variables which 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 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.

Using Indicator Variables

When an SQL statement returns a value, it puts this value in the associated host variable. In some situations, you can use an indicator variable to provide additional information about the value that is returned. An indicator variable is an ESQL/C variable that is associated with a host variable. When you pair a host variable and an indicator variable in this way, Informix calls the host variable the main variable. When ESQL/C returns a value into the main variable, it can also set the indicator variable so that your program can determine more about the value.

The indicator variable provides additional information in the following situations:

If you associate a host variable with an aggregate function or with a database column that allows null values, and the host variable receives a null value, this value is often not distinct from other values.
If the host variable is a character array and its value has been truncated, the size of the variable does not correctly indicate the size of the returned value.

This section describes how your program can use indicator variables to determine additional information about a returned value. Specifically, it contains sections about the following topics:

How ESQL/C sets indicator variables to indicate the preceding two conditions
How your program can declare an indicator variable
How your program can associate an indicator variable with a host variable
An example program that uses indicator variables

Values in Indicator Variables

ESQL/C can set an indicator variable in the following two cases:

To indicate that the associated host variable contains a null value
To indicate that a value in a host-variable character array has been truncated

Indicating Null Values

When an ESQL/C statement returns a null value to a host variable (through the INTO clause of a SELECT or FETCH statement), the actual value in the main variable 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 for this value to determine whether a value is null.

If you did not define an indicator variable, the behavior of ESQL/C at runtime depends on how you compile the program:
- If you compile the program with the -icheck preprocessor option, ESQL/C generates an error when the database server returns a null value and sets sqlca.sqlcode to a negative value. (See Chapter 7, "Working with INFORMIX-Universal Server Smart Large Objects.")
- If you compile the program without the -icheck option, ESQL/C does not generate an error when the database server returns a null value. Therefore, your program has no way to recognize when the database server returns a null value.

If the return code is not null, ESQL/C sets the indicator variable to 0.

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 in this case returns a zero (0).

Important: If you use the DATASKIP feature of the database server and you turn this feature on, an aggregate function might also return null if all the fragments that are on-line are empty or if all fragments are off-line. When DATASKIP is off, the values of the indicator variables (or the risnull() function) are not valid if the operation is not successful; therefore, check the SQLCODE value after the operation and before you check for null values.

SE

The DATASKIP feature is not available for 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:

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 has been truncated.

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

Declaring Indicator Variables

You declare indicator variables in the same way as host variables. You put the declaration within an ESQL/C block, as shown in the following example:

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 of 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 that is called name. You could declare a short integer-indicator variable that is called nameind, as the following example shows:

EXEC SQL BEGIN DECLARE SECTION;

	char    name[16];

	short nameind;

EXEC SQL END DECLARE SECTION;

GLS

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 a host variable and its indicator variable when you list them in an SQL statement. You can associate these variables in one of two ways:

Prefix the indicator variable with a colon (:) and place the keyword INDICATOR between the main variable name and the indicator variable name as follows: :hostvar INDICATOR :hostvarind

Important: The "INDICATOR :hostvarind" format conforms to ANSI standards.

Place a separator symbol between the main variable name and the indicator variable name. The following separator symbols are valid:
- A colon (:) $hostvar:hostvarind
- A dollar sign ($) $hostvar$hostvarind

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

An Example Using Indicator Variables

The code segments in Figure 1-18 and Figure 1-19 show examples of how to use indicator variables with host variables. Both examples use indicator variables to perform the following tasks: