Debian Codegraph @

src/interfaces/ecpg/test/sql/declare.pgc

raw
1 #include <locale.h>
2 #include <string.h>
3 #include <stdlib.h>
4
5 EXEC SQL WHENEVER SQLERROR SQLPRINT;
6
7 EXEC SQL INCLUDE sqlca;
8 EXEC SQL INCLUDE ../regression;
9
10 #define ARRAY_SIZE 2
11
12 void execute_test(void);
13 void commitTable(void);
14 void reset(void);
15 void printResult(char *tc_name, int loop);
16
17 EXEC SQL BEGIN DECLARE SECTION;
18 int f1[ARRAY_SIZE];
19 int f2[ARRAY_SIZE];
20 char f3[ARRAY_SIZE][20];
21 EXEC SQL END DECLARE SECTION;
22
23 int main(void)
24 {
25 setlocale(LC_ALL, "C");
26
27 ECPGdebug(1, stderr);
28
29 EXEC SQL CONNECT TO REGRESSDB1 AS con1;
30 EXEC SQL CONNECT TO REGRESSDB2 AS con2;
31
32 EXEC SQL AT con1 CREATE TABLE source(f1 integer, f2 integer, f3 varchar(20));
33 EXEC SQL AT con2 CREATE TABLE source(f1 integer, f2 integer, f3 varchar(20));
34
35 EXEC SQL AT con1 INSERT INTO source VALUES(1, 10, 'db on con1');
36 EXEC SQL AT con1 INSERT INTO source VALUES(2, 20, 'db on con1');
37
38 EXEC SQL AT con2 INSERT INTO source VALUES(1, 10, 'db on con2');
39 EXEC SQL AT con2 INSERT INTO source VALUES(2, 20, 'db on con2');
40
41 commitTable();
42
43 execute_test();
44
45 EXEC SQL AT con1 DROP TABLE IF EXISTS source;
46 EXEC SQL AT con2 DROP TABLE IF EXISTS source;
47
48 commitTable();
49
50 EXEC SQL DISCONNECT ALL;
51
52 return 0;
53 }
54
55 /*
56 * default connection: con2
57 * Non-default connection: con1
58 *
59 */
60 void execute_test(void)
61 {
62 EXEC SQL BEGIN DECLARE SECTION;
63 int i, count, length;
64 char *selectString = "SELECT f1,f2,f3 FROM source";
65 EXEC SQL END DECLARE SECTION;
66
67 /*
68 * testcase1. using DECLARE STATEMENT without using AT clause,
69 * using PREPARE and CURSOR statement without using AT clause
70 */
71 reset();
72
73 EXEC SQL DECLARE stmt_1 STATEMENT;
74 EXEC SQL PREPARE stmt_1 FROM :selectString;
75 EXEC SQL DECLARE cur_1 CURSOR FOR stmt_1;
76 EXEC SQL OPEN cur_1;
77
78 EXEC SQL WHENEVER NOT FOUND DO BREAK;
79 i = 0;
80 while (1)
81 {
82 EXEC SQL FETCH cur_1 INTO :f1[i], :f2[i], :f3[i];
83 i++;
84 }
85 EXEC SQL CLOSE cur_1;
86 EXEC SQL DEALLOCATE PREPARE stmt_1;
87 EXEC SQL WHENEVER NOT FOUND CONTINUE;
88
89 printResult("testcase1", 2);
90
91
92 /*
93 * testcase2. using DECLARE STATEMENT at con1,
94 * using PREPARE and CURSOR statement without using AT clause
95 */
96 reset();
97
98 EXEC SQL AT con1 DECLARE stmt_2 STATEMENT;
99 EXEC SQL PREPARE stmt_2 FROM :selectString;
100 EXEC SQL DECLARE cur_2 CURSOR FOR stmt_2;
101 EXEC SQL OPEN cur_2;
102
103 EXEC SQL WHENEVER NOT FOUND DO BREAK;
104 i = 0;
105 while (1)
106 {
107 EXEC SQL FETCH cur_2 INTO :f1[i], :f2[i], :f3[i];
108 i++;
109 }
110 EXEC SQL CLOSE cur_2;
111 EXEC SQL DEALLOCATE PREPARE stmt_2;
112 EXEC SQL WHENEVER NOT FOUND CONTINUE;
113
114 printResult("testcase2", 2);
115
116 /*
117 * testcase3. using DECLARE STATEMENT without using AT clause,
118 * using PREPARE and EXECUTE statement without using AT clause
119 */
120 reset();
121
122 EXEC SQL DECLARE stmt_3 STATEMENT;
123 EXEC SQL PREPARE stmt_3 FROM :selectString;
124 EXEC SQL EXECUTE stmt_3 INTO :f1, :f2, :f3;
125
126 EXEC SQL DEALLOCATE PREPARE stmt_3;
127
128 printResult("testcase3", 2);
129
130 /*
131 * testcase4. using DECLARE STATEMENT without using AT clause,
132 * using PREPARE and CURSOR statement at con2
133 */
134 reset();
135
136 EXEC SQL DECLARE stmt_4 STATEMENT;
137 EXEC SQL AT con2 PREPARE stmt_4 FROM :selectString;
138 EXEC SQL AT con2 DECLARE cur_4 CURSOR FOR stmt_4;
139 EXEC SQL AT con2 OPEN cur_4;
140
141 EXEC SQL WHENEVER NOT FOUND DO BREAK;
142 i = 0;
143 while (1)
144 {
145 EXEC SQL AT con2 FETCH cur_4 INTO :f1[i], :f2[i], :f3[i];
146 i++;
147 }
148 EXEC SQL AT con2 CLOSE cur_4;
149 EXEC SQL AT con2 DEALLOCATE PREPARE stmt_4;
150 EXEC SQL WHENEVER NOT FOUND CONTINUE;
151
152 printResult("testcase4", 2);
153
154 /*
155 * DESCRIBE statement is also supported.
156 */
157 EXEC SQL AT con1 DECLARE stmt_desc STATEMENT;
158 EXEC SQL PREPARE stmt_desc FROM :selectString;
159 EXEC SQL DECLARE cur_desc CURSOR FOR stmt_desc;
160 EXEC SQL OPEN cur_desc;
161
162 /* descriptor can be used for describe statement */
163 EXEC SQL AT con1 ALLOCATE DESCRIPTOR desc_for_describe;
164 EXEC SQL DESCRIBE stmt_desc INTO SQL DESCRIPTOR desc_for_describe;
165
166 EXEC SQL AT con1 GET DESCRIPTOR desc_for_describe :count = COUNT;
167 EXEC SQL AT con1 GET DESCRIPTOR desc_for_describe VALUE 3 :length = LENGTH;
168
169 EXEC SQL AT con1 DEALLOCATE DESCRIPTOR desc_for_describe;
170
171 /* for fetch statement */
172 EXEC SQL AT con1 ALLOCATE DESCRIPTOR desc_for_fetch;
173 EXEC SQL FETCH cur_desc INTO SQL DESCRIPTOR desc_for_fetch;
174
175 EXEC SQL AT con1 GET DESCRIPTOR desc_for_fetch VALUE 3 :f3[0] = DATA;
176
177 EXEC SQL AT con1 DEALLOCATE DESCRIPTOR desc_for_fetch;
178 EXEC SQL CLOSE cur_desc;
179 EXEC SQL DEALLOCATE stmt_desc;
180
181 printf("****descriptor results****\n");
182 printf("count: %d, length: %d, data: %s\n", count, length, f3[0]);
183 }
184
185 void commitTable()
186 {
187 EXEC SQL AT con1 COMMIT;
188 EXEC SQL AT con2 COMMIT;
189 }
190
191 /*
192 * reset all the output variables
193 */
194 void reset()
195 {
196 memset(f1, 0, sizeof(f1));
197 memset(f2, 0, sizeof(f2));
198 memset(f3, 0, sizeof(f3));
199 }
200
201 void printResult(char *tc_name, int loop)
202 {
203 int i;
204
205 if (tc_name)
206 printf("****%s test results:****\n", tc_name);
207
208 for (i = 0; i < loop; i++)
209 printf("f1=%d, f2=%d, f3=%s\n", f1[i], f2[i], f3[i]);
210
211 printf("\n");
212 }