The demo3.ec sample program

The demo4 sample program (The demo4.ec sample program) assumes these same conditions. While demo4 uses a system-descriptor area to define the select-list columns, demo3 uses an sqlda structure. The demo3 program does not perform exception handling.

======================================================================
1. #include <stdio.h>
2. EXEC SQL include sqlda;
3. EXEC SQL include sqltypes;
4. main()
5. {
6. struct sqlda *demo3_ptr;
7. struct sqlvar_struct *col_ptr;
8. static char data_buff[1024];
9. int pos, cnt, size;
10. EXEC SQL BEGIN DECLARE SECTION;
11.    int2 i, desc_count;
12.    char demoquery[80];
13. EXEC SQL END DECLARE SECTION;
14.    printf("DEMO3 Sample ESQL program running.\n\n");
15.    EXEC SQL connect to 'stores7';
=======================================================================

Line 2

The program must include the sqlda.h header file to provide a definition for the sqlda structure.

Lines 6 - 13

Lines 6 and 7 declare sqlda variables that are needed by the program. The demo3_ptr variable points to the sqlda structure that will hold the data that is fetched from the database. The col_ptr variable points to an sqlvar_struct structure so that the code can step through each of the sqlvar_struct structures in the variable-length portion of sqlda. Neither of these variables is declared as the host variable. Lines 10 - 13 declare host variables to hold the data that is obtained from the user and the data that is retrieved from the sqlda structure.

=======================================================================
16.    /* These next four lines have hard-wired both the query and
17.     * the value for the parameter. This information could have
18.     * been entered from the terminal and placed into the strings
19.     * demoquery and a query value string (queryvalue), respectively.
20.     */
21.    sprintf(demoquery, "%s %s",
22.       "select fname, lname from customer",
23.       "where lname < 'C' ");
24.    EXEC SQL prepare demo3id from :demoquery;
25.    EXEC SQL declare demo3cursor cursor for demo3id;
26.    EXEC SQL describe demo3id into demo3_ptr;
=======================================================================

Lines 16 - 24

These lines assemble the character string for the SELECT statement (in demoquery) and prepare it as the demo3id statement identifier. For more information about these steps, see Assemble and prepare the SQL statement.

Line 25

This line declares the demo3cursor for the prepared statement identifier, demo3id.

Line 26

The DESCRIBE statement describes the select-list columns for the prepared statement that is in the demo3id statement identifier. For this reason, you must prepare the statement before you use DESCRIBE. This DESCRIBE includes the INTO clause to specify the sqlda structure to which demo3_ptr points as the location for these column descriptions. The DESCRIBE...INTO statement also allocates memory for an sqlda structure and stores the address of this structure in the demo3_ptr variable.

SELECT fname, lname FROM customer WHERE lname < 'C'

=======================================================================
27.    desc_count = demo3_ptr->sqld;
28.    printf("There are %d returned columns:\n", desc_count);
29.    /* Print out what DESCRIBE returns */
30.    for (i = 1; i <= desc_count; i++)
31.       prsqlda(i, demo3_ptr->sqlvar[i-1]);
32.    printf("\n\n");
=======================================================================

Lines 27 and 28

Line 27 assigns the number of select-list columns that are found by the DESCRIBE statement to the desc_count host variable. Line 28 displays this information to the user.

Lines 29 - 32

This for loop goes through the sqlvar_struct structures for the columns of the select list. It uses the desc_count host variable to determine the number of these structures that are initialized by the DESCRIBE statement. For each sqlvar_struct structure, the prsqlda() function (line 31) displays information such as the data type, length, and name. For a description of prsqlda(), see the description of lines 75 - 81.

=======================================================================
33.    for(col_ptr=demo3_ptr->sqlvar, cnt=pos=0; cnt < desc_count;
34.       cnt++, col_ptr++)
35.       {
36.       /* Allow for the trailing null character in C
37.           character arrays */
38.       if(col_ptr->sqltype==SQLCHAR)
39.          col_ptr->sqllen += 1;
40.       /* Get next word boundary for column data and
41.           assign buffer position to sqldata */
42.       pos = (int)rtypalign(pos, col_ptr->sqltype);
43.       col_ptr->sqldata = &data_buff[pos];
44.       /* Determine size used by column data and increment
45.           buffer position */
46.       size = rtypmsize(col_ptr->sqltype, col_ptr->sqllen);
47.       pos += size;
48.       }
=======================================================================

Lines 33 - 48

This second for loop allocates memory for the sqldata fields and sets the sqldata fields to point to this memory.

Lines 40 - 47 examine the sqltype and sqllen fields of sqlda for each select-list column to determine the amount of memory you need to allocate for the data. The program does not use malloc() to allocate space dynamically. Instead, it uses a static data buffer (the data_buff variable defined on line 8) to hold the column data. The rtypalign() function (line 42) returns the position of the next word boundary for the column data type (in col_ptr->sqltype). Line 43 then assigns the address of this position within the data_buff data buffer to the sqldata field (for columns that receive values returned by the query).

The rtypmsize() function (line 46) returns the number of bytes required for the SQL data type that is specified by the sqltype and sqllen fields. Line 47 then increments the data buffer pointer (pos) by the size required for the data. For more information, see Allocate memory for column data.

=======================================================================
49.    EXEC SQL open demo3cursor;
50.    for (;;)
51.       {
52.       EXEC SQL fetch demo3cursor using descriptor demo3_ptr;
53.       if (strncmp(SQLSTATE, "00", 2) != 0)
54.          break;
55.       /* Print out the returned values */
56.       for (i=0; i<desc_count; i++)
57.          printf("Column: %s\tValue:%s\n", demo3_ptr-
             >sqlvar[i].sqlname,
58.             demo3_ptr->sqlvar[i].sqldata);
59.       printf("\n");
60.       }
=======================================================================

Line 49

The database server executes the SELECT statement when it opens the demo3cursor cursor. If the WHERE clause of your SELECT statement contains input parameters, you also need to specify the USING DESCRIPTOR clause of OPEN (see Handling an unknown column list).

Lines 50 - 60

This inner for loop executes for each row that is fetched from the database. The FETCH statement (line 52) includes the USING DESCRIPTOR clause to specify the sqlda structure to which demo3_ptr points as the location of the column values. After this FETCH, the column values are stored in the specified sqlda structure.

=======================================================================
61.    if (strncmp(SQLSTATE, "02", 2) != 0)
62.       printf("SQLSTATE after fetch is %s\n", SQLSTATE);
63.    EXEC SQL close demo3cursor;
=======================================================================

Lines 61 and 62

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

Line 63

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

=======================================================================
64.    EXEC SQL free demo3id;
65.    EXEC SQL free demo3cursor;
66.    /* No need to explicitly free data buffer in this case because
67.     * it wasn't allocated with malloc(). Instead, it is a static char
68.     * buffer
69.     */
70.    /* Free memory assigned to sqlda pointer. */
71.    free(demo3_ptr);
72.    EXEC SQL disconnect current;
73.    printf("\nDEMO3 Sample Program Over.\n\n");
74. }
75. prsqlda(index, sp)
76. int2 index;
77. register struct sqlvar_struct *sp;
78. {
79.    printf("    Column %d: type = %d, len = %d, data = %s\n",
80.      index, sp->sqltype,  sp->sqllen, sp->sqldata, sp->sqlname);
81. }
=======================================================================

Lines 64 and 65

These FREE statements release the resources that are allocated for the demo3id prepared statement and the demo3cursor database cursor.

Lines 66 - 71

At the end of the program, free the memory allocated to the sqlda structure. Because this program does not use malloc() to allocate the data buffer, it does not use the free() system call to free the sqldata pointers. Although the allocation of memory from a static buffer is straightforward, it has the disadvantage that this buffer remains allocated until the program ends. For more information, see Free memory allocated to an sqlda structure.

The free() system call (line 71) frees the sqlda structure to which demo3_ptr points.

Lines 75 - 81