The demo4.ec sample program
This demo4 program is a version of the demo3 sample program (The demo3.ec sample program) that uses a system-descriptor area to hold select-list columns. The demo4 program does not include exception handling.
=======================================================================
1. #include <stdio.h>
2. EXEC SQL define NAME_LEN 15;
3. main()
4. {
5. EXEC SQL BEGIN DECLARE SECTION;
6. mint i;
7. mint desc_count;
8. char demoquery[80];
9. char colname[19];
10. char result[ NAME_LEN + 1 ];
11. EXEC SQL END DECLARE SECTION;
=======================================================================Lines 5 - 11
These lines declare host variables to hold the data that is obtained from the user and the column values that are retrieved from the system-descriptor area.
=======================================================================
12. printf("DEMO4 Sample ESQL program running.\n\n");
13. EXEC SQL connect to 'stores7';
14. /* These next three lines have hard-wired both the query and
15. * the value for the parameter. This information could have been
16. * been entered from the terminal and placed into the strings
17. * demoquery and the query value string (queryvalue),
* respectively.
18. */
19. sprintf(demoquery, "%s %s",
20. "select fname, lname from customer",
21. "where lname < 'C' ");
22. EXEC SQL prepare demo4id from :demoquery;
23. EXEC SQL declare demo4cursor cursor for demo4id;
24. EXEC SQL allocate descriptor 'demo4desc' with max 4;
25. EXEC SQL open demo4cursor;
=======================================================================Lines 14 - 22
These lines assemble the character string for the statement (in demoquery) and prepare it as the demo4id statement identifier. For more information about these steps, see Assemble and prepare the SQL statement.
Line 23
This line declares the demo4cursor cursor for the prepared statement identifier, demo4id. All non-singleton SELECT statements must have a declared cursor.
Line 24
To be able to use a system-descriptor area for the select-list columns, you must first allocate it. This ALLOCATE DESCRIPTOR statement allocates the demo4desc system-descriptor area with four item descriptors.
Line 25
The database server executes the SELECT statement when it opens the demo4cursor cursor. If the WHERE clause of your SELECT statement contains input parameters, you also need to specify the USING SQL DESCRIPTOR clause of the OPEN statement. (See Handling a parameterized SELECT statement.)
=======================================================================
26. EXEC SQL describe demo4id using sql descriptor 'demo4desc';
27. EXEC SQL get descriptor 'demo4desc' :desc_count = COUNT;
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. prsysdesc(i);
32. printf("\n\n");
=======================================================================Line 26
The DESCRIBE statement describes the select-list columns for the prepared statement in the demo4id statement identifier. For this reason, the DESCRIBE must follow the PREPARE. This DESCRIBE includes the USING SQL DESCRIPTOR clause to specify the demo4desc system-descriptor area as the location for these column descriptions.
Lines 27 and 28
Line 27 uses the GET DESCRIPTOR statement to obtain the number of select-list columns found by the DESCRIBE. This number is read from the COUNT field of the demo4desc system-descriptor area and saved in the desc_count host variable. Line 28 displays this information to the user.
Lines 29 - 31
This for loop goes through the item descriptors for the columns of the select list. It uses the desc_count host variable to determine the number of item descriptors initialized by the DESCRIBE statement. For each item descriptor, the for loop calls the prsysdesc() function (line 31) to save information such as the data type, length, and name of the column in host variables. See Lines 58 - 76 for a description of prsysdesc().
=======================================================================
33. for (;;)
34. {
35. EXEC SQL fetch demo4cursor using sql descriptor 'demo4desc';
36. if (strncmp(SQLSTATE, "00", 2) != 0)
37. break;
38. /* Print out the returned values */
39. for (i = 1; i <= desc_count; i++)
40. {
41. EXEC SQL get descriptor 'demo4desc' VALUE :i
42. :colname=NAME, :result = DATA;
43. printf("Column: %s\tValue:%s\n ", colname, result);
44. }
45. printf("\n");
46. }
=======================================================================Lines 33 - 46
This inner for loop executes for each row fetched from the database. The FETCH statement (line 35) includes the USING SQL DESCRIPTOR clause to specify the demo4desc system-descriptor area as the location of the column values. After this FETCH executes, the column values are stored in the specified system-descriptor area.
The if statement
(lines 36 and 37) tests the value of the SQLSTATE variable to determine
if the FETCH was successful. If SQLSTATE contains a class code other
than "00", then the FETCH generates a warning ("01"),
the NOT FOUND condition ("02"), or an error (> "02").
In any of these cases, line 37 ends the for loop.
=======================================================================
47. if(strncmp(SQLSTATE, "02", 2) != 0)
48. printf("SQLSTATE after fetch is %s\n", SQLSTATE);
49. EXEC SQL close demo4cursor;
50. /* free resources for prepared statement and cursor*/
51. EXEC SQL free demo4id;
52. EXEC SQL free demo4cursor;
53. /* free system-descriptor area */
54. EXEC SQL deallocate descriptor 'demo4desc';
55. EXEC SQL disconnect current;
56. printf("\nDEMO4 Sample Program Over.\n\n");
57. }
=======================================================================Lines 47 and 48
Outside the for loop,
the program tests the SQLSTATE variable again so that it can notify
the user in the event of successful execution, a runtime error, or
a warning (class code not equal to "02").
Line 49
After all the rows are fetched, the CLOSE statement closes the demo4cursor cursor.
Lines 50 - 54
These FREE statements release the resources that are allocated for the prepared statement (line 51) and the database cursor (line 52).
The DEALLOCATE DESCRIPTOR statement (line 54) releases the memory allocated to the demo4desc system-descriptor area. For more information, see Free memory allocated to a system-descriptor area.
=======================================================================
58. prsysdesc(index)
59. EXEC SQL BEGIN DECLARE SECTION;
60. PARAMETER mint index;
61. EXEC SQL END DECLARE SECTION;
62. {
63. EXEC SQL BEGIN DECLARE SECTION;
64. mint type;
65. mint len;
66. mint nullable;
67. char name[40];
68. EXEC SQL END DECLARE SECTION;
69. EXEC SQL get descriptor 'demo4desc' VALUE :index
70. :type = TYPE,
71. :len = LENGTH,
72. :nullable = NULLABLE,
73. :name = NAME;
74. printf(" Column %d: type = %d, len = %d, nullable=%d, name =
%s\n",
75. index, type, len, nullable, name);
76. }
=======================================================================Lines 58 - 76
The prsysdesc() function displays information about a select-list column. It uses the GET DESCRIPTOR statement to access one item descriptor from the demo4desc system-descriptor area.
The GET DESCRIPTOR statement (lines 70 - 74) accesses the TYPE, LENGTH, NULLABLE, and NAME fields from an item descriptor in demo4desc to provide information about a column. It stores this information in host variables of appropriate lengths and data types. The VALUE keyword indicates the number of the item descriptor to access.