1 /*---------------------------------------------------------
2 FileName : dyn123.pc
3 Function : test programe for dynamic SQL method 1/2/3
4 ---------------------------------------------------------*/
5 #include <stdio.h>
6 #include <string.h>
7 #include <stdlib.h>
8 #include <sqlca.h>
9 #include <oraca.h>
10
11 #define SZ_MSG 512
12 #define SZ_NAME 20
13 #define ORACLE_EOF 1403
14 #define USER_PASS "SCOTT/TIGER"
15
16 EXEC ORACLE OPTION (RELEASE_CURSOR=YES);
17
18 EXEC SQL BEGIN DECLARE SECTION;
19 int deptno=20;
20 char uid[SZ_NAME];
21 char dynstmt[SZ_MSG],ename[SZ_NAME];
22 EXEC SQL END DECLARE SECTION;
23
24
25 char *title ="\nSQL ==> ";
26
27 /*---------------------------------------------------------------------
28 Function : This is the Oracle error handler.
29 Print diagnostic text containing the error message,
30 current SQL statement, and location of error.
31 ---------------------------------------------------------------------*/
32 void sql_error() {
33 printf("\n%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
34 printf("in \"%.*s...\"\n",
35 oraca.orastxt.orastxtl, oraca.orastxt.orastxtc);
36 printf("on line %d of %.*s.\n\n",
37 oraca.oraslnr, oraca.orasfnm.orasfnml,oraca.orasfnm.orasfnmc);
38
39 /* Disable Oracle error checking to avoid an infinite loop
40 * should another error occur within this routine as a result of the rollback.
41 */
42 EXEC SQL WHENEVER SQLERROR CONTINUE;
43
44 /* Roll back any pending changes and disconnect from Oracle. */
45 EXEC SQL ROLLBACK RELEASE;
46 exit(EXIT_FAILURE);
47 }
48
49 int main(int argc,char *argv[]) {
50 oraca.orastxtf = ORASTFERR; /* Save text of current SQL statement in
51 the ORACA if an error occurs. */
52 connect();
53 do_insert(); /* insert */
54 do_select(); /* select */
55 do_delete(); /* delete */
56 do_select(); /* select */
57
58 /* Close to transaction */
59 EXEC SQL COMMIT WORK RELEASE;
60 exit(EXIT_SUCCESS);
61 }
62
63 /*---------------------------------------------------------
64 Function : connect to oacle database
65 ---------------------------------------------------------*/
66 int connect() {
67
68 strcpy(uid, USER_PASS);
69
70 EXEC SQL WHENEVER SQLERROR DO sql_error("ERROR in connect :");
71
72 EXEC SQL CONNECT :uid ;
73 if (!sqlca.sqlcode) printf("\tConnected to ORACLE\n");
74 }
75
76 /*---------------------------------------------------------
77 Function : execute dynamic statement method1
78 EXEC SQL EXECUTE IMMEDIATE stmt_name;
79 ---------------------------------------------------------*/
80 int do_insert() {
81 static char *stmt[]= {
82 "alter session set NLS_DATE_FORMAT='yy/mm/dd hh24:mi:ss'",
83 "INSERT INTO EMP values (%s)",
84 "commit",
85 };
86 static char *data = "9999,'abe','CLERK',7902,'2004/05/21',800, NULL, 20";
87
88 int i=0;
89
90 EXEC SQL WHENEVER SQLERROR DO sql_error("ERROR in do_insert :");
91 printf("%s",title);
92 strcpy(dynstmt,stmt[i++]);
93 EXEC SQL EXECUTE IMMEDIATE :dynstmt;
94
95 snprintf(dynstmt,SZ_MSG,stmt[i++],data);
96 puts(dynstmt);
97
98 EXEC SQL EXECUTE IMMEDIATE :dynstmt;
99
100 strcpy(dynstmt,stmt[i++]);
101 EXEC SQL EXECUTE IMMEDIATE :dynstmt;
102 if (!sqlca.sqlcode) printf(" Success to insert\n");
103 }
104
105 /*---------------------------------------------------------
106 Function : execute dynamic statement method2
107 EXEC SQL PREPARE stmt_name FROM :host_string
108 EXEC SQL EXECUTE stmt_name USING :host_string
109 ---------------------------------------------------------*/
110 int do_delete() {
111 static char *stmt= "DELETE FROM EMP WHERE ENAME= :v1 ";
112
113 EXEC SQL WHENEVER SQLERROR DO sql_error("ERROR in do_delete :");
114 strcpy(dynstmt,stmt);
115 printf("%s",title);
116 puts(dynstmt);
117
118 printf("\tDelete record. Who ? ");
119 scanf("%s",ename);
120
121 EXEC SQL PREPARE S FROM :dynstmt;
122 EXEC SQL EXECUTE S USING :ename;
123
124 if (!sqlca.sqlcode) printf(" Success to delete %s\n",ename);
125 }
126
127 /*---------------------------------------------------------
128 Function : execute dynamic statement method3
129 EXEC SQL PREPARE stmt_name FROM :host_string;
130 EXEC SQL DECLARE cursur_name CURSOR FOR stmt_name;
131 EXEC SQL OPEN cursur_name USING :host_var;
132 EXEC SQL FETCH cursur_name INTO :host_var;
133 EXEC SQL CLOSE cursur_name;
134 ---------------------------------------------------------*/
135 int do_select() {
136 static char *stmt = "SELECT ename FROM emp WHERE deptno = :v1";
137
138 EXEC SQL WHENEVER SQLERROR DO sql_error("ERROR in do_select :");
139 strcpy(dynstmt,stmt);
140 printf("%s",title);
141 puts(dynstmt);
142
143 EXEC SQL PREPARE stmtID FROM :dynstmt;
144 EXEC SQL DECLARE cursor CURSOR FOR stmtID;
145 EXEC SQL OPEN cursor USING :deptno;
146
147 printf("\n\tList of DeptNo %d \n",deptno);
148 for (;;) {
149 EXEC SQL FETCH cursor INTO :ename;
150 if (sqlca.sqlcode == ORACLE_EOF) break;
151 if (sqlca.sqlcode != 0) break;
152 printf("\t%s\n",ename);
153 }
154 EXEC SQL CLOSE cursor;
155 if (!sqlca.sqlcode) printf(" Success to select emp\n");
156 }
157
[ ¼Â¹Ô·ë²Ì ]
Connected to ORACLE
SQL ==> INSERT INTO EMP values (9999,'abe','CLERK',7902,'2004/05/21',800, NULL, 20)
Success to insert
SQL ==> SELECT ename FROM emp WHERE deptno = :v1
List of DeptNo 20
SMITH
JONES
SCOTT
ADAMS
abe
Success to select emp
SQL ==> DELETE FROM EMP WHERE ENAME= :v1
Delete record. Who ? Success to delete abe
SQL ==> SELECT ename FROM emp WHERE deptno = :v1
List of DeptNo 20
SMITH
JONES
SCOTT
ADAMS
Success to select emp