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

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 an on-line version of this and other demonstration programs in the $INFORMIXDIR/demo/esqlc directory. If you are using Windows, you can find the demonstration programs in the %INFORMIXDIR%\demo\esqlauth directory.

Compiling the demo1 Program

The following command compiles the demo1 program:

esql demo1.ec

On UNIX, the name of the executable program defaults to a.out.

In Windows environments, the name of the executable program defaults to demo.exe.

You can use the -o option to assign a different name to the executable program. For more information on the esql command, see Using the esql Command.

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 begins with 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. The program then prints the names to standard output.

#include <stdio.h>

EXEC SQL define FNAME_LEN 15;

EXEC SQL define LNAME_LEN 15;

main()

{

EXEC SQL BEGIN DECLARE SECTION;

char fname[ FNAME_LEN + 1 ];

char lname[ LNAME_LEN + 1 ];

EXEC SQL END DECLARE SECTION;

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.

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.

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.

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

EXEC SQL WHENEVER ERROR STOP;

EXEC SQL connect to 'stores7';

EXEC SQL DECLARE democursor cursor for

select fname, lname

into :fname, :lname

from customer

where lname < 'C';

EXEC SQL open democursor;

The printf() function displays text to identify the program and to notify the user when the program begins to execute. The WHENEVER statement implements a minimum of error handling, causing the program to display an error number and terminate if the database server returns an error after processing an SQL statement. The CONNECT statement initiates a connection to the default database server and opens the stores7 demonstration 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.

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'.

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

for (;;)

{

EXEC SQL fetch democursor;

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

break;

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

}

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

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

EXEC SQL close democursor;

EXEC SQL free democursor;

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, Working with Opaque Data Types of the Universal Data Option.

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.

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

EXEC SQL disconnect current;

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

}

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 32 marks the end of the main() function and of the program.