The demo2.ec sample program

The demo2.ec program uses a host variable to hold the value of the input parameter for a SELECT statement. It also uses host variables to hold the column values that are returned from the database.

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 demoquery[80];
8.    char queryvalue[2];
9.    char fname[ FNAME_LEN + 1 ];
10.    char lname[ LNAME_LEN + 1 ];
11. EXEC SQL END DECLARE SECTION;
12.    printf("DEMO2 Sample ESQL program running.\n\n");
13.    EXEC SQL connect to'stores7';
14. /* The next three lines have hard-wired the query. This
15.  * information could have been entered from the terminal
16.  * and placed into the demoquery string
17.  */
18.    sprintf(demoquery, "%s %s",
19.          "select fname, lname from customer",
20.          "where lname > ? ");
21.    EXEC SQL prepare demo2id from :demoquery;

Lines 9 and 10

These lines declare a host variable (fname) for the parameter in the WHERE clause of the SELECT statement and declare host variables (fname and lname) for values that the SELECT statement returns.

Lines 14 - 21

These lines assemble the character string for the statement (in demoquery) and prepare it as the demo2id statement identifier. The question mark (?) indicates the input parameter in the WHERE clause. For more information about these steps, see Assemble and prepare the SQL statement.

22.    EXEC SQL declare demo2cursor cursor for demo2id;
23.    /* The next line has hard-wired the value for the parameter.
24.     * This information could also have been entered from the 
        * terminal
25.     * and placed into the queryvalue string.
26.     */
27.    sprintf(queryvalue, "C");
28.       EXEC SQL open demo2cursor using :queryvalue;
29.    for (;;)
30.       {
31.       EXEC SQL fetch demo2cursor into :fname, :lname;
32.       if (strncmp(SQLSTATE, "00", 2) != 0)
33.          break;
34.       /* Print out the returned values */
35.       printf("Column: fname\tValue: %s\n", fname);
36.       printf("Column: lname\tValue: %s\n", lname);
37.       printf("\n");
38.      }

Line 22

This line declares the demo2cursor cursor for the prepared statement identifier, demo2id. All non-singleton SELECT statements must have a declared cursor.

Lines 23 - 27

The queryvalue host variable is the input parameter for the SELECT statement. It contains the value C. In an interactive application, this value probably would be obtained from the user.

Line 28

The database server executes the SELECT statement when it opens the demo2cursor cursor. Because the WHERE clause of the SELECT statement contains input parameters (lines 20 and 21), the OPEN statement includes the USING clause to specify the input parameter value in queryvalue.

Lines 29 - 38

This for loop executes for each row fetched from the database. The FETCH statement (line 31) includes the INTO clause to specify the fname and lname host variables for the column values. After this FETCH statement executes, the column values are stored in these host variables.

39.    if (strncmp(SQLSTATE, "02", 2) != 0)
40.       printf("SQLSTATE after fetch is %s\n", SQLSTATE);
41.    EXEC SQL close demo2cursor;
42.    EXEC SQL free demo2cursor;
43.    EXEC SQL free demo2id;
44.    EXEC SQL disconnect current;
45.    printf("\nProgram Over.\n");
46. }

Lines 39 and 40

Outside the for loop, the program tests the SQLSTATE variable again so it can notify the user in the event of successful execution, a runtime error, or a warning (class code not equal to "02").

Line 41

After all the rows are fetched, the CLOSE statement closes the demo2cursor cursor.

Lines 42 and 43

These FREE statements release the resources allocated for the prepared statement (line 42) and the database cursor (line 43). Once a cursor or prepared statement has been freed, it cannot be used again in the program.