1 /*******************************************************************
2 Sample Program 10: Dynamic SQL Method 4
3
4 This program connects you to ORACLE using your username and
5 password, then prompts you for a SQL statement. You can enter
6 any legal SQL statement. Use regular SQL syntax, not embedded SQL.
7 Your statement will be processed. If it is a query, the rows
8 fetched are displayed.
9 You can enter multiline statements. The limit is 1023 characters.
10 This sample program only processes up to MAX_ITEMS bind variables and
11 MAX_ITEMS select-list items. MAX_ITEMS is #defined to be 40.
12 *******************************************************************/
13
14 #include <stdio.h>
15 #include <string.h>
16 #include <setjmp.h>
17 #include <sqlda.h>
18 #include <stdlib.h>
19 #include <sqlcpr.h>
20
21 #define SQL_SINGLE_RCTX ((void *)0)
22
23 /* Maximum number of select-list items or bind variables. */
24 #define MAX_ITEMS 40
25
26 /* Maximum lengths of the _names_ of the
27 select-list items or indicator variables. */
28 #define MAX_VNAME_LEN 30
29 #define MAX_INAME_LEN 30
30
31 #ifndef NULL
32 #define NULL 0
33 #endif
34
35 /* Prototypes */
36 #if defined(__STDC__)
37 void sql_error(void);
38 int oracle_connect(void);
39 int alloc_descriptors(int, int, int);
40 int get_dyn_statement(void);
41 void set_bind_variables(void);
42 void process_select_list(void);
43 void help(void);
44 #else
45 void sql_error(/*_ void _*/);
46 int oracle_connect(/*_ void _*/);
47 int alloc_descriptors(/*_ int, int, int _*/);
48 int get_dyn_statement(/* void _*/);
49 void set_bind_variables(/*_ void -*/);
50 void process_select_list(/*_ void _*/);
51 void help(/*_ void _*/);
52 #endif
53
54 char *dml_commands[] = {"SELECT", "select", "INSERT", "insert",
55 "UPDATE", "update", "DELETE", "delete"};
56
57 EXEC SQL INCLUDE sqlda;
58 EXEC SQL INCLUDE sqlca;
59
60 EXEC SQL BEGIN DECLARE SECTION;
61 char dyn_statement[1024];
62 EXEC SQL VAR dyn_statement IS STRING(1024);
63 EXEC SQL END DECLARE SECTION;
64
65 SQLDA *bind_dp;
66 SQLDA *select_dp;
67
68 /* Define a buffer to hold longjmp state info. */
69 jmp_buf jmp_continue;
70
71 /* A global flag for the error routine. */
72 int parse_flag = 0;
73
74 void main()
75 {
76 int i;
77
78 /* Connect to the database. */
79 if (oracle_connect() != 0)
80 exit(1);
81
82 /* Allocate memory for the select and bind descriptors. */
83 if (alloc_descriptors(MAX_ITEMS, MAX_VNAME_LEN, MAX_INAME_LEN) != 0)
84 exit(1);
85
86 /* Process SQL statements. */
87 for (;;)
88 {
89 (void) setjmp(jmp_continue);
90
91 /* Get the statement. Break on "exit". */
92 if (get_dyn_statement() != 0)
93 break;
94
95 /* Prepare the statement and declare a cursor. */
96 EXEC SQL WHENEVER SQLERROR DO sql_error();
97
98 parse_flag = 1; /* Set a flag for sql_error(). */
99 EXEC SQL PREPARE S FROM :dyn_statement;
100 parse_flag = 0; /* Unset the flag. */
101
102 EXEC SQL DECLARE C CURSOR FOR S;
103
104 /* Set the bind variables for any placeholders in the
105 SQL statement. */
106 set_bind_variables();
107
108 /* Open the cursor and execute the statement.
109 * If the statement is not a query (SELECT), the
110 * statement processing is completed after the
111 * OPEN.
112 */
113
114 EXEC SQL OPEN C USING DESCRIPTOR bind_dp;
115
116 /* Call the function that processes the select-list.
117 * If the statement is not a query, this function
118 * just returns, doing nothing.
119 */
120 process_select_list();
121
122 /* Tell user how many rows processed. */
123 for (i = 0; i < 8; i++)
124 {
125 if (strncmp(dyn_statement, dml_commands[i], 6) == 0)
126 {
127 printf("\n\n%d row%c processed.\n", sqlca.sqlerrd[2],
128 sqlca.sqlerrd[2] == 1 ? '\0' : 's');
129 break;
130 }
131 }
132 } /* end of for(;;) statement-processing loop */
133
134 /* When done, free the memory allocated for
135 pointers in the bind and select descriptors. */
136 for (i = 0; i < MAX_ITEMS; i++)
137 {
138 if (bind_dp->V[i] != (char *) 0)
139 free(bind_dp->V[i]);
140 free(bind_dp->I[i]); /* MAX_ITEMS were allocated. */
141 if (select_dp->V[i] != (char *) 0)
142 free(select_dp->V[i]);
143 free(select_dp->I[i]); /* MAX_ITEMS were allocated. */
144 }
145
146 /* Free space used by the descriptors themselves. */
147 SQLSQLDAFree(SQL_SINGLE_RCTX, bind_dp);
148 SQLSQLDAFree(SQL_SINGLE_RCTX, select_dp);
149
150 EXEC SQL WHENEVER SQLERROR CONTINUE;
151 /* Close the cursor. */
152 EXEC SQL CLOSE C;
153
154 EXEC SQL COMMIT WORK RELEASE;
155 puts("\nHave a good day!\n");
156
157 EXEC SQL WHENEVER SQLERROR DO sql_error();
158 return;
159 }
160
161
162 int oracle_connect()
163 {
164 EXEC SQL BEGIN DECLARE SECTION;
165 VARCHAR username[128];
166 VARCHAR password[32];
167 EXEC SQL END DECLARE SECTION;
168
169 printf("\nusername: ");
170 fgets((char *) username.arr, sizeof username.arr, stdin);
171 username.arr[strlen((char *) username.arr)-1] = '\0';
172 username.len = (unsigned short)strlen((char *) username.arr);
173
174 printf("password: ");
175 fgets((char *) password.arr, sizeof password.arr, stdin);
176 password.arr[strlen((char *) password.arr) - 1] = '\0';
177 password.len = (unsigned short)strlen((char *) password.arr);
178
179
180 EXEC SQL WHENEVER SQLERROR GOTO connect_error;
181
182 EXEC SQL CONNECT :username IDENTIFIED BY :password;
183
184 printf("\nConnected to ORACLE as user %s.\n", username.arr);
185
186 return 0;
187
188 connect_error:
189 fprintf(stderr, "Cannot connect to ORACLE as user %s\n", username.arr);
190 return -1;
191 }
192
193
194 /*
195 * Allocate the BIND and SELECT descriptors using SQLSQLDAAlloc().
196 * Also allocate the pointers to indicator variables
197 * in each descriptor. The pointers to the actual bind
198 * variables and the select-list items are realloc'ed in
199 * the set_bind_variables() or process_select_list()
200 * routines. This routine allocates 1 byte for select_dp->V[i]
201 * and bind_dp->V[i], so the realloc will work correctly.
202 */
203
204 alloc_descriptors(size, max_vname_len, max_iname_len)
205 int size;
206 int max_vname_len;
207 int max_iname_len;
208 {
209 int i;
210
211 /*
212 * The first SQLSQLDAAlloc parameter is the runtime context.
213
214 * The second parameter determines the maximum number of
215 * array elements in each variable in the descriptor. In
216 * other words, it determines the maximum number of bind
217 * variables or select-list items in the SQL statement.
218 *
219 * The third parameter determines the maximum length of
220 * strings used to hold the names of select-list items
221 * or placeholders. The maximum length of column
222 * names in ORACLE is 30, but you can allocate more or less
223 * as needed.
224 *
225 * The fourth parameter determines the maximum length of
226 * strings used to hold the names of any indicator
227 * variables. To follow ORACLE standards, the maximum
228 * length of these should be 30. But, you can allocate
229 * more or less as needed.
230 */
231
232 if ((bind_dp =
233 SQLSQLDAAlloc(SQL_SINGLE_RCTX, size, max_vname_len, max_iname_len)) ==
234 (SQLDA *) 0)
235 {
236 fprintf(stderr,
237 "Cannot allocate memory for bind descriptor.");
238 return -1; /* Have to exit in this case. */
239 }
240
241 if ((select_dp =
242 SQLSQLDAAlloc (SQL_SINGLE_RCTX, size, max_vname_len, max_iname_len)) ==
243 (SQLDA *) 0)
244 {
245 fprintf(stderr,
246 "Cannot allocate memory for select descriptor.");
247 return -1;
248 }
249 select_dp->N = MAX_ITEMS;
250
251 /* Allocate the pointers to the indicator variables, and the
252 actual data. */
253 for (i = 0; i < MAX_ITEMS; i++) {
254 bind_dp->I[i] = (short *) malloc(sizeof (short));
255 select_dp->I[i] = (short *) malloc(sizeof(short));
256 bind_dp->V[i] = (char *) malloc(1);
257 select_dp->V[i] = (char *) malloc(1);
258 }
259
260 return 0;
261 }
262
263
264 int get_dyn_statement()
265 {
266 char *cp, linebuf[256];
267 int iter, plsql;
268
269
270 for (plsql = 0, iter = 1; ;)
271 {
272 if (iter == 1)
273 {
274 printf("\nSQL> ");
275 dyn_statement[0] = '\0';
276 }
277
278 fgets(linebuf, sizeof linebuf, stdin);
279
280 cp = strrchr(linebuf, '\n');
281 if (cp && cp != linebuf)
282 *cp = ' ';
283 else if (cp == linebuf)
284 continue;
285
286 if ((strncmp(linebuf, "EXIT", 4) == 0) ||
287 (strncmp(linebuf, "exit", 4) == 0))
288 {
289 return -1;
290 }
291
292 else if (linebuf[0] == '?' ||
293 (strncmp(linebuf, "HELP", 4) == 0) ||
294 (strncmp(linebuf, "help", 4) == 0))
295 {
296 help();
297 iter = 1;
298 continue;
299 }
300
301 if (strstr(linebuf, "BEGIN") ||
302 (strstr(linebuf, "begin")))
303 {
304 plsql = 1;
305 }
306
307 strcat(dyn_statement, linebuf);
308
309 if ((plsql && (cp = strrchr(dyn_statement, '/'))) ||
310 (!plsql && (cp = strrchr(dyn_statement, ';'))))
311 {
312 *cp = '\0';
313 break;
314 }
315 else
316 {
317 iter++;
318 printf("%3d ", iter);
319 }
320 }
321 return 0;
322 }
323
324
325
326 void set_bind_variables()
327 {
328 int i, n;
329 char bind_var[64];
330
331 /* Describe any bind variables (input host variables) */
332 EXEC SQL WHENEVER SQLERROR DO sql_error();
333
334 bind_dp->N = MAX_ITEMS; /* Initialize count of array elements. */
335 EXEC SQL DESCRIBE BIND VARIABLES FOR S INTO bind_dp;
336
337 /* If F is negative, there were more bind variables
338 than originally allocated by SQLSQLDAAlloc(). */
339 if (bind_dp->F < 0)
340 {
341 printf ("\nToo many bind variables (%d), maximum is %d\n.",
342 -bind_dp->F, MAX_ITEMS);
343 return;
344 }
345
346 /* Set the maximum number of array elements in the
347 descriptor to the number found. */
348 bind_dp->N = bind_dp->F;
349
350 /* Get the value of each bind variable as a
351 * character string.
352 *
353 * C[i] contains the length of the bind variable
354 * name used in the SQL statement.
355 * S[i] contains the actual name of the bind variable
356 * used in the SQL statement.
357 *
358 * L[i] will contain the length of the data value
359 * entered.
360 *
361 * V[i] will contain the address of the data value
362 * entered.
363 *
364 * T[i] is always set to 1 because in this sample program
365 * data values for all bind variables are entered
366 * as character strings.
367 * ORACLE converts to the table value from CHAR.
368 *
369 * I[i] will point to the indicator value, which is
370 * set to -1 when the bind variable value is "null".
371 */
372 for (i = 0; i < bind_dp->F; i++)
373 {
374 printf ("\nEnter value for bind variable %.*s: ",
375 (int)bind_dp->C[i], bind_dp->S[i]);
376 fgets(bind_var, sizeof bind_var, stdin);
377
378 /* Get length and remove the new line character. */
379 n = strlen(bind_var) - 1;
380
381 /* Set it in the descriptor. */
382 bind_dp->L[i] = n;
383
384 /* (re-)allocate the buffer for the value.
385 SQLSQLDAAlloc() reserves a pointer location for
386 V[i] but does not allocate the full space for
387 the pointer. */
388
389 bind_dp->V[i] = (char *) realloc(bind_dp->V[i],
390 (bind_dp->L[i] + 1));
391
392 /* And copy it in. */
393 strncpy(bind_dp->V[i], bind_var, n);
394
395 /* Set the indicator variable's value. */
396 if ((strncmp(bind_dp->V[i], "NULL", 4) == 0) ||
397 (strncmp(bind_dp->V[i], "null", 4) == 0))
398 *bind_dp->I[i] = -1;
399 else
400 *bind_dp->I[i] = 0;
401
402 /* Set the bind datatype to 1 for CHAR. */
403 bind_dp->T[i] = 1;
404 }
405 return;
406 }
407
408
409
410 void process_select_list()
411 {
412 int i, null_ok, precision, scale;
413
414 if ((strncmp(dyn_statement, "SELECT", 6) != 0) &&
415 (strncmp(dyn_statement, "select", 6) != 0))
416 {
417 select_dp->F = 0;
418 return;
419 }
420
421 /* If the SQL statement is a SELECT, describe the
422 select-list items. The DESCRIBE function returns
423 their names, datatypes, lengths (including precision
424 and scale), and NULL/NOT NULL statuses. */
425
426 select_dp->N = MAX_ITEMS;
427
428 EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;
429
430 /* If F is negative, there were more select-list
431 items than originally allocated by SQLSQLDAAlloc(). */
432 if (select_dp->F < 0)
433 {
434 printf ("\nToo many select-list items (%d), maximum is %d\n",
435 -(select_dp->F), MAX_ITEMS);
436 return;
437 }
438
439 /* Set the maximum number of array elements in the
440 descriptor to the number found. */
441 select_dp->N = select_dp->F;
442
443 /* Allocate storage for each select-list item.
444
445 SQLNumberPrecV6() is used to extract precision and scale
446 from the length (select_dp->L[i]).
447
448 sqlcolumnNullCheck() is used to reset the high-order bit of
449 the datatype and to check whether the column
450 is NOT NULL.
451
452 CHAR datatypes have length, but zero precision and
453 scale. The length is defined at CREATE time.
454
455 NUMBER datatypes have precision and scale only if
456 defined at CREATE time. If the column
457 definition was just NUMBER, the precision
458 and scale are zero, and you must allocate
459 the required maximum length.
460
461 DATE datatypes return a length of 7 if the default
462 format is used. This should be increased to
463 9 to store the actual date character string.
464 If you use the TO_CHAR function, the maximum
465 length could be 75, but will probably be less
466 (you can see the effects of this in SQL*Plus).
467
468 ROWID datatype always returns a fixed length of 18 if
469 coerced to CHAR.
470
471 LONG and
472 LONG RAW datatypes return a length of 0 (zero),
473 so you need to set a maximum. In this example,
474 it is 240 characters.
475
476 */
477
478 printf ("\n");
479 for (i = 0; i < select_dp->F; i++)
480 {
481 char title[MAX_VNAME_LEN];
482 /* Turn off high-order bit of datatype (in this example,
483 it does not matter if the column is NOT NULL). */
484 SQLColumnNullCheck (0, (unsigned short *)&(select_dp->T[i]),
485 (unsigned short *)&(select_dp->T[i]), &null_ok);
486
487 switch (select_dp->T[i])
488 {
489 case 1 : /* CHAR datatype: no change in length
490 needed, except possibly for TO_CHAR
491 conversions (not handled here). */
492 break;
493 case 2 : /* NUMBER datatype: use SQLNumberPrecV6() to
494 extract precision and scale. */
495 SQLNumberPrecV6( SQL_SINGLE_RCTX,
496 (unsigned long *)&(select_dp->L[i]), &precision, &scale);
497 /* Allow for maximum size of NUMBER. */
498 if (precision == 0) precision = 40;
499 /* Also allow for decimal point and
500 possible sign. */
501 /* convert NUMBER datatype to FLOAT if scale > 0,
502 INT otherwise. */
503 if (scale > 0)
504 select_dp->L[i] = sizeof(float);
505 else
506 select_dp->L[i] = sizeof(int);
507 break;
508
509 case 8 : /* LONG datatype */
510 select_dp->L[i] = 240;
511 break;
512
513 case 11 : /* ROWID datatype */
514 select_dp->L[i] = 18;
515 break;
516
517 case 12 : /* DATE datatype */
518 select_dp->L[i] = 9;
519 break;
520
521 case 23 : /* RAW datatype */
522 break;
523
524 case 24 : /* LONG RAW datatype */
525 select_dp->L[i] = 240;
526 break;
527 }
528 /* Allocate space for the select-list data values.
529 SQLSQLDAAlloc() reserves a pointer location for
530 V[i] but does not allocate the full space for
531 the pointer. */
532
533 if (select_dp->T[i] != 2)
534 select_dp->V[i] = (char *) realloc(select_dp->V[i],
535 select_dp->L[i] + 1);
536 else
537 select_dp->V[i] = (char *) realloc(select_dp->V[i],
538 select_dp->L[i]);
539
540 /* Print column headings, right-justifying number
541 column headings. */
542
543 /* Copy to temporary buffer in case name is null-terminated */
544 memset(title, ' ', MAX_VNAME_LEN);
545 strncpy(title, select_dp->S[i], select_dp->C[i]);
546 if (select_dp->T[i] == 2)
547 if (scale > 0)
548 printf ("%.*s ", select_dp->L[i]+3, title);
549 else
550 printf ("%.*s ", select_dp->L[i], title);
551 else
552 printf("%-.*s ", select_dp->L[i], title);
553
554 /* Coerce ALL datatypes except for LONG RAW and NUMBER to
555 character. */
556 if (select_dp->T[i] != 24 && select_dp->T[i] != 2)
557 select_dp->T[i] = 1;
558
559 /* Coerce the datatypes of NUMBERs to float or int depending on
560 the scale. */
561 if (select_dp->T[i] == 2)
562 if (scale > 0)
563 select_dp->T[i] = 4; /* float */
564 else
565 select_dp->T[i] = 3; /* int */
566 }
567 printf ("\n\n");
568
569 /* FETCH each row selected and print the column values. */
570 EXEC SQL WHENEVER NOT FOUND GOTO end_select_loop;
571
572 for (;;)
573 {
574 EXEC SQL FETCH C USING DESCRIPTOR select_dp;
575
576 /* Since each variable returned has been coerced to a
577 character string, int, or float very little processing
578 is required here. This routine just prints out the
579 values on the terminal. */
580 for (i = 0; i < select_dp->F; i++)
581 {
582 if (*select_dp->I[i] < 0)
583 if (select_dp->T[i] == 4)
584 printf ("%-*c ",(int)select_dp->L[i]+3, ' ');
585 else
586 printf ("%-*c ",(int)select_dp->L[i], ' ');
587 else
588 if (select_dp->T[i] == 3) /* int datatype */
589 printf ("%*d ", (int)select_dp->L[i],
590 *(int *)select_dp->V[i]);
591 else if (select_dp->T[i] == 4) /* float datatype */
592 printf ("%*.2f ", (int)select_dp->L[i],
593 *(float *)select_dp->V[i]);
594 else /* character string */
595 printf ("%-*.*s ", (int)select_dp->L[i],
596 (int)select_dp->L[i], select_dp->V[i]);
597 }
598 printf ("\n");
599 }
600 end_select_loop:
601 return;
602 }
603
604
605
606 void help()
607 {
608 puts("\n\nEnter a SQL statement or a PL/SQL block at the SQL> prompt.");
609 puts("Statements can be continued over several lines, except");
610 puts("within string literals.");
611 puts("Terminate a SQL statement with a semicolon.");
612 puts("Terminate a PL/SQL block (which can contain embedded semicolons)");
613 puts("with a slash (/).");
614 puts("Typing \"exit\" (no semicolon needed) exits the program.");
615 puts("You typed \"?\" or \"help\" to get this message.\n\n");
616 }
617
618
619 void sql_error()
620 {
621 /* ORACLE error handler */
622 printf ("\n\n%.70s\n",sqlca.sqlerrm.sqlerrmc);
623 if (parse_flag)
624 printf
625 ("Parse error at character offset %d in SQL statement.\n",
626 sqlca.sqlerrd[4]);
627
628 EXEC SQL WHENEVER SQLERROR CONTINUE;
629 EXEC SQL ROLLBACK WORK;
630 longjmp(jmp_continue, 1);
631 }
1 /* 包含C头文件 */
2 #include <stdio.h>
3 #include <string.h>
4 #include <stdlib.h>
5 #include <setjmp.h>
6 #include <sqlcpr.h>
7
8 /* 包含SQLDA和SQLCA结构 */
9 #include <sqlda.h>
10 #include <sqlca.h>
11
12 /* 定义绑定变量和选择列表项的最大个数 */
13 #define MAX_ITEMS 40
14
15 /* 定义绑定变量和选择列表项名称的最大长度 */
16 #define MAX_VNAME_LEN 30
17
18 /* 定义指示变量名称的最大长度 */
19 #define MAX_INAME_LEN 30
20
21 void connect();
22 void sql_error();
23 void alloc_descriptors(int , int , int);
24 void dealloc_descriptors();
25 void set_bind_variables();
26 void process_select_list();
27
28 /* 定义绑定描述区和选择描述区 */
29 SQLDA* bind_dp;
30 SQLDA* select_dp;
31
32 /* 定义输入宿主变量：存放动态SQL语句 */
33 char sql_stat[100];
34 char current_date[20];
35
36 int main()
37 {
38 /* 安装错误处理句柄 */
39 exec sql whenever sqlerror do sql_error();
40
41 /* 连接到数据库 */
42 connect2();
43
44 /* 分配绑定描述区和选择描述区 */
45 alloc_descriptors(MAX_ITEMS , MAX_VNAME_LEN , MAX_INAME_LEN);
46
47 for( ; ; )
48 {
49 printf("请输入动态SQL语句(exit:退出):");
50 gets(sql_stat);
51
52 /* EXIT(exit):退出 */
53 if(0 == strncmp(sql_stat , "exit" , 4) || 0 == strncmp(sql_stat , "EXIT" , 4))
54 break;
55
56 /* 准备动态SQL语句 */
57 exec sql prepare s from :sql_stat;
58
59 /* 定义游标 */
60 exec sql declare c cursor for s;
61
62 /* 出错，继续下一循环 */
63 if(0 != sqlca.sqlcode)
64 continue;
65
66 /* 设置绑定变量 */
67 set_bind_variables();
68
69 /*
70 * 打开游标
71 * select语句：生成结果集
72 * 其他SQL语句：执行语句
73 */
74 exec sql open c using descriptor bind_dp;
75
76 /*
77 * select语句
78 */
79 if(0 == strncmp(sql_stat , "select" , 6) || 0 == strncmp(sql_stat , "SELECT" , 6))
80 {
81 process_select_list();
82 }
83 /* 关闭游标 */
84 exec sql close c;
85 }
86
87 /* 释放选择描述区和选择描述区 */
88 dealloc_descriptors();
89
90 /* 提交事务，断开连接 */
91 exec sql commit work release;
92 puts("谢谢使用Oracle动态SQL方法四!\n");
93
94 return 0;
95 }
96
97
98 void connect2()
99 {
100 /* 定义宿主变量 */
101 char username[20] , password[20] , server[20];
102
103 /* 输入用户名、口令和网络服务名 */
104 printf("username:");
105 gets(username);
106
107 printf("password: ");
108 gets(password);
109
110 printf("server:");
111 gets(server);
112
113 /* 连接到数据库 */
114 EXEC SQL CONNECT :username identified by :password using :server;
115 }
116
117
118 void sql_error()
119 {
120 /* 显示SQL错误信息 */
121 printf("%.*s\n" , sqlca.sqlerrm.sqlerrml , sqlca.sqlerrm.sqlerrmc);
122 }
123
124 //struct SQLDA {
125 // ub4 // int N; // Descriptor size in number of entries //
126 // text** // char **V; // Ptr to Arr of addresses of main variables //
127 // ub4* // int *L; // Ptr to Arr of lengths of buffers //
128 // sb2* // short *T; // Ptr to Arr of types of buffers //
129 // sb2** // short **I; // Ptr to Arr of addresses of indicator vars //
130 // sb4 // int F; // Number of variables found by DESCRIBE //
131 // text** // char **S; // Ptr to Arr of variable name pointers //
132 // ub2* // short *M; // Ptr to Arr of max lengths of var. names //
133 // ub2* // short *C; // Ptr to Arr of current lengths of var. names //
134 // text** // char **X; // Ptr to Arr of ind. var. name pointers //
135 // ub2* // short *Y; // Ptr to Arr of max lengths of ind. var. names//
136 // ub2* // short *Z; // Ptr to Arr of cur lengths of ind. var. names//
137 // };
138
139 void alloc_descriptors(int size , int max_vname_len , int max_iname_len)
140 {
141
142 int i;
143
144 /* 分配绑定描述区和选择描述区 */
145 bind_dp = SQLSQLDAAlloc(0 , size , MAX_VNAME_LEN , MAX_INAME_LEN);
146 select_dp = SQLSQLDAAlloc(0 , size , MAX_VNAME_LEN , MAX_INAME_LEN);
147
148
149 /* 为指示变量、绑定变量和选择列表项分配内存 */
150 for(i = 0 ; i != MAX_ITEMS ; ++i)
151 {
152 bind_dp->I[i] = (short*)malloc(sizeof(short));
153 select_dp->I[i] = (short*)malloc(sizeof(short));
154
155 bind_dp->V[i] = (char*)malloc(1);
156 select_dp->V[i] = (char*)malloc(1);
157 }
158 }
159
160 void dealloc_descriptors()
161 {
162 int i;
163
164 /* 释放指示变量、绑定变量和选择列表项占用的内存 */
165 for(i = 0 ; i != MAX_ITEMS ; ++i)
166 {
167 if(bind_dp->V[i] != (char*)0)
168 free(bind_dp->V[i]);
169 free(bind_dp->I[i]);
170
171 if(select_dp->V[i] != (char*)0)
172 free(select_dp->V[i]);
173 free(select_dp->I[i]);
174 }
175
176 /* 释放绑定描述区和选择描述区 */
177 SQLSQLDAFree(0 , bind_dp);
178 SQLSQLDAFree(0 , select_dp);
179 }
180
181 void set_bind_variables()
182 {
183 int i;
184 char bind_var[64];
185
186 /* 设置绑定变量最大个数 */
187 bind_dp->N = MAX_ITEMS;
188
189 /* 绑定变量名称: 绑定描述区 */
190 exec sql describe bind variables for s into bind_dp;
191
192 /* 设置绑定变量实际个数 */
193 bind_dp->N = bind_dp->F;
194
195 /* 循环处理绑定变量 */
196 for(i = 0 ; i != bind_dp->F ; ++i)
197 {
198 /* 显示绑定变量名 */
199 printf("请输入绑定变量%.*s的值：" , (int)bind_dp->C[i] , bind_dp->S[i]);
200
201 /* 输入绑定变量的值 */
202 gets(bind_var);
203
204 /* 设置绑定变量的长度成员 */
205 bind_dp->L[i] = strlen(bind_var);
206
207 /* 为绑定变量数据缓冲区重新分配内存(多一位，留给'\0') */
208 bind_dp->V[i] = (char*)realloc(bind_dp->V[i] , bind_dp->L[i] + 1);
209
210 /* 绑定变量数据: 数据缓冲区 */
211 strcpy(bind_dp->V[i] , bind_var);
212
213 /* 设置指示变量，处理NULL */
214 if(0 == strncmp(bind_var , "NULL" , 4) || 0 == strncmp(bind_var , "null" , 4))
215 *bind_dp->I[i] = -1;
216 else
217 *bind_dp->I[i] = 0;
218
219 /* 设置数据缓冲区数据类型代码->char */
220 bind_dp->T[i] = 1;
221 }
222 }
223
224 void process_select_list()
225 {
226 int i , null_ok , precision , scale;
227 char title[MAX_VNAME_LEN];
228
229 /* 设置选择列表项的最大个数 */
230 select_dp->N = MAX_ITEMS;
231
232 /* 选择列表项: 选择描述区 */
233 exec sql describe select list for s into select_dp;
234
235 /* 设置选择列表项的实际个数 */
236 select_dp->N = select_dp->F;
237
238 /* 循环处理选择列表项 */
239 for(i = 0 ; i != select_dp->F ; ++i)
240 {
241 /* 清除select_dp->T[i]的高位->null */
242 SQLColumnNullCheck(0 , (unsigned short*)&select_dp->T[i]
243 , (unsigned short*)&select_dp->T[i] , &null_ok);
244
245 /* 根据内部数据类型确定外部类型数据长度(显示长度) */
246 switch(select_dp->T[i])
247 {
248 case 2:
249 /* number类型，取得精度与标度 */
250 //SQLNumberPrecV6(0 , (unsigned short*)&select_dp->T[i] , &precision , &scale);
251 SQLNumberPrecV6(0 , (unsigned long *)&select_dp->L[i] , &precision , &scale); //wangbaoming modify 201409
252
253 if(scale > 0)
254 /* 实数: 显示长度:float */
255 select_dp->L[i] = sizeof(float);
256 else
257 /* 整数: 显示长度 int */
258 select_dp->L[i] = sizeof(int);
259 break;
260 case 12:
261 /* DATA数据类型(DD-MON-YY) */
262 select_dp->L[i] = 9;
263 break;
264 }
265
266 /* 根据变量长度，重新为选择列表项数据缓冲区分配内存 */
267 if(2 != select_dp->T[i])
268 /* 其他类型 */
269 select_dp->V[i] = (char*)realloc(select_dp->V[i] , select_dp->L[i] + 1);
270 else
271 /* number类型 */
272 select_dp->V[i] = (char*)realloc(select_dp->V[i] , select_dp->L[i]);
273
274 /* 初始化title */
275 memset(title , ' ' , MAX_VNAME_LEN);
276
277 /* 选择列表项名称: title */
278 strncpy(title , select_dp->S[i] , select_dp->C[i]);
279
280 /* 显示列名 */
281 if(2 == select_dp->T[i])
282 if(scale > 0)
283 printf("\t%.*s" , select_dp->L[i] + 3, title);
284 else
285 printf("\t%.*s" , select_dp->L[i] , title);
286 else
287 printf("\t%-.*s" , select_dp->L[i] , title);
288
289 /* 根据Oracle内部类型确定外部数据类型(显示类型) */
290 if( 2 == select_dp->T[i])
291 {
292 /* number 类型*/
293 if(scale > 0)
294 /* float */
295 select_dp->T[i] = 4;
296 else
297 /* int */
298 select_dp->T[i] = 3;
299 }
300 else
301 /* char */
302 select_dp->T[i] = 1;
303 }
304
305 printf("\n");
306
307 /* 提取数据完毕->结束循环 */
308 exec sql whenever not found do break;
309
310 /* 循环处理选择列表数据 */
311 for( ; ; )
312 {
313 /* 数据->选择描述区 */
314 exec sql fetch c using descriptor select_dp;
315
316 /* 显示数据 */
317 for( i = 0 ; i != select_dp->F ; ++i)
318 {
319 if(*select_dp->I[i] < 0){
320 /* 处理NULL */
321 printf("\tNULL");
322 }else{
323 if(3 == select_dp->T[i]) {
324 /* int */
325 printf("\t%d" , *(int*)select_dp->V[i]);
326 }else if(4 == select_dp->T[i]){
327 /* float */
328 printf("\t%8.2f" , *(float*)select_dp->V[i]);
329 }else{
330 /* char */
331 printf("\t%.*s" , select_dp->L[i] , select_dp->V[i]);
332 }
333 }
334 }
335 printf("\n");
336 }
337 }
