Guide to the dyn_sql.ec file
=======================================================================
1. /*
2. This program prompts the user to enter a SELECT statement
3. for the stores7 database. It processes the statement using
dynamic sql
4. and system descriptor areas and displays the rows returned by the
5. database server.
6. */
7. #include <stdio.h>
8. #include <stdlib.h>
9. #include <ctype.h>
10. EXEC SQL include sqltypes;
11. EXEC SQL include locator;
12. EXEC SQL include datetime;
13. EXEC SQL include decimal;
14. #define WARNNOTIFY 1
15. #define NOWARNNOTIFY 0
16. #define LCASE(c) (isupper(c) ? tolower(c) : (c))
17. #define BUFFSZ 256
18. extern char statement[80];
=======================================================================Lines 7 - 13
These lines specify C and files to include in the program. The stdio.h file enables dyn_sql to use the standard C I/O library. The stdlib.h file contains string-to-number conversion functions, memory allocation functions, and other miscellaneous standard library functions. The ctypes.h file contains macros that check the attributes of a character. For example, one macro determines whether a character is uppercase or lowercase.
The sqltypes.h header file contains symbolic constants that correspond to the data types that are found in HCL OneDB™ databases. The program uses these constants to determine the data types of columns that the dynamic SELECT statement returns.
The locator.h file contains the definition of the locator structure (ifx_loc_t), which is the type of host variable needed for TEXT and BYTE columns. The datetime.h file contains definitions of the datetime and interval structures, which are the data types of host variables for DATETIME and INTERVAL columns. The decimal.h file contains the definition of the dec_t structure, which is the type of host variable needed for DECIMAL columns.
Lines 14 - 17
The exp_chk() exception-handling function uses the WARNNOTIFY and NOWARNNOTIFY constants (lines 14 and 15). The second argument of exp_chk() tells the function to display information in the SQLSTATE and SQLCODE variables for warnings (WARNNOTIFY) or not to display information for warnings (NOWARNNOTIFY). The exp_chk() function is in the exp_chk.ec source file. For a description, see Guide to the exp_chk.ec file.
Line 16 defines
LCASE, a macro that converts an uppercase character to a lowercase
character. Line 17 defines BUFFSZ to be the number 256.
The program uses BUFFSZ to specify the size of arrays that store input
from the user.
Line 18
Line 18 declares statement as an external global variable to hold the name of the last SQL statement that the program asked the database server to execute. The exception-handling functions use this information. (See lines 399 - 406.)
=======================================================================
19. EXEC SQL BEGIN DECLARE SECTION;
20. ifx_loc_t lcat_descr;
21. ifx_loc_t lcat_picture;
22. EXEC SQL END DECLARE SECTION;
23. mint whenexp_chk();
24. main(argc, argv)
25. mint argc;
26. char *argv[];
27. {
28. int4 ret, getrow();
29. short data_found = 0;
30. EXEC SQL BEGIN DECLARE SECTION;
31. char ans[BUFFSZ], db_name[30];
32. char name[40];
33. mint sel_cnt, i;
34. short type;
35. EXEC SQL END DECLARE SECTION;
36. printf("DYN_SQL Sample ESQL Program running.\n\n");
37. EXEC SQL whenever sqlerror call whenexp_chk;
38. if (argc > 2) /* correct no. of args? */
39. {
40. printf("\nUsage: %s [database]\nIncorrect no. of
argument(s)\n",
41. argv[0]);
42. printf("\nDYN_SQL Sample Program over.\n\n");
43. exit(1);
44. }
45. strcpy(db_name, "stores7");
46. if(argc == 2)
47. strcpy(db_name, argv[1]);
48. sprintf(statement,"CONNECT TO %s",db_name);
49. EXEC SQL connect to :db_name;
50. printf("Connected to %s\n", db_name);
51. ++argv;
=======================================================================Lines 19 - 23
Lines 19 - 23 define the global host variables that are used in SQL statements. Lines 20 and 21 define the locator structures that are the host variables for the cat_descr and cat_picture columns of the catalog table. Line 23 declares the whenexp_chk() function, which the program calls when an error occurs on an SQL statement.
Lines 24 - 27
The main() function is the point where the program begins to execute. The argc parameter gives the number of arguments from the command line when the program was invoked. The argv parameter is an array of pointers to command-line arguments. This program expects only one argument (the name of the database to be accessed), and it is optional.
Lines 28 - 51
Line 28 defines an int4 data type (ret) to receive a return value from the getrow() function. Line 28 also declares that the getrow function returns a int4 data type. Lines 30 - 35 define the host variables that are local to the main() program block. Line 37 executes the WHENEVER statement to transfer control to whenexp_chk() if any errors occur in SQL statements. For more information about the whenexp_chk() function, see Guide to the exp_chk.ec file.
Lines
38 - 51 establish a connection to a database. If argc equals 2,
the program assumes that the user entered a database name on the command
line (by convention the first argument is the name of the program),
and the program opens this database. If the user did not enter a database
name on the command line, the program opens the stores7 database
(see line 45), which is the default. In both cases, the program connects
to the default database server that is specified by the INFORMIXSERVER environment
variable because no database server is specified.
=======================================================================
52. while(1)
53. {
54. /* prompt for SELECT statement */
55. printf("\nEnter a SELECT statement for the %s database",
56. db_name);
57. printf("\n\t(e.g. select * from customer;)\n");
58. printf("\tOR a ';' to terminate program:\n>> ");
59. if(!getans(ans, BUFFSZ))
60. continue;
61. if (*ans == ';')
62. {
63. strcpy(statement, "DISCONNECT");
64. EXEC SQL disconnect current;
65. printf("\nDYN_SQL Sample Program over.\n\n");
66. exit(1);
67. }
68. /* prepare statement id */
69. printf("\nPreparing statement (%s)...\n", ans);
70. strcpy(statement, "PREPARE sel_id");
71. EXEC SQL prepare sel_id from :ans;
72. /* declare cursor */
73. printf("Declaring cursor 'sel_curs' for SELECT...\n");
74. strcpy(statement, "DECLARE sel_curs");
75. EXEC SQL declare sel_curs cursor for sel_id;
76. /* allocate descriptor area */
77. printf("Allocating system-descriptor area...\n");
78. strcpy(statement, "ALLOCATE DESCRIPTOR selcat");
79. EXEC SQL allocate descriptor 'selcat';
80. /* Ask the database server to describe the statement */
81. printf("Describing prepared SELECT...\n");
82. strcpy(statement,
83. "DESCRIBE sel_id USING SQL DESCRIPTOR selcat");
84. EXEC SQL describe sel_id using sql descriptor 'selcat';
85. if (SQLCODE != 0)
86. {
87. printf("** Statement is not a SELECT.\n");
88. free_stuff();
89. strcpy(statement, "DISCONNECT");
90. EXEC SQL disconnect current;
91. printf("\nDYN_SQL Sample Program over.\n\n");
92. exit(1);
93. }
=======================================================================Lines 52 - 67
The while(1) on line 52 begins a loop that continues to the end of the main() function. Lines 55 - 58 prompt the user to enter either a SELECT statement or, to terminate the program, a semicolon. The getans() function receives the input from the user. If the first character is not a semicolon, the program continues to process the input.
Lines 68 - 75
The PREPARE statement prepares the SELECT statement (which the user enters) from the array ans[] and assigns it the statement identifier sel_id. The PREPARE statement enables the database server to parse, validate, and generate an execution plan for the statement.
The DECLARE statement (lines 72 - 75) creates the sel_curs cursor for the set of rows that the SELECT statement returns, in case it returns more than one row.
Lines 76 - 79
The ALLOCATE DESCRIPTOR statement allocates the selcat system-descriptor area in memory. The statement does not include the WITH MAX clause and, therefore, uses the default memory allocation, which is for 100 columns.
Lines 80 - 93
The DESCRIBE statement obtains information from the database server about the statement that is in the sel_id statement identifier. The database server returns the information in the selcat system-descriptor area, which the preceding ALLOCATE DESCRIPTOR statement creates. The information that DESCRIBE puts into the system-descriptor area includes the number, names, data types, and lengths of the columns in the select list.
The
DESCRIBE statement also sets the SQLCODE variable to a number that
indicates the type of statement that was described. To check whether
the statement type is SELECT, line 85 compares the value of SQLCODE
to 0 (the value defined in the sqlstypes.h file
for a SELECT statement with no INTO TEMP clause). If the statement
is not a SELECT, line 87 displays a message to that effect and the
program frees the cursor and the resources that have been allocated.
Then it closes the connection and exits.
=======================================================================
94. /* Determine the number of columns in the select list */
95. printf("Getting number of described values from ");
96. printf("system-descriptor area...\n");
97. strcpy(statement, "GET DESCRIPTOR selcat: COUNT field");
98. EXEC SQL get descriptor 'selcat' :sel_cnt = COUNT;
99. /* open cursor; process select statement */
100. printf("Opening cursor 'sel_curs'...\n");
101. strcpy(statement, "OPEN sel_curs");
102. EXEC SQL open sel_curs;
103. /*
104. * The following loop checks whether the cat_picture or
105. * cat_descr columns are described in the system-descriptor area.
106. * If so, it initializes a locator structure to read the simple
107. * large-object data into memory and sets the address of the
108. * locator structure in the system-descriptor area.
109. */
110. for(i = 1; i <= sel_cnt; i++)
111. {
112. strcpy(statement,
113. "GET DESCRIPTOR selcat: TYPE, NAME fields");
114. EXEC SQL get descriptor 'selcat' VALUE :i
115. :type = TYPE,
116. :name = NAME;
117. if (type == SQLTEXT && !strncmp(name, "cat_descr",
118. strlen("cat_descr")))
119. {
120. lcat_descr.loc_loctype = LOCMEMORY;
121. lcat_descr.loc_bufsize = -1;
122. lcat_descr.loc_oflags = 0;
123. strcpy(statement, "SET DESCRIPTOR selcat: DATA field");
124. EXEC SQL set descriptor 'selcat' VALUE :i
125. DATA = :lcat_descr;
126. }
127. if (type == SQLBYTES && !strncmp(name, "cat_picture",
128. strlen("cat_picture")))
129. {
130. lcat_picture.loc_loctype = LOCMEMORY;
131. lcat_picture.loc_bufsize = -1;
132. lcat_picture.loc_oflags = 0;
133. strcpy(statement, "SET DESCRIPTOR selcat: DATA field");
134. EXEC SQL set descriptor 'selcat' VALUE :i
135. DATA = :lcat_picture;
136. }
137. }
=======================================================================Lines 94 - 98
The GET DESCRIPTOR statement retrieves the COUNT value from the selcat system-descriptor area. The COUNT value indicates how many columns are described in the system-descriptor area.
Lines 99 - 102
The OPEN statement begins execution of the dynamic SELECT statement and activates the sel_curs cursor for the set of rows that it returns.
Lines 114 - 137
This section of the code uses the GET DESCRIPTOR statement to determine whether the simple large-object columns from the catalog table (cat_descr and cat_picture) are included in the select list. If you dynamically select a simple large-object column, you must set the address of a locator structure into the DATA field of the item descriptor to tell the database server where to return the locator structure.
- The data is returned in a memory buffer (loc_loctype = LOCMEMORY).
- The database server allocates the memory buffer (loc_bufsize =
-1).
Then the program uses the SET DESCRIPTOR statement to load the address of the locator structure into the DATA field of the descriptor area.
For more information about how to work with the TEXT and BYTE data types, see Simple large objects.
=======================================================================
138. while(ret = getrow("selcat")) /* fetch a row */
139. {
140. data_found = 1;
141. if (ret < 0)
142. {
143. strcpy(statement, "DISCONNECT");
144. EXEC SQL disconnect current;
145. printf("\nDYN_SQL Sample Program over.\n\n");
146. exit(1);
147. }
148. disp_data(sel_cnt, "selcat"); /* display the data */
149. }
150. if (!data_found)
151. printf("** No matching rows found.\n");
152. free_stuff();
153. if (!more_to_do()) /* More to do? */
154. break; /* no, terminate loop */
155. }
156. }
157. /* fetch the next row for selected items */
158. int4 getrow(sysdesc)
159. EXEC SQL BEGIN DECLARE SECTION;
160. PARAMETER char *sysdesc;
161. EXEC SQL END DECLARE SECTION;
162. {
163. int4 exp_chk();
164. sprintf(statement, "FETCH %s", sysdesc);
165. EXEC SQL fetch sel_curs using sql descriptor :sysdesc;
166. return((exp_chk(statement)) == 100 ? 0 : 1);
167. }
=======================================================================Lines 138 - 149
The getrow() function
retrieves the selected rows one by one. Each iteration of the while loop
retrieves one row, which the program then processes with the disp_data() function
(line 148). When all the rows are retrieved, getrow() returns
a 0 (zero) and the while loop terminates.
For more information about the getrow() function,
see Lines 157 - 167.
Line 152
The free_stuff() function frees resources that were allocated when the dynamic SELECT statement was processed. See Lines 381 - 387.
Lines 153 - 156
When all the selected rows
are processed, the program calls the more_to_do() function,
which asks whether the user would like to process more SELECT statements.
If the answer is no, more_to_do() returns 0 and
the break statement terminates the while loop that began
on line 52. If the answer is yes, the program begins the next iteration
of the while statement on line 52 to accept and process another
SELECT statement.
Lines 157 - 167
The getrow() function
moves the cursor to and then fetches the next row in the set of rows
that are returned by the dynamic SELECT statement. It fetches the
row values into the system-descriptor area that is specified in the sysdesc variable.
If there are no more rows to fetch (exp_chk() returns 100), getrow() returns 0.
If the FETCH encounters a runtime error, getrow() returns 1.
=======================================================================
168. {/*
169. * This function loads a column into a host variable of the correct
170. * type and displays the name of the column and the value, unless
* the
171. * value is NULL.
172. disp_data(col_cnt, sysdesc)
173. */
174. mint col_cnt;
175. EXEC SQL BEGIN DECLARE SECTION;
176. PARAMETER char *sysdesc;
177. EXEC SQL END DECLARE SECTION;
178. EXEC SQL BEGIN DECLARE SECTION;
179. mint int_data, i;
180. char *char_data;
181. int4 date_data;
182. datetime dt_data;
183. interval intvl_data;
184. decimal dec_data;
185. short short_data;
186. char name[40];
187. short char_len, type, ind;
188. EXEC SQL END DECLARE SECTION;
189. int4 size;
190. unsigned amount;
191. mint x;
192. char shdesc[81], str[40], *p;
193. printf("\n\n");
194. /* For each column described in the system descriptor area,
195. * determine its data type. Then retrieve the column name and its
196. * value, storing the value in a host variable defined for the
197. * particular data type. If the column is not NULL, display the
198. * name and value.
199. */
200. for(i = 1; i <= col_cnt; i++)
201. {
202. strcpy(statement, "GET DESCRIPTOR: TYPE field");
203. EXEC SQL get descriptor :sysdesc VALUE :i
204. :type = TYPE;
205. switch(type)
206. {
207. case SQLSERIAL:
208. case SQLINT:
209. strcpy(statement,
210. "GET DESCRIPTOR: NAME, INDICATOR, DATA fields");
211. EXEC SQL get descriptor :sysdesc VALUE :i
212. :name = NAME,
213. ind = INDICATOR,
214. :int_data = DATA;
215. if(ind == -1)
216. printf("\n%.20s: NULL", name);
217. else
218. printf("\n%.20s: %d", name, int_data);
219. break;
220. case SQLSMINT:
221. strcpy(statement,
222. "GET DESCRIPTOR: NAME, INDICATOR, DATA fields");
223. EXEC SQL get descriptor :sysdesc VALUE :i
224. :name = NAME,
225. :ind = INDICATOR,
226. :short_data = DATA;
227. if(ind == -1)
228. printf("\n%.20s: NULL", name);
229. else
230. printf("\n%.20s: %d", name, short_data);
231. break;
232. case SQLDECIMAL:
233. case SQLMONEY:
234. strcpy(statement,
235. "GET DESCRIPTOR: NAME, INDICATOR, DATA fields");
236. EXEC SQL get descriptor :sysdesc VALUE :i
237. :name = NAME,
238. :ind = INDICATOR,
239. :dec_data = DATA;
240. if(ind == -1)
241. printf("\n%.20s: NULL", name);
242. else
243. {
244. if(type == SQLDECIMAL)
245. rfmtdec(&dec_data, "###,###,###.##", str);
246. else
247. rfmtdec(&dec_data, "$$$,$$$,$$$.$$", str);
248. printf("\n%.20s: %s", name, str);
249. }
250. break;
251. case SQLDATE:
252. strcpy(statement,
253. "GET DESCRIPTOR: NAME, INDICATOR, DATA fields");
254. EXEC SQL get descriptor :sysdesc VALUE :i
255. :name = NAME,
256. :ind = INDICATOR,
257. :date_data = DATA;
258. if(ind == -1)
259. printf("\n%.20s: NULL", name);
260. else
261. {
262. if((x = rfmtdate(date_data, "mmm. dd, yyyy",
263. str)) < 0)
264. printf("\ndisp_data() - DATE - fmt error");
265. else
266. printf("\n%.20s: %s", name, str);
267. }
268. break;
269. case SQLDTIME:
270. strcpy(statement,
271. "GET DESCRIPTOR: NAME, INDICATOR, DATA fields");
272. EXEC SQL get descriptor :sysdesc VALUE :i
273. :name = NAME,
274. :ind = INDICATOR,
275. :dt_data = DATA;
276. if(ind == -1)
277. printf("\n%.20s: NULL", name);
278. else
279. {
280. x = dttofmtasc(&dt_data, str, sizeof(str), 0);
281. printf("\n%.20s: %s", name, str);
282. }
283. break;
284. case SQLINTERVAL:
285. strcpy(statement,
286. "GET DESCRIPTOR: NAME, INDICATOR, DATA fields");
287. EXEC SQL get descriptor :sysdesc VALUE :i
288. :name = NAME,
289. :ind = INDICATOR,
290. :intvl_data = DATA;
291. if(ind == -1)
292. printf("\n%.20s: NULL", name);
293. else
294. {
295. if((x = intofmtasc(&intvl_data, str,
296. sizeof(str),
297. "%3d days, %2H hours, %2M minutes"))
298. < 0)
299. printf("\nINTRVL - fmt error %d", x);
300. else
301. printf("\n%.20s: %s", name, str);
302. }
303. break;
304. case SQLVCHAR:
305. case SQLCHAR:
306. strcpy(statement,
307. "GET DESCRIPTOR: LENGTH, NAME fields");
308. EXEC SQL get descriptor :sysdesc VALUE :i
309. :char_len = LENGTH,
310. :name = NAME;
311. amount = char_len;
312. if(char_data = (char *)(malloc(amount + 1)))
313. {
314. strcpy(statement,
315. "GET DESCRIPTOR: NAME, INDICATOR, DATA fields");
316. EXEC SQL get descriptor :sysdesc VALUE :i
317. :char_data = DATA,
318. :ind = INDICATOR;
319. if(ind == -1)
320. printf("\n%.20s: NULL", name);
321. else
322. printf("\n%.20s: %s", name, char_data);
323. }
324. else
325. {
326. printf("\n%.20s: ", name);
327. printf("Can't display: out of memory");
328. }
329. break;
330. case SQLTEXT:
331. strcpy (statement,
332. "GET DESCRIPTOR: NAME, INDICATOR, DATA fields");
333. EXEC SQL get descriptor :sysdesc VALUE :i
334. :name = NAME,
335. :ind = INDICATOR,
336. :lcat_descr = DATA;
337. size = lcat_descr.loc_size; /* get size of data */
338. printf("\n%.20s: ", name);
339. if(ind == -1)
340. {
341. printf("NULL");
342. break;
343. }
344. p = lcat_descr.loc_buffer; /* set p to buf addr */
345. /* print buffer 80 characters at a time */
346. while(size >= 80)
347. {
348. /* mv from buffer to shdesc */
349. ldchar(p, 80, shdesc);
350. printf("\n%80s", shdesc); /* display it */
351. size -= 80; /* decrement length */
352. p += 80; /* bump p by 80 */
353. }
354. strncpy(shdesc, p, size);
355. shdesc[size] = '\0';
356. printf("%-s\n", shdesc); /* dsply last segment */
357. break;
358. case SQLBYTES:
359. strcpy (statement,
360. "GET DESCRIPTOR: NAME, INDICATOR fields");
361. EXEC SQL get descriptor :sysdesc VALUE :i
362. :name = NAME,
363. :ind = INDICATOR;
364. if(ind == -1)
365. printf("%.20s: NULL", name);
366. else
367. {
368. printf("%.20s: ", name);
369. printf("Can't display BYTE type value");
370. }
371. break;
372. default:
373. printf("\nUnexpected data type: %d", type);
374. EXEC SQL disconnect current;
375. printf("\nDYN_SQL Sample Program over.\n\n");
376. exit(1);
377. }
378. }
379. printf("\n");
380.}
=======================================================================Lines 168 - 380
The disp_data() function displays the values that are stored in each row that the SELECT statement returns. The function must be able to receive and process any data type within the scope of the dynamic SELECT statement (in this case, any column within the stores7 database). This function accepts two arguments: col_cnt contains the number of columns that are contained in the system-descriptor area, and sysdesc contains the name of the system-descriptor area that contains the column information. This second argument must be declared with the PARAMETER keyword because the argument is used in the FETCH statement.
The disp_data() function first defines host variables for each of the data types that are found in the stores7 database (lines 178 - 188), except for the locator structures that have been globally defined already for the cat_descr and cat_picture columns of the catalog table (lines 19 - 22).
For each column that is described in the system-descriptor area, disp_data() retrieves its data type with a GET DESCRIPTOR statement. Next, disp_data()executes a switch on that data type and, for each type (column), it executes another GET DESCRIPTOR statement to retrieve the name of the column, the indicator flag, and the data. Unless the column is null, disp_data() moves the column data from the DATA field of the system-descriptor area to a corresponding host variable. Then it displays the column name and the content of the host variable.
The disp_data() function uses the symbolic constants defined in sqltypes.h to compare data types. It also uses the library functions rfmtdec(), rfmtdate(), dttofmtasc(), and intofmtosc() to format the DECIMAL and MONEY, DATE, DATETIME, and INTERVAL data types for display.
- The GET DESCRIPTOR statement (lines 333 and 361) retrieves the locator structure from the system-descriptor area and moves it to the ifx_loc_t host variable.
- The disp_data() function obtains the address of the data buffer from the locator structure, in loc_buffer, and retrieves the data from there.
Regarding the BYTE data type, for the sake of brevity disp_data() retrieves the locator structure but does not display the data. For an example of the type of logic required to display a BYTE column, see Guide to the dispcat_pic.ec File.
=======================================================================
381. free_stuff()
382. {
383. EXEC SQL free sel_id; /* free resources for statement */
384. EXEC SQL free sel_curs; /* free resources for cursor */
385. /* free system descriptor area */
386. EXEC SQL deallocate descriptor 'selcat';
387. }
388. /*
389. * The inpfuncs.c file contains the following functions used in
* this
390. * program:
391. * more_to_do() - asks the user to enter 'y' or 'n' to indicate
392. * whether to run the main program loop again.
393. *
394. * getans(ans, len) - accepts user input, up to 'len' number of
395. * characters and puts it in 'ans'
396. */
397. #include "inpfuncs.c"
398. /*
399 * The exp_chk.ec file contains the exception handling functions to
400. * check the SQLSTATE status variable to see if an error has
* occurred
401. * following an SQL statement. If a warning or an error has
402. * occurred, exp_chk() executes the GET DIAGNOSTICS statement and
403. * displays the detail for each exception that is returned.
404. */
405. EXEC SQL include exp_chk.ec;
=======================================================================Lines 381 - 387
The free_stuff() function frees resources that were allocated to process the dynamic statement. Line 383 frees resources that were allocated by the application when it prepared the dynamic SELECT statement. Line 384 releases resources allocated by the database server to process the sel_curs cursor. The DEALLOCATE DESCRIPTOR statement releases the memory allocated for the selcat system-descriptor area and its associated data areas.
Lines 388 - 397
Several of the demonstration programs also call the more_to_do() and getans() functions. Therefore, these functions are also broken out into a separate C source file and included in the appropriate demonstration program. Neither of these functions contain , so the program can use the C #include preprocessor statement to include the file. For a description of these functions, see ids_esqlc_0234.html#ids_esqlc_0234.