1 /*
2 FileName : dyn4.pc
3 Function : sample program of dynamic SQL method4
4 Usage : dyn4 [-uUSERNAME -pPASSWORD] (default: scott/tiger)
5 */
6 #include <stdio.h>
7 #include <string.h>
8 #include <setjmp.h>
9 #include <ctype.h>
10 #include <stdlib.h>
11 #include <sqlca.h>
12 #include <sqlda.h>
13 #include <sqlcpr.h>
14 #include <ocidfn.h>
15
16 #define MAX_ITEMS 40 /* Max number of select-list items/bind variables. */
17 #define MAX_VNAME 30 /* Max lengths of the _names_ of select-list items */
18 #define MAX_INAME 30 /* Max lengths of the _names_ of indicator variables */
19 #define MAX_USERNM 32
20 #define MAX_STMT 1024
21 #define MAX_LBUF 256
22 #define MAX_FMT1 12
23 #define ERR_CONN 1
24 #define ERR_ALLOC 2
25 #define ERR_OVR_MAX_ITEMS 10
26 #define DEF_PRECISION 10
27 #define DEF_USERNAME "scott"
28 #define DEF_PASSNAME "tiger"
29
30 void sql_error(char *);
31 void set_bind_variables(void);
32 void help(void);
33 int connect(void);
34 int alloc_descriptors(int, int, int);
35 int get_dyn_statement(void);
36 int do_select(void);
37
38 #define SZ_STTSTR 4 /* EXIT/QUIT/HELP */
39 char *stt_command[] = {"EXIT","QUIT","HELP"};
40 #define SZ_STTCMDS (sizeof(stt_command)/sizeof(char *))
41
42 #define SZ_CMDSTR 6 /* SELECT/DELETE/INSERT/UPDATE */
43 char *dml_command[] = {"SELECT", "INSERT", "UPDATE", "DELETE"};
44 #define SZ_CMDS (sizeof(dml_command)/sizeof(char *))
45 #define CMD_SELECT 0
46
47 EXEC SQL BEGIN DECLARE SECTION;
48 char dyn_statement[MAX_STMT],username[MAX_USERNM],password[MAX_USERNM];
49 EXEC SQL END DECLARE SECTION;
50
51 int parse_flag = 0; /* flag to parse for the error routine. */
52 SQLDA *bind_dp,*select_dp;
53
54 int main(int argc,char *argv[]) {
55 int i,ret,cmd;
56
57 get_opt(argc,argv);
58 if (connect() != 0) die_err(ERR_CONN);
59
60 /* Allocate memory for the select and bind descriptors. */
61 if (alloc_descriptors(MAX_ITEMS, MAX_VNAME, MAX_INAME) != 0)
62 die_err(ERR_ALLOC);
63
64 /* Process SQL statements. */
65 for (;;) {
66 ret = -1;
67 if (get_dyn_statement() != 0) break; /* Get the statement */
68 cmd = chk_command(dyn_statement,SZ_CMDSTR,dml_command,SZ_CMDS);
69
70 /* Prepare the statement and declare a cursor. */
71 EXEC SQL WHENEVER SQLERROR GOTO err_loop;
72
73 parse_flag = 1; /* Set a flag for sql_error(). */
74 EXEC SQL PREPARE S FROM :dyn_statement;
75 parse_flag = 0; /* Unset the flag. */
76
77 get_sql_stmt();
78 EXEC SQL DECLARE C CURSOR FOR S;
79
80 /* Set the bind variables for any placeholders in the SQL statement. */
81 set_bind_variables();
82
83 /* Open the cursor and execute the statement. */
84 EXEC SQL OPEN C USING DESCRIPTOR bind_dp;
85
86 /* Call the function that processes the select-list. */
87 if (cmd == CMD_SELECT) ret = do_select();
88
89 /* Tell user how many rows processed. */
90 if (ret == 0) {
91 printf("\n%d row%c processed.\n", sqlca.sqlerrd[2],
92 sqlca.sqlerrd[2] > 1 ? 's' : '\0');
93 }
94 EXEC SQL CLOSE C;
95 continue;
96 err_loop:
97 sql_error("main :");
98 }
99
100 /* free the memory allocated for the bind and select descriptors. */
101 for (i = 0; i < MAX_ITEMS; i++) {
102 if (bind_dp->I[i] != (short *) 0) free(bind_dp->I[i]);
103 if (select_dp->I[i] != (short *) 0) free(select_dp->I[i]);
104 if (bind_dp->V[i] != (char *) 0) free(bind_dp->V[i]);
105 if (select_dp->V[i] != (char *) 0) free(select_dp->V[i]);
106 }
107
108 /* Free space used by the descriptors themselves. */
109 sqlclu(bind_dp);
110 sqlclu(select_dp);
111
112 EXEC SQL WHENEVER SQLERROR CONTINUE;
113
114 EXEC SQL COMMIT WORK RELEASE;
115 exit(0);
116 }
117
118 /*-------------------------------------------------------------------
119 Function : connect to Oracle
120 -------------------------------------------------------------------*/
121 int connect() {
122 static char *suc_msg = "\nConnected to ORACLE as user %s.\n";
123
124 EXEC SQL WHENEVER SQLERROR GOTO con_err;
125
126 EXEC SQL CONNECT :username IDENTIFIED BY :password;
127 printf(suc_msg, username);fflush(stdout);
128 return 0;
129
130 con_err:
131 sql_error("connect : ");
132 return -1;
133 }
134
135 /*---------------------------------------------------------------------------
136 Function : Allocate the BIND and SELECT descriptors using SQLSQLDAAlloc().
137 Return : 0 = success
138 -1 = error
139 SQLSQLDAAlloc(SQL_SINGLE_RCTX,max_list,max_listname,max_ind_name);
140 ---------------------------------------------------------------------------*/
141 alloc_descriptors(int size, int max_vname,int max_iname) {
142 int i;
143
144 if ((bind_dp = SQLSQLDAAlloc(
145 SQL_SINGLE_RCTX,size,max_vname,max_iname)) == (SQLDA *) 0) {
146 fprintf(stderr, "Cannot allocate memory for bind descriptor.");
147 return -1;
148 }
149
150 if ((select_dp = SQLSQLDAAlloc(
151 SQL_SINGLE_RCTX,size, max_vname, max_iname)) == (SQLDA *) 0) {
152 fprintf(stderr, "Cannot allocate memory for select descriptor.");
153 return -1;
154 }
155
156 /* Allocate the pointers to the indicator variables, and the actual data. */
157 for (i = 0; i < MAX_ITEMS; i++) {
158 bind_dp ->I[i] = (short *) malloc(sizeof(short));
159 select_dp->I[i] = (short *) malloc(sizeof(short));
160 }
161 return 0;
162 }
163
164 /*-----------------------------------------------------------------------
165 Function : input SQL statement
166 Return : 0 : success
167 -1 : exit
168 -----------------------------------------------------------------------*/
169 int get_dyn_statement() {
170 char *cp, linebuf[MAX_LBUF];
171 int i,iter, plsql,cmd,len;
172
173 for (plsql = 0, iter = 1; ;) {
174 if (iter == 1) {
175 printf("\nSQL> ");fflush(stdout);
176 dyn_statement[0] = '\0';
177 }
178
179 fgets(linebuf, MAX_LBUF, stdin);
180 len = strlen(linebuf);
181 cp = strrchr(linebuf, '\n');
182 if (cp) {
183 if (cp != linebuf) *cp = ' ';
184 else continue;
185 }
186 for (i=0; i<len; i++) linebuf[i] = toupper(linebuf[i]);
187
188 cmd = chk_command(linebuf,SZ_STTSTR,stt_command,SZ_STTCMDS);
189 if ((cmd == 0)||(cmd == 1)) return -1;
190 else if (cmd == 2) { /* help */
191 help();
192 iter = 1;
193 continue;
194 }
195
196 if (strstr(linebuf, "BEGIN")) {
197 plsql = 1;
198 }
199
200 strcat(dyn_statement, linebuf);
201
202 if ((plsql && (cp = strrchr(dyn_statement, '/'))) ||
203 (!plsql && (cp = strrchr(dyn_statement, ';')))) {
204 *cp = '\0';
205 break;
206 }
207 else {
208 iter++;
209 printf("%3d ", iter);
210 }
211 }
212 return 0;
213 }
214
215 /*---------------------------------------------------------------------
216 Function : set bind variables (SQLDA bind_dp)
217 SQL : select * from emp where deptno = :dd and job = :jj;
218 SQLDA :
219 struct SQLDA {
220 int N; \ プレースホルダ配列の最大値
221 char *V[]; @& バインド変数の値を格納するアドレスの配列
222 int L[]; @ V のバッファ長の配列
223 short T[]; @ V の型の配列 (SQLT_CHR,SQLT_NUM,SQLT_INT ..)
224 NOT NULL 制約がないと上位ビットが ON となっている
225 short I[]; @& 標識変数の値を格納するアドレスの配列
226 int F; # DESCRIBE でのプレースホルダ配列の数（実際の数）
227 char *S[]; # プレースホルダ名を格納するアドレスの配列
228 short M[]; S のバッファ長の配列
229 short C[]; # S に実際格納されたバイト数の配列
230 char X[]; 標識変数名を格納するアドレス配列
231 short Y[]; X のバッファ長の配列
232 short Z[]; X に実際格納されたバイト数の配列
233 };
234
235 \ : set user before EXEC SQL
236 # : set (EXEC SQL DESCRIBE BIND VARIABLES FOR S INTO bind_dp)
237 @ : set user
238 ---------------------------------------------------------------------*/
239 void set_bind_variables() {
240 int i, n;
241 char bind_var[64],*cp;
242
243 /* Describe any bind variables (input host variables) */
244 EXEC SQL WHENEVER SQLERROR GOTO err_set;
245
246 bind_dp->N = MAX_ITEMS; /* Initialize count of array elements. */
247 EXEC SQL DESCRIBE BIND VARIABLES FOR S INTO bind_dp;
248
249 /* If F is negative, there were more bind variables
250 than originally allocated by sqlald(). */
251 if (bind_dp->F < 0) {
252 fprintf (stderr,"\nToo many bind variables (%d), maximum is %d\n.",
253 -bind_dp->F, MAX_ITEMS);
254 return;
255 }
256
257 /* Set the maximum number of array elements in the
258 descriptor to the number found. */
259 bind_dp->N = bind_dp->F;
260
261 /*
262 EXEC SQL DESCRIBE BIND 前に設定しておく部分
263 N プレースホルダ配列の最大値
264
265 EXEC SQL DESCRIBE BIND VARIABLES FOR S INTO bind_dp で設定される部分
266 F DESCRIBE でのプレースホルダ配列の数（実際の数）
267 S[i] SQL 文のプレースホルダ名を格納するアドレス
268 C[i] S[i] に実際格納されたバイト数の配列
269
270 EXEC SQL OPEN C USING DESCRIPTOR bind_dp 前に設定しておく部分
271 V[i] バインド変数の値を格納するアドレス
272 L[i] V[i] のバッファ長
273 T[i] V の型の配列 (SQLT_CHR,SQLT_NUM,SQLT_INT ..)
274 I[i] 標識変数の値を格納するアドレス
275 */
276 for (i = 0; i < bind_dp->F; i++) {
277 printf ("\nEnter value for bind variable %.*s: ",
278 (int)bind_dp->C[i], bind_dp->S[i]);
279 fgets(bind_var, sizeof bind_var, stdin);
280
281 /* Set it in the descriptor. (remove the new line character) */
282 bind_dp->L[i] = n = strlen(bind_var) - 1;
283
284 /* allocate the buffer for the value. */
285 bind_dp->V[i] = cp = (char *) malloc((n + 1));
286 if (! cp) die_err(ERR_ALLOC);
287 strcpy(bind_dp->V[i], bind_var); /* set value */
288
289 /* Set the indicator variable's value. */
290 if ((strncmp(bind_dp->V[i], "NULL", 4) == 0) ||
291 (strncmp(bind_dp->V[i], "null", 4) == 0))
292 *bind_dp->I[i] = -1;
293 else
294 *bind_dp->I[i] = 0;
295
296 bind_dp->T[i] = 1; /* Set the bind datatype to 1 for CHAR. */
297 }
298 return;
299 err_set:
300 sql_error("set_bind_variables : ");
301 return;
302 }
303
304 /*----------------------------------------------------------------
305 Function : process to SELECT_statement
306 1. get table_list [DESCRIBE]
307 2. print result_set
308 2.1 allocate memory
309 2.2 print column headings
310 2.3 make format of row datas.
311 2.4 print minus-bars of each column
312 2.5 print each row datas
313 Return : 0 = success
314 others = error
315 SQLDA:
316 struct SQLDA {
317 int N; \ 選択項目配列の最大値
318 char *V[]; @& 選択項目の値を格納するアドレスの配列
319 int L[]; @ V のバッファ長の配列
320 short T[]; @ V の型の配列 (SQLT_CHR,SQLT_NUM,SQLT_INT ..)
321 NOT NULL 制約がないと上位ビットが ON となっている
322 short I[]; @& 標識変数の値を格納するアドレスの配列
323 int F; # DESCRIBE での選択項目配列の数（実際の数）
324 char *S[]; # 選択項目名を格納するアドレスの配列
325 short M[]; S のバッファ長の配列
326 short C[]; # S に実際格納されたバイト数の配列
327 char X[]; 標識変数名を格納するアドレス配列
328 short Y[]; X のバッファ長の配列
329 short Z[]; X に実際格納されたバイト数の配列
330 };
331
332 \ : set user before EXEC SQL DESCRIBE
333 # : set (EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp)
334 @ : set user
335 & : set (EXEC SQL FETCH C USING DESCRIPTOR select_dp)
336 ----------------------------------------------------------------*/
337 int do_select() {
338 int i,j, null_ok, precision, scale, bsize,pre[MAX_ITEMS],len;
339 char fmt[MAX_ITEMS][MAX_FMT1];
340
341 /* 1. describe the select-list items. */
342 EXEC SQL WHENEVER SQLERROR GOTO err_sel;
343 select_dp->N = MAX_ITEMS;
344 EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;
345
346 /* F に実際の項目数が設定されます。
347 F が負の場合は選択項目数が MAX_ITEMS より多かった場合です */
348 if (select_dp->F < 0) {
349 fprintf (stderr,"\nToo many select-list items (%d), maximum is %d\n",
350 -(select_dp->F), MAX_ITEMS);
351 return ERR_OVR_MAX_ITEMS;
352 }
353 select_dp->N = select_dp->F;
354
355 /*
356 SQLNumberPrecV6() : 精度とスケールを取得
357 SQLColumnNullCheck() : T[i] の NULL/NOT NULL のビットを off
358
359 CHAR The length is defined at CREATE time.
360 NUMBER NUMBER(n,m) n:精度 m:スケール
361 スケールが 0 なら int そうでなかったら float
362 DATE 本来のバイト数は 7 なので多めにして 9
363 ROWID 18
364 LONG & LONG RAW 最大値として 240
365 */
366 /* 2. print result_set */
367 printf("\n");
368 for (i = 0; i < select_dp->F; i++) {
369 char title[MAX_VNAME],*cp;
370
371 fmt[i][0] = '\0';
372
373 /* T[i] の NULL/NOT NULL のビットを off にしてから使う */
374 SQLColumnNullCheck(SQL_SINGLE_RCTX,(unsigned short *)&(select_dp->T[i]),
375 (unsigned short *)&(select_dp->T[i]), &null_ok);
376 switch (select_dp->T[i]) {
377 case SQLT_NUM :
378 /* NUMBER datatype: SQLNumberPrecV6() で精度とスケールを取得 */
379 SQLNumberPrecV6 (SQL_SINGLE_RCTX,
380 (unsigned int *)&(select_dp->L[i]), &precision, &scale);
381 if (precision == 0) precision = DEF_PRECISION;
382 /* NUMBER datatype to FLOAT if scale > 0, INT otherwise. */
383 if (scale > 0) select_dp->L[i] = sizeof(float);
384 else select_dp->L[i] = sizeof(int);
385 pre[i] = precision;
386 break;
387
388 case SQLT_RID : /* ROWID datatype */
389 select_dp->L[i] = 18;
390 break;
391
392 case SQLT_DAT : /* DATE datatype */
393 select_dp->L[i] = 10; /* yyyy/mm/dd */
394 break;
395
396 case SQLT_BIN : /* RAW datatype */
397 case SQLT_CHR : /* CHAR datatype */
398 break;
399
400 case SQLT_LNG : /* LONG datatype */
401 case SQLT_LBI : /* LONG RAW datatype */
402 select_dp->L[i] = 240;
403 break;
404 }
405
406 /* 2.1 allocate memory */
407 if (select_dp->T[i] != SQLT_NUM) bsize = select_dp->L[i] + 1;
408 else bsize = select_dp->L[i];
409 select_dp->V[i] = cp = (char *) malloc(bsize);
410 if (! cp) die_err(ERR_ALLOC);
411
412 /* 2.2 print column headings */
413 strncpy(title, select_dp->S[i], select_dp->C[i]);
414 title[select_dp->C[i]] = '\0';
415 if (select_dp->T[i] == SQLT_NUM) len = pre[i];
416 else len = select_dp->L[i];
417 printf ("%-*.*s ", len,len, title);
418
419 /* LONG RAW と NUMBER 以外は char に設定 */
420 if (select_dp->T[i] != SQLT_LBI && select_dp->T[i] != SQLT_NUM)
421 select_dp->T[i] = SQLT_CHR;
422
423 /* NUMBER でスケールが正の場合は float,それ以外は int に設定 */
424 if (select_dp->T[i] == SQLT_NUM) {
425 if (scale > 0)
426 select_dp->T[i] = SQLT_FLT; /* float */
427 else
428 select_dp->T[i] = SQLT_INT; /* int */
429 }
430
431 /* 2.3 make format of row datas. */
432 switch (select_dp->T[i]) {
433 case SQLT_FLT : /* %X.Xf */
434 snprintf(fmt[i],MAX_FMT1,"%%%d.%df ",precision,scale);
435 break;
436 case SQLT_INT : /* %Xd */
437 snprintf(fmt[i],MAX_FMT1,"%%%dd ",precision);
438 break;
439 default: /* %-X.Xs */
440 snprintf(fmt[i],MAX_FMT1,"%%-%d.%ds ",
441 (int)select_dp->L[i],(int)select_dp->L[i]);
442 break;
443 }
444 }
445 printf ("\n");
446
447 /* 2.4 print minus-bars of each column */
448 for (i = 0; i < select_dp->F; i++) {
449 if ((select_dp->T[i] == SQLT_FLT) || (select_dp->T[i] == SQLT_INT))
450 len = pre[i];
451 else len = select_dp->L[i];
452 for (j=0; j<len; j++) printf ("%c",'-');
453 printf (" ");
454 }
455 printf ("\n");
456 fflush(stdout);
457
458 /* 2.5 print each row datas */
459 EXEC SQL WHENEVER NOT FOUND GOTO end_select_loop;
460
461 for (;;) {
462 EXEC SQL FETCH C USING DESCRIPTOR select_dp;
463
464 /* 標識変数の値が負の場合、値は予測不能 */
465 for (i = 0; i < select_dp->F; i++) {
466 if (*select_dp->I[i] < 0) {
467 if ((select_dp->T[i] == SQLT_FLT)||
468 (select_dp->T[i] == SQLT_INT)) len = pre[i];
469 else len = select_dp->L[i];
470 printf ("%-*c ",len, ' ');
471 }
472 else {
473 switch (select_dp->T[i]) {
474 case SQLT_FLT :
475 printf(fmt[i],*(float *)select_dp->V[i]);
476 break;
477 case SQLT_INT :
478 printf(fmt[i],*(int *)select_dp->V[i]);
479 break;
480 default:
481 printf(fmt[i],select_dp->V[i]);
482 break;
483 }
484 }
485 }
486 printf ("\n");
487 fflush(stdout);
488 }
489 end_select_loop:
490 return 0;
491 err_sel:
492 sql_error("do_select : ");
493 return -1;
494 }
495
496 /*----------------------------------------------------------------------
497 Function : check oracle command or not
498 Param : buf = SQL statement
499 len = length of candidate string
500 cand = candidate array
501 cand_no = no. of cand
502 Return : >=0 : oracle exec command
503 <0 : not
504 ----------------------------------------------------------------------*/
505 int chk_command(char *buf,int len,char *cand[],int cand_no) {
506 int i,ret=-1,buflen;
507 char cmdstr[MAX_LBUF];
508
509 buflen = strlen(buf);
510 if (buflen > len) {
511 strncpy(cmdstr,buf,len);
512 cmdstr[len] = '\0';
513 for (i=0; i<cand_no; i++) {
514 if (! strcmp(cmdstr,cand[i])) {
515 ret = i;
516 break;
517 }
518 }
519 }
520 return ret;
521 }
522
523 int get_sql_stmt() {
524 char sbuf[MAX_STMT];
525 size_t size=MAX_STMT,sqlcode;
526
527 sqlgls(sbuf,&size,&sqlcode);
528 sbuf[size] = '\0';
529 printf("SQL[%d]=%s\n",sqlcode,sbuf);
530 }
531
532 static char *help_msg[]={
533 "Enter a SQL statement or a PL/SQL block at the SQL> prompt.",
534 "Statements can be continued over several lines, except within string literals.",
535 "Terminate a SQL statement with a semicolon.",
536 "Terminate a PL/SQL block (which can contain embedded semicolons) with a slash (/).",
537 "Typing \"exit\" (no semicolon needed) exits the program.",
538 "You typed \"?\" or \"help\" to get this message.\n",
539 };
540 #define SZ_HELP_MSG (sizeof(help_msg)/sizeof(char *))
541
542 void help() {
543 int i;
544
545 for (i=0; i<SZ_HELP_MSG; i++) fprintf(stderr,"%s\n",help_msg[i]);
546 }
547
548 /*----------------------------------------------------------------------
549 Function : error handler of oracle
550 ----------------------------------------------------------------------*/
551 void sql_error(char *msg) {
552 int len=MAX_LBUF,size;
553 char msgbuf[MAX_LBUF];
554
555 fprintf(stderr,"\n\n",msg);
556 SQLErrorGetText(SQL_SINGLE_RCTX,msgbuf,&len,&size);
557 fprintf(stderr,"\n%s\n",msgbuf);
558 if (parse_flag)
559 fprintf(stderr,"Parse error at character offset %d in SQL statement.\n",
560 sqlca.sqlerrd[4]);
561
562 EXEC SQL WHENEVER SQLERROR CONTINUE;
563 EXEC SQL ROLLBACK WORK;
564 }
565
566
567 extern int optind;
568 extern char *optarg;
569 char *opt = "u:p:"; /* input options */
570 /*------------------------------------------------------------------
571 Function : analyze input options USERNAME/PASSWORD
572 if set nothing, set scott/tiger for default.
573 ------------------------------------------------------------------*/
574 int get_opt(int argc,char *argv[]) {
575 int c;
576
577 /* analize input options */
578 memset(username,0,MAX_USERNM);
579 memset(password,0,MAX_USERNM);
580 while((c=getopt(argc,argv,opt)) != EOF) {
581 switch(c) {
582 case 'u': /* username */
583 strcpy(username,optarg);
584 break;
585
586 case 'p': /* password */
587 strcpy(password,optarg);
588 break;
589 }
590 }
591 if (argc != 3) {
592 strcpy(username,DEF_USERNAME);
593 strcpy(password,DEF_PASSNAME);
594 }
595 }
596
597 int die_err(int no) {
598 fprintf(stderr,"Error code is %d\n",no);
599 exit(no);
600 }
[ 実行結果 ]
Connected to ORACLE as user scott.
SQL> select * from emp where deptno =:dd;
SQL[4]=SELECT * FROM EMP WHERE DEPTNO =:DD
Enter value for bind variable DD: 20
EMPN ENAME JOB MGR HIREDATE SAL COMM DE
---- ---------- --------- ---- ---------- ------- ------- --
7369 SMITH CLERK 7902 1980/12/17 800.00 20
7566 JONES MANAGER 7839 1981/04/02 2975.00 20
7788 SCOTT ANALYST 7566 1982/12/09 3000.00 20
7876 ADAMS CLERK 7788 1983/01/12 1100.00 20
4 rows processed.
SQL> exit