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

A Sample INFORMIX-ESQL/C Program

 The demo1.ec program illustrates most of the concepts that this chapter presents, such as include files, identifiers, host variables, and embedded SQL statements. It demonstrates how to use header files, declare and use host variables, and embed SQL statements.

Important: If you are using UNIX, you can find the on-line versions of this and other demonstration programs in the $INFORMIXDIR/demo/esqlc directory. This and other demonstration programs included throughout this book are not available for Windows environments.

Compiling the demo1 Program

The following command compiles the demo1 program:

The -o demo1 option causes the executable program to be named demo1. Without the -o option, the name of the executable program defaults to a.out. For more information on the esql command, see "Using the esql Command".

Guide to demo1.ec File

The sample ESQL/C program, demo1.ec, uses a static SELECT statement. This means that at compile time the program can obtain all of the information that it needs to run the SELECT statement.

The demo1.ec program reads from the customer table in the stores7 database the first and last names of customers whose last name is a value less than 'C'. Two host variables (:fname and :lname) hold the data from the customer table. A cursor manages the rows that the database server retrieves from the table. The database server fetches the rows one at a time. Your program then prints the names to standard output.

1 #include <stdio.h>


	2	 EXEC SQL define FNAME_LEN       15;


	3	 EXEC SQL define LNAME_LEN       15;



	4	 main()


	5	 {


	6	 EXEC SQL BEGIN DECLARE SECTION;


	7	 	char fname[ FNAME_LEN + 1 ];


	8	 	char lname[ LNAME_LEN + 1 ];


	9	 EXEC SQL END DECLARE SECTION;


 Line 1
The #include statement tells the C preprocessor to include the stdio.h system header file from the /usr/include directory. The stdio.h file enables demo1 to use the standard C language I/O library.

Lines 2 to 3

ESQL/C processes the define directives in stage 1 of preprocessing. The directives define the constants FNAME_LEN and LNAME_LEN, which the program uses later in host-variable declarations.

Lines 4 to 9

Line 4 begins the main() function, the entry point for the program. The EXEC SQL block declares host variables that are local to the main() function that receive data from the fname and lname columns of the customer table. The length of each array is 1 byte greater than the length of the character column from which it receives data. The extra byte stores the null terminator.

10 printf( "DEMO1 Sample ESQL Program running.\n\n");

11 EXEC SQL connect to 'stores7';


	12	 	EXEC SQL declare democursor cursor for


	13	 		select fname, lname


	14	 		into :fname, :lname


	15	 		from customer


	16	 		where lname < 'C';



	17	 	EXEC SQL open democursor;


 Lines 10 to 11
The printf() function displays text to identify the program and to notify the user when the program begins to execute. The CONNECT statement initiates a connection to the default database server and opens the stores7 database. You specify the default database server in the INFORMIXSERVER environment variable, which you must set before an application can connect to any database server.

Lines 12 to 16

The DECLARE statement creates a cursor that is called democursor to manage the rows that the database server reads from the customer table. The SELECT statement within the DECLARE statement determines the type of data that the database server reads from the table. This SELECT statement reads the first and last names of those customers whose last name (lname) begins with a letter less than 'C'.

Line 17

The OPEN statement opens the democursor cursor and begins execution of the SELECT statement.

18 for (;;)

19 {

20 EXEC SQL fetch democursor;

21 if (strncmp(SQLSTATE, "00", 2) != 0)

22 break;


	23	 		printf("%s  %s\n",fname, lname);


	24	 		}



	25	 	if (strncmp(SQLSTATE, "02", 2) != 0)


	26	 		printf("SQLSTATE after fetch is  %s\n", SQLSTATE);



	27	 	EXEC SQL close democursor;


	28	 	EXEC SQL free democursor;


 Lines 18 to 24
This section of code executes a FETCH statement inside a loop that repeats until SQLSTATE is not equal to "00". This condition indicates that either the end-of-data condition or a runtime error has occurred. In each iteration of the loop, the FETCH statement uses the cursor democursor to retrieve the next row that the SELECT statement returns and to put the selected data into the host variables fname and lname. The database server sets status variable SQLSTATE to "00" each time it fetches a row successfully. If the end-of-data condition occurs, the database server sets SQLSTATE to "02"; if an error occurs, it sets SQLSTATE to a value greater than "02". For more information about error handling and the SQLSTATE status variable, see Chapter 10 of this manual.

Lines 25 to 26

If the class code in SQLSTATE is any value except "02", then this printf() statement displays the SQLSTATE value for the user. This output is useful in the event of a runtime error.

Lines 27 to 28

The CLOSE and FREE statements free the resources that the database server had allocated for the cursor. The cursor is no longer usable.

29 EXEC SQL disconnect current;

30 printf("\nDEMO1 Sample Program over.\n\n");

31 }

Lines 29 to 31

The DISCONNECT CURRENT statement closes the database and terminates the current connection to a database server. The final printf() tells the user that the program is over. The right brace (}) on line 31 marks the end of the main() function and of the program.



Informix-ESQL/C Programmer's Manual, version 9.1
Copyright © 1998, Informix Software, Inc. All rights reserved.