Guide to the timeout.ec File
=======================================================================
1. /*
2. * timeout.ec *
3. */
4. #include <stdio.h>
5. #include <string.h>
6. #include <ctype.h>
7. #include <decimal.h>
8. #include <errno.h>
9. EXEC SQL include sqltypes;
10. #define LCASE(c) (isupper(c) ? tolower(c) : (c))
11. /* Defines for callback mechanism */
12. #define DB_TIMEOUT 200 /* number of milliseconds in timeout */
13. #define SQL_INTERRUPT -213 /* SQLCODE value for interrupted stmt
*/
14. /* These constants are used for the canceltst table, created by
15. * this program.
16. */
17. #define MAX_ROWS 10000 /* number of rows added to table */
18. EXEC SQL define CHARFLDSIZE 20; /* size of character columns in
* table */
19. /* Define for sqldone() return values */
20. #define SERVER_BUSY -439
21. /* These constants used by the exp_chk2() function to determine
22. * whether to display warnings.
23. */
24. #define WARNNOTIFY 1
25. #define NOWARNNOTIFY 0
26. int4 dspquery();
27. extern int4 exp_chk2();
28. void on_timeout();
29. main()
30. {
31. char ques[80], prompt_ans();
32. int4 ret;
33. mint create_tbl(), drop_tbl();
34. printf("TIMEOUT Sample ESQL Program running.\n\n");
35. /*
36. * Establish an explicit connection to the stores7 database
37. * on the default database server.
38. */
39. EXEC SQL connect to 'stores7';
=======================================================================Lines 4 - 9
Lines 4 - 8 include the UNIX™ header files from the /usr/include directory. The sqltypes.h header file (line 9) defines names for integer values that identify SQL and C data types.
Lines 10 - 20
Line 10 defines LCASE, a macro that converts an uppercase character to a lowercase character. The DB_TIMEOUT (line 12) constant defines the number of milliseconds in the timeout interval. The SQL_INTERRUPT constant (line 13) defines the SQLCODE value that the database server returns when it interrupts an SQL statement.
Lines 17 and 18 define constants that the create_tbl() function uses to create the canceltst table. This table holds the test data needed for the large query (lines 125 - 132). MAX_ROWS is the number of rows that create_tbl() inserts into canceltst. You can change this number if you find that the query does not run long enough for you to interrupt it. CHARFLDSIZE is the number of characters in the character fields (char_fld1 and char_fld2) of canceltst.
Line 20 defines the SERVER_BUSY constant to hold the sqldone() return value that indicates that the database server is busy processing an SQL request. Use of this constant makes code more readable and removes the explicit return value from the code.
Lines 24 and 25
The exp_chk2() exception-handling function uses the WARNNOTIFY and NOWARNNOTIFY constants (lines 24 and 25). Calls to exp_chk2() specify one of these as the second argument to indicate whether the function displays SQLSTATE and SQLCODE information for warnings (WARNNOTIFY) or does not display this information for warnings (NOWARNNOTIFY). For more information about the exp_chk2() function, see Lines 348 - 355.
Lines 29 - 33
The main() program block begins on line 29. Lines 31 - 33 declare variables local to the main() program block.
=======================================================================
40. if (exp_chk2("CONNECT to stores7", NOWARNNOTIFY) < 0)
41. exit(1);
42. printf("Connected to 'stores7' on default server\n");
43. /*
44. * Create the canceltst table to hold MAX_ROWS (10,000) rows.
45. */
46. if (!create_tbl())
47. {
48. printf("\nTIMEOUT Sample Program over.\n\n");
49. exit(1);
50. }
51. while(1)
52. {
53. /*
54. * Establish on_timeout() as callback function. The callback
55. * function is called with an argument value of 2 when the
56. * database server has executed a single SQL request for number
57. * of milliseconds specified by the DB_TIMEOUT constant
58. * (0.00333333 minutes by default). Call to sqlbreakcallback()
59. * must come after server connection is established and before
60. * the first SQL statement that can be interrupted.
61. */
62. if (sqlbreakcallback(DB_TIMEOUT, on_timeout))
63. {
64. printf("\nUnable to establish callback function.\n");
65. printf("TIMEOUT Sample Program over.\n\n");
66. exit(1);
67. }
68. /*
69. * Notify end user of timeout interval.
70. */
71. printf("Timeout interval for SQL requests is: ");
72. printf("%0.8f minutes\n", DB_TIMEOUT/60000.00);
73. stcopy("Are you ready to begin execution of the query?",
74. ques);
75. if (prompt_ans(ques) == 'n')
76. {
77. /*
78. * Unregister callback function so table cleanup will not
79. * be interrupted.
80. */
81. sqlbreakcallback(-1L, (void *)NULL);
82. break;
83. }
=======================================================================Lines 43 - 50
The create_tbl() function
creates the canceltst table in the stores7 database.
It inserts MAX_ROWS number of rows into this table. If create_tbl() encounters
some error while it creates canceltst, execution of the timeout program
cannot continue. The program exits with a status value of 1 (line
49).
Line 51
This while loop (which ends on line 97), controls the execution of the query on the canceltst table. It allows the user to run this query multiple times to test various interrupt scenarios.
Lines 53 - 67
The first task of the while loop
is to use sqlbreakcallback() to specify a timeout
interval of DB_TIMEOUT (200) milliseconds and to register on_timeout() as
the callback function. If this call to sqlbreakcallback() fails,
the program exits with a status value of 1. To test
different timeout intervals, you can change the DB_TIMEOUT constant
value and recompile the timeout.ec source file.
Lines 68 - 72
These printf() functions notify the user of the timeout interval. Notice that the message displays this interval in minutes, not milliseconds. It divides the DB_TIMEOUT value by 60,000 (number of milliseconds in a minute).
Lines 73 - 83
The prompt_ans() function
asks the user to indicate when to begin execution of the canceltst query.
If the user enters n (no), the program calls the sqlbreakcallback() function
to unregister the callback function. This call prevents the SQL statements
in the drop_tbl() function (lines 322 - 329) from
initiating the callback function. For a description of the prompt_ans() function,
see Lines 337 - 347.
=======================================================================
84. /*
85. * Start display of query output
86. */
87. printf("\nBeginning execution of query...\n\n");
88. if ((ret = dspquery()) == 0)
89. {
90. if (prompt_ans("Try another run?") == 'y')
91. continue;
92. else
93. break;
94. }
95. else /* dspquery() encountered an error */
96. exit(1);
97. } /* end while */
98. /*
99. * Drop the table created for this program
100. */
101. drop_tbl();
102. EXEC SQL disconnect current;
103. if (exp_chk2("DISCONNECT for stores7", WARNNOTIFY) != 0)
104. exit(1);
105. printf("\nDisconnected stores7 connection\n");
106. printf("\nTIMEOUT Sample Program over.\n\n");
107. }
108. /* This function performs the query on the canceltst table. */
109. int4 dspquery()
110. {
111. mint cnt = 0;
112. int4 ret = 0;
113. int4 sqlcode = 0;
114. int4 sqlerr_code, sqlstate_err();
115. void disp_exception(), disp_error(), disp_warning();
116. EXEC SQL BEGIN DECLARE SECTION;
117. char fld1_val[ CHARFLDSIZE + 1 ];
118. char fld2_val[ CHARFLDSIZE + 1 ];
119. int4 int_val;
120. EXEC SQL END DECLARE SECTION;
121. /* This query contains an artificially complex WHERE clause to
122. * keep the database server busy long enough for an interrupt
123. * to occur.
124. */
125. EXEC SQL declare cancel_curs cursor for
126. select sum(int_fld), char_fld1, char_fld2
127. from canceltst
128. where char_fld1 matches "*f*"
129. or char_fld1 matches "*h*"
130. or char_fld2 matches "*w*"
131. or char_fld2 matches "*l*"
132. group by char_fld1, char_fld2;
=======================================================================Lines 84 - 97
If the user chooses to continue the query, the program calls the dspquery() function (line 88) to run the canceltst query. The prompt_ans() function displays a prompt so the user can decide whether to run the program again.
Lines 98 - 101
The drop_tbl() function drops the canceltst table from the stores7 database to clean up after the program.
Lines 108 - 120
The dspquery() function runs a query of the canceltst table and displays the results. It returns zero (success) or the negative value of SQLCODE (failure) to indicate the result of the canceltst query.
Lines 121 - 132
- All char_fld1 columns that contain an
for anhwith the criteria:char_fld1 matches "*f*" or char_fld1 matches "*h*"These criteria match rows with a char_fld1 value of
Informixor “4100 Bohannon Dr.” - All char_fld2 columns that contain a
wor alwith the criteria:char_fl2 matches "*w*" or char_fld2 matches "*l*"These criteria match rows with a char_fld2 value of
Softwareor “Menlo Park, CA”.
This SELECT is artificially complex to ensure that the query takes a long time to execute. Without a reasonably complex query, the database server finishes execution before the user has a chance to interrupt it. In a production application, only use the sqlbreakcallback() feature with queries that take a long time to execute.
=======================================================================
EXEC SQL open cancel_curs;
sqlcode = SQLCODE;
sqlerr_code = sqlstate_err(); /* check SQLSTATE for exception */
if (sqlerr_code != 0) /* if exception found */
{
if (sqlerr_code == -1) /* runtime error encountered */
{
if (sqlcode == SQL_INTERRUPT) /* user interrupt */
{
/* This is where you would clean up resources */
printf("\n TIMEOUT INTERRUPT PROCESSED\n\n");
sqlcode = 0;
}
else /* serious runtime error */
disp_error("OPEN cancel_curs");
EXEC SQL close cancel_curs;
EXEC SQL free cancel_curs;
return(sqlcode);
}
else if (sqlerr_code == 1) /* warning encountered */
disp_warning("OPEN cancel_curs");
}
=======================================================================Line 133
This OPEN statement causes the database server to execute the SELECT that is associated with the cancel_curs cursor. Because the database server executes the canceltst query, this OPEN is the statement that the user would be most likely to interrupt. When the FETCH executes, the database server just sends matching rows to the application, an operation that is not usually time intensive.
Lines 134 - 154
This block of code checks
the success of the OPEN. Since the OPEN can be interrupted, this exception
checking must include an explicit check for the interrupt value of -213.
The database server sets SQLCODE to -213 when it
has interrupted an SQL request. On line 140, the program uses the
SQL_INTERRUPT defined constant (which line 13 defines), for this SQLCODE
value.
The sqlstate_err() function (line 135) uses the GET DIAGNOSTICS statement to analyze the value of the SQLSTATE variable. If this function returns a non-zero value, SQLSTATE indicates a warning, a runtime error, or the NOT FOUND condition. Before the call to sqlstate_err(), line 134 saves the SQLCODE value so that execution of any other SQL statements (such as GET DIAGNOSTICS in sqlstate_err()) does not overwrite it. The function returns the value of SQLCODE if the OPEN encounters a runtime error (line 150).
The first if statement
(line 136) checks if the OPEN encounters any type of exception (sqlstate_err() returns
a nonzero value). The second if (line 138) checks if the OPEN
has generated a runtime error (return value of -1).
However, if the database server has interrupted the OPEN, sqlstate_err() also
returns -1. Since does
not handle an interrupted SQL statement as a runtime error, the third if checks
explicitly for the SQL_INTERRUPT value (line 140). If the OPEN was
interrupted, line 143 notifies the user that the interrupt request
was successful and then the function resets the saved SQLCODE value
(in sqlcode) to zero to indicate that the OPEN did not generate
a runtime error.
Lines 146 and 147 execute only if the OPEN
generates a runtime error other than SQL_INTERRUPT (-213).
The disp_error() function displays the exception
information in the diagnostics area and the SQLCODE value. Lines 148
- 150 cleanup after the OPEN. They close and free the cancel_curs cursor
and then return the SQLCODE value. The dspquery() function
does not continue with the FETCH (line 158) if the OPEN was interrupted.
If sqlstate_err() returns
one (1), the OPEN has generated a warning. Lines
152 and 153 call the disp_warning() function to
display warning information from the diagnostics area. For more information
about the disp_error() and disp_warning() functions,
see Lines 348 - 355.
=======================================================================
155. printf("Displaying data...\n");
156. while(1)
157. {
158. EXEC SQL fetch cancel_curs into :int_val, :fld1_val,
:fld2_val;
159. if ((ret = exp_chk2("FETCH from cancel_curs", NOWARNNOTIFY))
== 0)
160. {
161. printf(" sum(int_fld) = %d\n", int_val);
162. printf(" char_fld1 = %s\n", fld1_val);
163. printf(" char_fld2 = %s\n\n", fld2_val);
164. }
165. /*
166. * Will display warning messages (WARNNOTIFY) but continue
167. * execution when they occur (exp_chk2() == 1)
168. */
169. else
170. {
171. if (ret==100) /* NOT FOUND condition */
172. {
173. printf("\nNumber of rows found: %d\n\n", cnt);
174. break;
175. }
176. if (ret < 0) /* Runtime error */
177. {
178. EXEC SQL close cancel_curs;
179. EXEC SQL free cancel_curs;
180. return(ret);
181 . }
182. }
183. cnt++;
184. } /* end while */
185. EXEC SQL close cancel_curs;
186. EXEC SQL free cancel_curs;
187. return(0);
188. }
189. /*
190. * The on_timeout() function is the callback function. If the user
191. * confirms the cancellation, this function uses sqlbreak() to
192. * send an interrupt request to the database server.
193. */
194. void on_timeout(when_called)
195. mint when_called;
196. {
197. mint ret;
198. static intr_sent;
=======================================================================Lines 155 - 182
This while loop executes
for each row that the cancel_curs cursor contains. The FETCH
statement (line 158) retrieves one row from the cancel_curs cursor.
If the FETCH generates an error, the function releases the cursor
resources and returns the SQLCODE error value (lines 176 - 181). Otherwise,
the function displays the retrieved data to the user. On the last
row (ret = 100), the function displays the
number of rows that it retrieved (line 173).
Lines 185 - 187
After the FETCH has retrieved the last row from the cursor, the function releases resources allocated to the cancel_curs cursor and returns a success value of zero.
Lines 190 - 198
The on_timeout() function is the callback function for the timeout program. The sqlbreakcallback() call on line 62 registers this callback function and establishes a timeout interval of 200 milliseconds. This function is called every time the database server begins and ends an SQL request. For long-running requests, the application also calls on_timeout() each time the timeout interval elapses.
=======================================================================
199. /* Determine when callback function has been called. */
200. switch(when_called)
201. {
202. case 0: /* Request to server completed */
203. printf("+------SQL Request ends");
204. printf("-------------------------------+\n\n");
205. /*
206. * Unregister callback function so no further SQL statements
207. * can be interrupted.
208. */
209. if (intr_sent)
210. sqlbreakcallback(-1L, (void *)NULL);
211. break;
212. case 1: /* Request to server begins */
213. printf("+------SQL Request begins");
214. printf("-----------------------------+\n");
215. printf("| ");
216. printf(" |\n");
217. intr_sent = 0;
218. break;
219. case 2: /* Timeout interval has expired */
220. /*
221. * Is the database server still processing the request?
222. */
223. if (sqldone() == SERVER_BUSY)
224. if (!intr_sent) /* has interrupt already been sent? */
225. {
226. printf("| An interrupt has been received ");
227. printf("by the application.|\n");
228. printf("| ");
229. printf(" |\n");
230. /*
231. * Ask user to confirm interrupt
232. */
233. if (cancel_request())
234. {
235. printf("| TIMEOUT INTERRUPT ");
236. printf("REQUESTED |\n");
237. /*
238. * Call sqlbreak() to issue an interrupt request for
239. * current SQL request to be cancelled.
240. */
241. sqlbreak();
242. }
243. intr_sent = 1;
244. }
245. break;
=======================================================================Lines 199 - 249
- Lines 202 - 211: If when_called is
0, the callback function was called after the database server ends an SQL request. The function displays the bottom of the message-request box to indicate the end of the SQL request, as follows:+------SQL Request ends-------------------------------+ - Lines 212 - 218: If when_called is
1, the callback function was called when the database server begins an SQL request. The display of the top of the message-request box indicates this condition:+------SQL Request begins-----------------------------+ | |For more information about these message-request boxes, see Lines 21 - 30. The function also initializes the intr_sent flag to
0because the user has not yet sent an interrupt for this SQL request. - Lines 219 - 245: If when_called is
2, the callback function was called because the timeout interval has elapsed.
To handle the elapsed timeout interval, the callback function
first calls the sqldone() function
(line 223) to determine whether the database server is still busy
processing the SQL request. If the database server is idle, the application
does not need to send an interrupt. If sqldone() returns
SERVER_BUSY (-439), the database server is still
busy.
Line 224 checks if the user has already attempted to
interrupt the SQL request that is currently executing. If an interrupt
was sent, intr_sent is 1, and the program
does not need to send another request. If an interrupt request has
not yet been sent, the callback function notifies the user that the
timeout interval has elapsed (lines 226 - 229). It then uses the cancel_request() function
(line 233) to allow the user to confirm the interrupt. For more information
about cancel_request(), see Lines 251 - 261.
=======================================================================
246. default:
247. printf("Invalid status value in callback: %d\n", when_called);
248. break;
249. }
250. }
251. /* This function prompts the user to confirm the sending of an
252. * interrupt request for the current SQL request.
253. */
254. mint cancel_request()
255. {
256. char prompt_ans();
257. if (prompt_ans("Do you want to confirm this interrupt?") == 'n')
258. return(0); /* don't interrupt SQL request */
259. else
260. return(1); /* interrupt SQL request */
261. }
262. /* This function creates a new table in the current database. It
263. * populates this table with MAX_ROWS rows of data. */
264. mint create_tbl()
265. {
266. char st_msg[15];
267. int ret = 1;
268. EXEC SQL BEGIN DECLARE SECTION;
269. mint cnt;
270. mint pa;
271. mint i;
272. char fld1[ CHARFLDSIZE + 1 ], fld2[ CHARFLDSIZE + 1 ];
273. EXEC SQL END DECLARE SECTION;
274. /*
275. * Create canceltst table in current database
276. */
277. EXEC SQL create table canceltst (char_fld1 char(20),
278. char_fld2 char(20), int_fld integer);
279. if (exp_chk2("CREATE TABLE", WARNNOTIFY) < 0)
280. return(0);
281. printf("Created table 'canceltst'\n");
282. /*
283. * Insert MAX_ROWS of data into canceltst
284. */
285. printf("Inserting rows into 'canceltst'...\n");
286. for (i = 0; i < MAX_ROWS; i++)
187. {
=======================================================================Lines 199 - 249 (continued)
If the user
confirms the interrupt, the callback function calls the sqlbreak() function
to send the interrupt request to the database server. The callback
function does not wait for the database server to respond to the interrupt
request. Execution continues to line 243 and sets the intr_sent flag
to 1, to indicate that the interrupt request was
sent. If the callback function was called with an invalid argument
value (a value other than 0, 1,
or 2), the function displays an error message (line
247).
Lines 251 - 261
Do you want to confirm this interrupt?If
the user answers y (yes), cancel_request() returns 0.
If the user answers n (no), cancel_request() returns 1.
Lines 262 - 281
The create_tbl() function creates the canceltst table and inserts the test data into this table. The CREATE TABLE statement (lines 277 and 278) creates the canceltst table with three columns: int_fld, char_fld1, and char_fld2. If the CREATE TABLE encounters an error, the exp_chk2() function (line 279) displays the diagnostics-area information and create_tbl() returns 0 to indicate that an error has occurred.
Lines 282 - 287
This for loop controls the insertion of the canceltst rows. The MAX_ROWS constant determines the number of iterations for the loop, and hence the number of rows that the function inserts into the table. If you cannot interrupt the canceltst query (lines 126 - 132) because it executes too quickly, increase the value of MAX_ROWS and recompile the timeout.ec file.
=======================================================================
288. if (i%2 == 1) /* odd-numbered rows */
289. {
290. stcopy("4100 Bohannan Dr", fld1);
291 stcopy("Menlo Park, CA", fld2);
292. }
293. else /* even-numbered rows */
294. {
295. stcopy("Informix", fld1);
296. stcopy("Software", fld2);
297. }
298. EXEC SQL insert into canceltst
299. values (:fld1, :fld2, :i);
300. if ( (i+1)%1000 == 0 ) /* every 1000 rows */
301. printf(" Inserted %d rows\n", i+1);
302. sprintf(st_msg, "INSERT #%d", i);
303. if (exp_chk2(st_msg, WARNNOTIFY) < 0)
304. {
305. ret = 0;
306. break;
307. }
308. }
309. printf("Inserted %d rows into 'canceltst'.\n", MAX_ROWS);
310. /*
311. * Verify that MAX_ROWS rows have added to canceltst
312. */
313. printf("Counting number of rows in 'canceltst' table...\n");
314. EXEC SQL select count(*) into :cnt from canceltst;
315. if (exp_chk2("SELECT count(*)", WARNNOTIFY) < 0)
316. return(0);
317. printf("Number of rows = %d\n\n", cnt);
318. return (ret);
319. }
320. /* This function drops the 'canceltst' table */
321. mint drop_tbl()
322. {
323. printf("\nCleaning up...\n");
324. EXEC SQL drop table canceltst;
325. if (exp_chk2("DROP TABLE", WARNNOTIFY) < 0)
326. return(0);
327. printf("Dropped table 'canceltst'\n");
328. return(1);
329. }
=======================================================================Lines 288 - 292
This if statement
generates the values for the char_fld1 and char_fld2 columns
of the canceltst table. Lines 290 and 291 execute for odd-numbered
rows. They store the strings “4100 Bohannon Dr”
and “Menlo Park, CA” in the fld1 and fld2 variables.
Lines 293 - 297
Lines 295 and 296 execute
for even-numbered rows. They store the strings Informix and Software in
the fld1 and fld2 variables.
Lines 298 - 307
The INSERT statement inserts a row into the canceltst table. It takes the value for the int_fld column from the :i host variable (the row number), and the values for the char_fld1 and char_fld2 columns from the :fld1 and :fld2 host variables. The function notifies the user after it inserts every 1000 rows (lines 300 and 301). If the INSERT encounters an error, the exp_chk2() function (line 303) displays the diagnostics-area information and create_tbl() returns zero to indicate that an error has occurred.
Lines 300 - 317
These lines verify that
the program has added the rows to the canceltst table and that
it can access them. The program does a SELECT on the newly created canceltst table
and returns the number of rows found. The program checks whether this
number matches the number that the function has added, which line
309 displays. If the SELECT encounters an error, the exp_chk2() function
(line 315) displays the diagnostics-area information, and create_tbl() returns 0 to
indicate that an error has occurred.
Lines 320 - 329
The drop_tbl() function
drops the canceltst table from the current database. If the
DROP TABLE statement (line 324) encounters an error, the exp_chk2() function
displays the diagnostics-area information and drop_tbl() returns 0 to
indicate that an error has occurred.
=======================================================================
330. /*
331. * The inpfuncs.c file contains the following functions used in
this
332. * program:
333. * getans(ans, len) - accepts user input, up to 'len' number of
334. * characters and puts it in 'ans'
335. */
336. #include "inpfuncs.c"
337. char prompt_ans(question)
338. char * question;
339. {
340. char ans = ‘ ‘;
341. while(ans != 'y' && ans != 'n')
342. {
343. printf("\n*** %s (y/n): ", question);
344. getans(&ans,1);
345. }
346. return ans;
347. }
348. /*
349. * The exp_chk() file contains the exception handling functions to
350. * check the SQLSTATE status variable to see if an error has
* occurred
351. * following an SQL statement. If a warning or an error has
352. * occurred, exp_chk2() executes the GET DIAGNOSTICS statement and
353. * displays the detail for each exception that is returned.
354. */
355. EXEC SQL include exp_chk.ec;
=======================================================================Lines 330 - 336
Several of the demonstration programs also call the getans() function. Therefore, this function is broken out into a separate C source file and included in the appropriate demonstration program. Because this function does not contain , the program can use the C #include preprocessor statement to include the file. For a description of this function, see ids_esqlc_0234.html#ids_esqlc_0234.
Lines 337 - 347
The prompt_ans() function
displays the string in the question argument and waits for
the user to enter y (yes) or n (no)
as a response. It returns the single-character response.