1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
|
#include <stdlib.h>
#include <string.h>
#include <limits.h>
#include "ecpg_config.h"
exec sql include ../regression;
exec sql include sqlda.h;
exec sql include pgtypes_numeric.h;
exec sql whenever sqlerror stop;
/* These shouldn't be under DECLARE SECTION */
sqlda_t *inp_sqlda, *outp_sqlda, *outp_sqlda1;
static void
dump_sqlda(sqlda_t *sqlda)
{
int i;
if (sqlda == NULL)
{
printf("dump_sqlda called with NULL sqlda\n");
return;
}
for (i = 0; i < sqlda->sqld; i++)
{
if (sqlda->sqlvar[i].sqlind && *(sqlda->sqlvar[i].sqlind) == -1)
printf("name sqlda descriptor: '%s' value NULL'\n", sqlda->sqlvar[i].sqlname.data);
else
switch (sqlda->sqlvar[i].sqltype)
{
case ECPGt_char:
printf("name sqlda descriptor: '%s' value '%s'\n", sqlda->sqlvar[i].sqlname.data, sqlda->sqlvar[i].sqldata);
break;
case ECPGt_int:
printf("name sqlda descriptor: '%s' value %d\n", sqlda->sqlvar[i].sqlname.data, *(int *)sqlda->sqlvar[i].sqldata);
break;
case ECPGt_long:
printf("name sqlda descriptor: '%s' value %ld\n", sqlda->sqlvar[i].sqlname.data, *(long int *)sqlda->sqlvar[i].sqldata);
break;
case ECPGt_long_long:
printf(
#ifdef _WIN32
"name sqlda descriptor: '%s' value %I64d\n",
#else
"name sqlda descriptor: '%s' value %lld\n",
#endif
sqlda->sqlvar[i].sqlname.data, *(long long int *)sqlda->sqlvar[i].sqldata);
break;
case ECPGt_double:
printf("name sqlda descriptor: '%s' value %f\n", sqlda->sqlvar[i].sqlname.data, *(double *)sqlda->sqlvar[i].sqldata);
break;
case ECPGt_numeric:
{
char *val;
val = PGTYPESnumeric_to_asc((numeric*)sqlda->sqlvar[i].sqldata, -1);
printf("name sqlda descriptor: '%s' value NUMERIC '%s'\n", sqlda->sqlvar[i].sqlname.data, val);
PGTYPESchar_free(val);
break;
}
}
}
}
int
main (void)
{
exec sql begin declare section;
char *stmt1 = "SELECT * FROM t1";
char *stmt2 = "SELECT * FROM t1 WHERE id = ?";
int rec;
int id;
exec sql end declare section;
char msg[128];
ECPGdebug(1, stderr);
strcpy(msg, "connect");
exec sql connect to REGRESSDB1 as regress1;
strcpy(msg, "set");
exec sql set datestyle to iso;
strcpy(msg, "create");
exec sql create table t1(
id integer,
t text,
d1 numeric,
d2 float8,
c char(10),
big bigint
);
strcpy(msg, "insert");
exec sql insert into t1 values
(1, 'a', 1.0, 1, 'a',1111111111111111111),
(2, null, null, null, null,null),
(3, 'c', 0.0, 3, 'c',3333333333333333333),
(4, 'd', 'NaN', 4, 'd',4444444444444444444),
(5, 'e', 0.001234, 5, 'e',5555555555555555555);
strcpy(msg, "commit");
exec sql commit;
/* SQLDA test for getting all records from a table */
outp_sqlda = NULL;
strcpy(msg, "prepare");
exec sql prepare st_id1 from :stmt1;
strcpy(msg, "declare");
exec sql declare mycur1 cursor for st_id1;
strcpy(msg, "open");
exec sql open mycur1;
exec sql whenever not found do break;
rec = 0;
while (1)
{
strcpy(msg, "fetch");
exec sql fetch 1 from mycur1 into descriptor outp_sqlda;
printf("FETCH RECORD %d\n", ++rec);
dump_sqlda(outp_sqlda);
}
exec sql whenever not found continue;
strcpy(msg, "close");
exec sql close mycur1;
strcpy(msg, "deallocate");
exec sql deallocate prepare st_id1;
free(outp_sqlda);
/* SQLDA test for getting ALL records into the sqlda list */
outp_sqlda = NULL;
strcpy(msg, "prepare");
exec sql prepare st_id2 from :stmt1;
strcpy(msg, "declare");
exec sql declare mycur2 cursor for st_id2;
strcpy(msg, "open");
exec sql open mycur2;
strcpy(msg, "fetch");
exec sql fetch all from mycur2 into descriptor outp_sqlda;
outp_sqlda1 = outp_sqlda;
rec = 0;
while (outp_sqlda1)
{
sqlda_t *ptr;
printf("FETCH RECORD %d\n", ++rec);
dump_sqlda(outp_sqlda1);
ptr = outp_sqlda1;
outp_sqlda1 = outp_sqlda1->desc_next;
free(ptr);
}
strcpy(msg, "close");
exec sql close mycur2;
strcpy(msg, "deallocate");
exec sql deallocate prepare st_id2;
/* SQLDA test for getting one record using an input descriptor */
/*
* Input sqlda has to be built manually
* sqlda_t contains 1 sqlvar_t structure already.
*/
inp_sqlda = (sqlda_t *)malloc(sizeof(sqlda_t));
memset(inp_sqlda, 0, sizeof(sqlda_t));
inp_sqlda->sqln = 1;
inp_sqlda->sqlvar[0].sqltype = ECPGt_int;
inp_sqlda->sqlvar[0].sqldata = (char *)&id;
printf("EXECUTE RECORD 4\n");
id = 4;
outp_sqlda = NULL;
strcpy(msg, "prepare");
exec sql prepare st_id3 FROM :stmt2;
strcpy(msg, "execute");
exec sql execute st_id3 using descriptor inp_sqlda into descriptor outp_sqlda;
dump_sqlda(outp_sqlda);
strcpy(msg, "deallocate");
exec sql deallocate prepare st_id3;
free(inp_sqlda);
free(outp_sqlda);
/* SQLDA test for getting one record using an input descriptor
* on a named connection
*/
exec sql connect to REGRESSDB1 as con2;
/*
* Input sqlda has to be built manually
* sqlda_t contains 1 sqlvar_t structure already.
*/
inp_sqlda = (sqlda_t *)malloc(sizeof(sqlda_t));
memset(inp_sqlda, 0, sizeof(sqlda_t));
inp_sqlda->sqln = 1;
inp_sqlda->sqlvar[0].sqltype = ECPGt_int;
inp_sqlda->sqlvar[0].sqldata = (char *)&id;
printf("EXECUTE RECORD 4\n");
id = 4;
outp_sqlda = NULL;
strcpy(msg, "prepare");
exec sql at con2 prepare st_id4 FROM :stmt2;
strcpy(msg, "execute");
exec sql at con2 execute st_id4 using descriptor inp_sqlda into descriptor outp_sqlda;
dump_sqlda(outp_sqlda);
strcpy(msg, "commit");
exec sql at con2 commit;
strcpy(msg, "deallocate");
exec sql deallocate prepare st_id4;
free(inp_sqlda);
free(outp_sqlda);
strcpy(msg, "disconnect");
exec sql disconnect con2;
/* End test */
strcpy(msg, "drop");
exec sql drop table t1;
strcpy(msg, "commit");
exec sql commit;
strcpy(msg, "disconnect");
exec sql disconnect;
return 0;
}
|
