Guide to demo1.ec file

The sample 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 family names of customers whose family 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.

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

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 - 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 WHENEVER ERROR STOP;
12. EXEC SQL connect to ’stores7’;
13. EXEC SQL DECLARE democursor cursor for
14. select fname, lname
15. into :fname, :lname
16. from customer
17. where lname < ’C’;
18. EXEC SQL open democursor;

Lines 10 - 12

The printf() function shows 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.

Lines 13 - 17

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 family names of those customers whose family name (lname) begins with a letter less than 'C'.

Line 18

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

19. for (;;)
20. {
21. EXEC SQL fetch democursor;
22. if (strncmp(SQLSTATE, "00", 2) != 0)
23. break;
24. printf("%s %s\n",fname, lname);
25. }
26. if (strncmp(SQLSTATE, "02", 2) != 0)
27. printf("SQLSTATE after fetch is %s\n", SQLSTATE);
28. EXEC SQL close democursor;
29. EXEC SQL free democursor;

Lines 19 - 25

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 Opaque data types.

Lines 26 - 27

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

Lines 28 - 29

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

30. EXEC SQL disconnect current;
31. printf("\nDEMO1 Sample Program over.\n\n");
32. }

Lines 30 - 32

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