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

Embedding SQL Statements

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 12, "Working with the Database Server."

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

The EXEC SQL keywords: EXEC SQL SQL_statement;

EXEC SQL

ANSI

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

Handling Case Sensitivity in Embedded SQL Statements

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

Figure 1-2
Case Sensitivity in ESQL/C Files

ESQL/C Identifier Case Sensitive Example
Host Variable
Yes
ESQL/C treats the variables fname and Fname as distinct variables:
EXEC SQL BEGIN DECLARE SECTION;
char fname[16], lname[16];
char Fname[16];
EXEC SQL END DECLARE SECTION;
This sample does not generate a warning from the preprocessor.

SQL Keyword
No
Both CONNECT statements are valid ways of establishing a connection to the stores7 database:
EXEC SQL CONNECT TO 'stores7';
or
EXEC SQL connect to 'stores7';
In examples given in this manual, SQL keywords appear as lowercase characters.

Statement Identifiers
Cursor Names
No
The following example shows the creation of statement IDs and cursor names:
EXEC SQL prepare st from
'select * from tab1';
/* duplicate */
EXEC SQL prepare ST from
'insert into tab2
values (1,2)';
EXEC SQL declare curname cursor
for st;
/* duplicate */
EXEC SQL declare CURNAME cursor
for ST;
This code produces errors because the statement IDs st and ST are duplicates, as are the cursor names curname and CURNAME.

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 = ' "';

However, ESQL/C uses quotation marks differently than it uses nonembedded tools. Figure 1-3 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 = ' "';	Figure 1-3 A SELECT Statement with an Invalid WHERE Clause

For the WHERE clause in Figure 1-3, the ESQL/C preprocessor does not process a double quote; it just 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 resolve this misinterpretation, you can escape the double quote with the C escape character, the backslash (\). The correct syntax for the query in Figure 1-3 is as follows:

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. To search for the string "\" (where the double quotes are part of the string), enter five backslashes in the following query:

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

Figure 1-4 shows the string after it has passed through each of the processing steps.

Figure 1-4
Escaped Query String As It Is Processed

Processor After Processing
ESQL/C preprocessor
'\"\\\\\"'

C compiler
'"\\"'

ANSI-compliant database server
'"\"'

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 double-quoted string.

Adding Comments

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 line (one that begins with either EXEC SQL or $ and terminates with a semicolon). The comment continues to the end of the line.

DATABASE

stores7

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 shown in the following example: EXEC SQL begin work; /* You can also use a C comment here */

Specifying Host Variables

Host variables are normal C variables that you use in embedded SQL statements. When you use a host variable in an SQL statement, you must precede its name with a symbol to distinguish it from regular C variables. You can use either of the following symbols:

A colon (:)

hostvar

EXEC SQL connect to :hostvar;

A dollar sign ($)

hostvar

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 in SQL Statements".

Using ESQL/C Header Files

The Informix installation script stores the header files in the $INFORMIXDIR/incl/esql directory on a UNIX system and in the %informixdir%\incl\esql directory in a Windows environment.

Figure 1-5 shows the header files included with the ESQL/C product.

Figure 1-5
ESQL/C Header Files

(1 of 3)

Header File Contains Additional Information
datetime.h
Structure definition for DATETIME and INTERVAL data types.
Chapter 6

decimal.h
Structure definition for DECIMAL and MONEY data types.
Chapter 4

gls.h
Function prototypes and data structures for the gls functionality.
Informix Guide to GLS Functionality

locator.h
Structure definition for BYTE and TEXT data types.
Chapter 7

sqlca.h
Structure definition that ESQL/C uses to store error-status codes. This structure allows you to check for the success or failure of SQL statements. The esql preprocessor automatically includes this file when it preprocesses your program.
Chapter 10

sqlda.h
Structure definition for value pointers and descriptions of dynamically defined variables.
Chapter 12

sqlhdr.h
The definitions of cursor-related structures. This file includes the sqlda.h header file. The preprocessor automatically includes this file when it preprocesses your program.
"Including Header Files"

sqliapi.h
Function prototypes for ESQL/C library APIs.

sqlproto.h
The function prototypes of all ESQL/C library functions for use with source that is not fully ANSI C compliant.
"Declaring Function Prototypes"

sqlstype.h
The definitions of strings that correspond to SQL statements. ESQL/C uses these strings when your program contains the DESCRIBE statement.
Chapter 12

sqltypes.h
The integer constants that correspond to ESQL/C language and SQL data types. ESQL/C uses these constants when your program contains a DESCRIBE statement.
Chapter 12

sqlxtype.h
The integer constants that correspond to C language and SQL data types that ESQL/C uses when you are in X/Open mode. ESQL/C uses these constants when your program contains a DESCRIBE statement.
Chapter 12

value.h
The value structures that the Informix product NewEra uses.
NewEra documentation

varchar.h
The macros that you can use with the VARCHAR data type.
Chapter 2

xa.h
Function prototypes and data structures for TP/XA applications.
TP/XA Programmer's Manual

IUS

Figure 1-6 shows the ESQL/C header files specific to INFORMIX-Universal Server.

Figure 1-6
ESQL/C Header Files for INFORMIX-Universal Server

Header File Contents Additional Information
collct.h
Definitions of data structures for complex types in ESQL/C.
Chapter 9

ifxgls.h
Function prototypes for the GLS Application Programming Interface

int8.h
The definition of the structure that stores the INT8 data type.
Chapter 5

WIN NT/95

The sqlxtype.h header file is not available in the Windows environments.

Figure 1-7 shows the ESQL/C header files specific to the Windows environment.

Figure 1-7
ESQL/C Header Files for Windows Environments

Header File Contents Additional Information
ifxcexp.h
The infxcexp.c file contains the C code to export the addresses of all C runtime routines that the ESQL client-interface DLL uses.
"Using the Same Runtime Routines for Version Independence"

login.h
The definition of the InetLogin and
HostInfoStruct structures, which enable you to customize configuration information for the application. Because this file does not contain ESQL statements, you do not need to include it with the ESQL include directive. Use instead the C #include preprocessor directive.
Chapter 3

Declaring Function Prototypes

ESQL/C provides the sqlproto.h header file to declare function prototypes for all ESQL/C library functions. These function prototypes are required in an ESQL/C source file that you compile with an ANSI C compiler. By default, the esql command processor does not include function-prototype declarations. Having the processor include the ANSI-compliant function prototypes for the ESQL/C functions prevents an ANSI C compiler from generating warnings.

Warning: Although you can use an ANSI C compiler, the ESQL/C preprocessor does not fully support ANSI C, so you might not be able to preprocess all programs that follow the ANSI C standards.

Because the sqlproto.h file does not contain any ESQL/C statements, you can include this file in either of the following ways:

With the ESQL/C include preprocessor directive: EXEC SQL include sqlproto;
With the C #include preprocessor directive: #include "sqlproto.h";

Including Header Files

The ESQL/C preprocessor automatically includes the following ESQL/C header files in your program:

The sqlca.h file, which allows your program to check the success or failure of your ESQL/C statements with the SQLSTATE or SQLCODE variable

WIN NT/95
The sqlhdr.h file provides cursor-related structures for your ESQL/C program. This header file automatically includes the sqlda.h header file.

Warning: Although you can now use an ANSI C compiler, the ESQL/C preprocessor does not fully support ANSI C, so you might not be able to preprocess all programs that follow the ANSI C standards.

To include any of the other header files in your ESQL/C program, you must use the include preprocessor directive. However, you only need to include an ESQL/C header file if your program refers to the structures or the definitions that the header file defines. For example, if your program accesses datetime data, you must include the datetime.h header file, as follows:

EXEC SQL include datetime.h;

Make sure to terminate the line of code with a semicolon. Here are some additional examples:

EXEC SQL include varchar.h;

EXEC SQL include sqlda;

$include sqlstype;

Tip: You do not have to enter the .h file extension for an ESQL/C header file; the esql preprocessor assumes a .h extension.

For information on the include directive, see "The include Directive".

Informix-ESQL/C Programmer's ManualChapter 1: Programming with INFORMIX-ESQL/C Home Contents Index Master Index New Book