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

An ESQL/C program can use SQL statements to communicate with the database server. The program can use both static and dynamic SQL statements. A static SQL statement is one in which all the components are known when you compile the program. A dynamic SQL statement is one in which you do not know all the components at compile time; the program receives all or part of the statement at runtime. For a description of dynamic SQL, see Chapter 14, Using Dynamic SQL.

You can embed SQL statements in a C function with one of two formats:

• The EXEC SQL keywords:
EXEC SQL SQL_statement;

Using EXEC SQL keywords is the ANSI-compliant method to embed an SQL statement.

• The dollar sign ($) notation:
$SQL_statement;

In either of these formats, replace SQL_statement with the complete text of a valid statement. ESQL/C statements can include host variables in most places where you can use a constant. For any exceptions, see the ../sqls/intro.htmltax of individual statements in the Informix Guide to SQL: Syntax.

This section describes the following topics that are related to using embedded SQL statements in an ESQL/C program:

• Case sensitivity of identifiers
• Using quotes and escape characters
• Commenting statements
• Host variables
• Header files

Figure 1-1 describes how the ESQL/C preprocessor treats uppercase and lowercase letters.

Using Quotes and Escape Characters

An escape character indicates to the ESQL/C preprocessor that it should print the following character as a literal character instead of interpreting it. You can use the escape character with an interpreted character to make the compiler escape, or ignore, the interpreted meaning.

In ANSI SQL, the backslash character (\) is the escape character. To search for data that begins with the string \abc, the WHERE clause must use an escape character as follows:

... where col1 = '\\abc';

However, ANSI standards specify that using the backslash character (\) to escape single (' ') or double (" ") quotation marks is illegal. For example, the following attempt to find a single quote does not conform to ANSI standards:

... where col1 = '\'';

In nonembedded tools such as DB-Access, you can escape a quote with either of the following methods:

• You can use the same quotation mark as an escape character, as follows:
... where col1 = '''';
• You can use an alternative quotation mark. For example, to look for a double quote, you can enclose this double quote with single quotes, as follows:
... where col1 = ' "';

Figure 1-2 shows a SELECT statement with a WHERE clause that contains a double quote enclosed with single quotes.

EXEC SQL select col1 from tab1 where col1 = '  "';

For the WHERE clause in Figure 1-3, the ESQL/C preprocessor does not process a double quote; it passes it on to the C compiler. When the C compiler receives the string ' " ' (double quote enclosed with single quotes), it interprets the first single quote as the start of a string and the double quote as the end of a string. The compiler cannot match the single quote that remains and therefore generates an error.

To cause the C compiler to interpret the double quote mark as a character, precede the double quote mark with the C escape character, the backslash (\). The following example illustrates the correct syntax for the query in Figure 1-3:

EXEC SQL select col1 from tab1 where col1 = '\"';

Because both C and ANSI SQL use the backslash character as the escape character, be careful when you search for the literal backslash in embedded queries. The following query shows the correct syntax to search for the string "\" (where the double quotes are part of the string):

EXEC SQL select col1 from tab1 where col1 = '\"\\\\\"';

This string requires five backslashes to obtain the correct interpretation. Three of the backslashes are escape characters, one for each double quote and one for the backslash. Figure 1-3 shows the string after it passes through each of the processing steps.

ESQL/C supports strings in either single quotes ('string') or double quotes ("string"). However, the C language supports strings only in double quotes. Therefore, the ESQL/C preprocessor converts every statement in an ESQL/C source file into a double-quoted string.

ESQL/C does not allow a newline character (0x0A) in a quoted string. The database server does allow a newline character in a quoted string, however, if you specify that you want to allow it. Consequently, you can include the newline character in a quoted string that is part of a dynamically prepared SQL statement because the database server, rather than ESQL/C, processes the prepared statement. For more information about dynamic SQL programs, see Chapter 14, Using Dynamic SQL.

You can specify that you want the database server to allow the newline character in a quoted string either on a per session basis or on an all session basis. A session is the duration of the client connection to the database server.

To allow or disallow a newline character in a quoted string for a particular session, you must execute the user-defined routine ifx_allow_newline(boolean). The following example illustrates how to invoke the ifx_allow_newline() user-defined routine to allow newlines in quoted strings:

EXEC SQL execute procedure ifx_allow_newline(`t');

To disallow newline in quoted strings, change the argument to f as in the following example:

EXEC SQL execute procedure ifx_allow_newline(`f');

To allow or disallow a newline character in a quoted string for all sessions, set the ALLOW_NEWLINE parameter in the ONCONFIG file. A value of 1 allows the newline character. A value of 0 (zero) disallows the newline character. For more information on the ALLOW_NEWLINE parameter, see your Administrator's Guide.

To add comments to an ESQL/C program, you can use either of the following formats:

• You can use a double dash (--) comment indicator on any ESQL/C statement. The statement must begin with either EXEC SQL or $ and terminate with a semicolon. The comment continues to the end of the line.

For example, the comment on the first of the following lines notes that the ESQL/C statement opens the stores7 demonstration database:

EXEC SQL database stores7; -- stores7 database is open now!
printf("\nDatabase opened\n"); /* This is not an ESQL/C */
/* line so it needs a */
/* regular C notation */
/* for a comment */

• You can use a standard C comment on an ESQL/C line, as the following example shows:
EXEC SQL begin work; /* You can also use a C comment here */

Host variables are ESQL/C or C variables that you use in embedded SQL statements to transfer data between database columns and the ESQL/C program. When you use a host variable in an SQL statement, you must precede its name with a symbol to distinguish it as a host variable. You can use either of the following symbols:

• A colon (:)

For example, to specify the host variable that is called hostvar as a connection name, use the following syntax:

EXEC SQL connect to :hostvar;

• A dollar sign ($)

For example, to specify the host variable that is called hostvar as a connection name, use the following syntax:

EXEC SQL connect to $hostvar;

Important: Using the colon (:) as a host-variable prefix conforms to ANSI SQL standards.

When you list more than one host variable within an SQL statement, separate the host variables with commas (,). For example, the esql command interprets the following line as two host variables, host1 and host2:

EXEC SQL select fname, lname into :host1, :host2 from customer;

If you omit the comma, esql interprets the second variable as an indicator variable for the first host variable. The esql command interprets the following line as one host variable, host1, and one indicator variable, host2, for the host1 host variable:

EXEC SQL select fname, lname into :host1 :host2 from customer;

For more information on the syntax of indicator variables, see Using Indicator Variables.

Outside an SQL statement, treat a host variable as you would a regular C variable. For more information on how to declare and use host variables, see Declaring and Using Host Variables.