Line data Source code
1 : /*
2 : * SPDX-License-Identifier: MPL-2.0
3 : *
4 : * This Source Code Form is subject to the terms of the Mozilla Public
5 : * License, v. 2.0. If a copy of the MPL was not distributed with this
6 : * file, You can obtain one at http://mozilla.org/MPL/2.0/.
7 : *
8 : * Copyright 2024 MonetDB Foundation;
9 : * Copyright August 2008 - 2023 MonetDB B.V.;
10 : * Copyright 1997 - July 2008 CWI.
11 : */
12 :
13 : #ifdef _MSC_VER
14 : /* suppress deprecation warning for snprintf */
15 : #define _CRT_SECURE_NO_WARNINGS
16 :
17 : #include <WTypes.h>
18 : #endif
19 : #include <stdio.h>
20 : #include <stdlib.h>
21 : #include <stdint.h>
22 : #include <string.h>
23 : #include <sql.h>
24 : #include <sqlext.h>
25 :
26 : static void
27 0 : prerr(SQLSMALLINT tpe, SQLHANDLE hnd, const char *func, const char *pref)
28 : {
29 0 : SQLCHAR state[6];
30 0 : SQLINTEGER errnr;
31 0 : SQLCHAR msg[256];
32 0 : SQLSMALLINT msglen;
33 :
34 0 : switch (SQLGetDiagRec(tpe, hnd, 1, state, &errnr, msg, sizeof(msg), &msglen)) {
35 0 : case SQL_SUCCESS_WITH_INFO:
36 0 : if (msglen >= (signed int) sizeof(msg))
37 0 : fprintf(stderr, "(message truncated)\n");
38 : /* fall through */
39 : case SQL_SUCCESS:
40 0 : fprintf(stderr, "%s: %s: SQLstate %s, Errnr %d, Message %s\n", func, pref, (char*)state, (int)errnr, (char*)msg);
41 0 : break;
42 0 : case SQL_INVALID_HANDLE:
43 0 : fprintf(stderr, "%s: %s, invalid handle passed to error function\n", func, pref);
44 0 : break;
45 0 : case SQL_ERROR:
46 0 : fprintf(stderr, "%s: %s, unexpected error from SQLGetDiagRec\n", func, pref);
47 0 : break;
48 : case SQL_NO_DATA:
49 : break;
50 0 : default:
51 0 : fprintf(stderr, "%s: %s, weird return value from SQLGetDiagRec\n", func, pref);
52 0 : break;
53 : }
54 0 : }
55 :
56 : static void
57 4047 : check(SQLRETURN ret, SQLSMALLINT tpe, SQLHANDLE hnd, const char *func)
58 : {
59 4047 : switch (ret) {
60 : case SQL_SUCCESS:
61 : break;
62 0 : case SQL_SUCCESS_WITH_INFO:
63 0 : prerr(tpe, hnd, func, "Info");
64 0 : break;
65 0 : case SQL_ERROR:
66 0 : prerr(tpe, hnd, func, "Error");
67 0 : break;
68 0 : case SQL_INVALID_HANDLE:
69 0 : fprintf(stderr, "%s: Error: invalid handle\n", func);
70 0 : exit(1);
71 0 : default:
72 0 : fprintf(stderr, "%s: Unexpected return value\n", func);
73 0 : break;
74 : }
75 4047 : }
76 :
77 : int
78 1 : main(int argc, char **argv)
79 : {
80 1 : SQLHANDLE env;
81 1 : SQLHANDLE dbc;
82 1 : SQLHANDLE stmt, stmt2;
83 1 : char *dsn = "MonetDB";
84 1 : char *user = "monetdb";
85 1 : char *pass = "monetdb";
86 1 : SQLRETURN ret;
87 1 : int i;
88 1 : SQLSMALLINT f1;
89 1 : char f2[30], ff2[30];
90 1 : SQLDOUBLE f3;
91 1 : SQL_DATE_STRUCT f4, ff4;
92 1 : SQL_TIME_STRUCT f5, ff5;
93 :
94 1 : if (argc > 1)
95 1 : dsn = argv[1];
96 1 : if (argc > 2)
97 0 : user = argv[2];
98 0 : if (argc > 3)
99 0 : pass = argv[3];
100 1 : if (argc > 4 || *dsn == '-') {
101 0 : fprintf(stderr, "Usage: %s [datasource [user [password]]]\n", argv[0]);
102 0 : exit(1);
103 : }
104 :
105 1 : if (SQLAllocHandle(SQL_HANDLE_ENV, NULL, &env) != SQL_SUCCESS) {
106 0 : fprintf(stderr, "Cannot allocate ODBC environment handle\n");
107 0 : exit(1);
108 : }
109 :
110 1 : ret = SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) (uintptr_t) SQL_OV_ODBC3, 0);
111 1 : check(ret, SQL_HANDLE_ENV, env, "SQLSetEnvAttr");
112 :
113 1 : ret = SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
114 1 : check(ret, SQL_HANDLE_ENV, env, "SQLAllocHandle (DBC)");
115 :
116 1 : ret = SQLConnect(dbc, (SQLCHAR *) dsn, SQL_NTS, (SQLCHAR *) user, SQL_NTS, (SQLCHAR *) pass, SQL_NTS);
117 1 : check(ret, SQL_HANDLE_DBC, dbc, "SQLConnect");
118 :
119 1 : ret = SQLSetConnectAttr(dbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER) (uintptr_t) SQL_AUTOCOMMIT_OFF, 0);
120 1 : check(ret, SQL_HANDLE_DBC, dbc, "SQLSetConnectAttr");
121 :
122 : /* create a test table to be filled with values */
123 1 : ret = SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
124 1 : check(ret, SQL_HANDLE_DBC, dbc, "SQLAllocHandle (STMT 1)");
125 :
126 1 : ret = SQLExecDirect(stmt, (SQLCHAR *)
127 : "CREATE TABLE odbcsampletest (\n"
128 : " i INT DEFAULT '0' NOT NULL,\n"
129 : " s VARCHAR(200),\n"
130 : " f FLOAT,\n"
131 : " d DATE,\n"
132 : " t TIME\n"
133 : ")", SQL_NTS);
134 1 : check(ret, SQL_HANDLE_STMT, stmt, "SQLExecDirect 1");
135 :
136 : /* figure out the type of the columns and bind parameters */
137 : {
138 1 : SQLSMALLINT coltype;
139 1 : SQLSMALLINT colno;
140 :
141 1 : ret = SQLColumns(stmt, NULL, 0, NULL, 0, (SQLCHAR *) "odbcsampletest", SQL_NTS, NULL, 0);
142 1 : check(ret, SQL_HANDLE_STMT, stmt, "SQLColumns");
143 1 : ret = SQLBindCol(stmt, 5, SQL_C_SSHORT, &coltype, 0, NULL);
144 1 : check(ret, SQL_HANDLE_STMT, stmt, "SQLBindCol 1");
145 1 : ret = SQLBindCol(stmt, 17, SQL_C_SSHORT, &colno, 0, NULL);
146 1 : check(ret, SQL_HANDLE_STMT, stmt, "SQLBindCol 2");
147 :
148 6 : for (;;) {
149 6 : ret = SQLFetch(stmt);
150 6 : if (ret == SQL_NO_DATA)
151 : break;
152 5 : check(ret, SQL_HANDLE_STMT, stmt, "SQLFetch");
153 5 : switch (colno) {
154 1 : case 1:
155 1 : ret = SQLBindParameter(stmt, 1, SQL_PARAM_INPUT, SQL_C_SSHORT, coltype, 0, 0, &f1, sizeof(f1), NULL);
156 1 : check(ret, SQL_HANDLE_STMT, stmt, "SQLBindParameter 1");
157 1 : break;
158 1 : case 2:
159 1 : ret = SQLBindParameter(stmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, coltype, 0, 0, &f2, sizeof(f2), NULL);
160 1 : check(ret, SQL_HANDLE_STMT, stmt, "SQLBindParameter 2");
161 1 : break;
162 1 : case 3:
163 1 : ret = SQLBindParameter(stmt, 3, SQL_PARAM_INPUT, SQL_C_DOUBLE, coltype, 0, 0, &f3, sizeof(f3), NULL);
164 1 : check(ret, SQL_HANDLE_STMT, stmt, "SQLBindParameter 3");
165 1 : break;
166 1 : case 4:
167 1 : ret = SQLBindParameter(stmt, 4, SQL_PARAM_INPUT, SQL_C_TYPE_DATE, coltype, 0, 0, &f4, sizeof(f4), NULL);
168 1 : check(ret, SQL_HANDLE_STMT, stmt, "SQLBindParameter 4");
169 1 : break;
170 1 : case 5:
171 1 : ret = SQLBindParameter(stmt, 5, SQL_PARAM_INPUT, SQL_C_TYPE_TIME, coltype, 0, 0, &f5, sizeof(f5), NULL);
172 1 : check(ret, SQL_HANDLE_STMT, stmt, "SQLBindParameter 5");
173 1 : break;
174 : }
175 : }
176 1 : ret = SQLFreeStmt(stmt, SQL_UNBIND);
177 1 : check(ret, SQL_HANDLE_STMT, stmt, "SQLFreeStmt");
178 1 : ret = SQLCloseCursor(stmt);
179 1 : check(ret, SQL_HANDLE_STMT, stmt, "SQLCloseCursor");
180 : }
181 :
182 : /* prepare for filling the test table */
183 : /* we use a single statement with parameters whose values vary */
184 :
185 1 : ret = SQLPrepare(stmt, (SQLCHAR *)
186 : "INSERT INTO odbcsampletest VALUES (?, ?, ?, ?, ?)", SQL_NTS);
187 1 : check(ret, SQL_HANDLE_STMT, stmt, "SQLPrepare 1");
188 :
189 : /* do the actual filling of the test table */
190 1 : f4.year = 2003;
191 1 : f4.month = 1;
192 1 : f4.day = 1;
193 1 : f5.hour = 0;
194 1 : f5.minute = 0;
195 1 : f5.second = 0;
196 2001 : for (i = 0; i < 2000; i++) {
197 2000 : f1 = i;
198 : /* \342\200\230 is the UTF-8 encoding of U+2018 Left Single Quotation Mark;
199 : \342\200\231 is the UTF-8 encoding of U+2019 Right Single Quotation Mark */
200 2000 : snprintf(f2, sizeof(f2), "value \342\200\230%d\342\200\231", i);
201 2000 : f3 = i * 1.5;
202 2000 : f4.day++;
203 2000 : if ((f4.day == 29 && f4.month == 2) || (f4.day == 31 && (f4.month == 4 || f4.month == 6 || f4.month == 9 || f4.month == 11)) || f4.day == 32) {
204 65 : f4.day = 1;
205 65 : f4.month++;
206 65 : if (f4.month == 13) {
207 5 : f4.month = 1;
208 5 : f4.year++;
209 : }
210 : }
211 2000 : f5.second++;
212 2000 : if (f5.second == 60) {
213 33 : f5.second = 0;
214 33 : f5.minute++;
215 33 : if (f5.minute == 60) {
216 0 : f5.minute = 0;
217 0 : f5.hour++;
218 0 : if (f5.hour == 25)
219 0 : f5.hour = 0;
220 : }
221 : }
222 2000 : ret = SQLExecute(stmt);
223 2000 : check(ret, SQL_HANDLE_STMT, stmt, "SQLExecute 1");
224 : }
225 :
226 1 : ret = SQLEndTran(SQL_HANDLE_DBC, dbc, SQL_COMMIT);
227 1 : check(ret, SQL_HANDLE_DBC, dbc, "SQLEndTran");
228 :
229 1 : ret = SQLSetConnectAttr(dbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER) (uintptr_t) SQL_AUTOCOMMIT_ON, 0);
230 1 : check(ret, SQL_HANDLE_DBC, dbc, "SQLSetConnectAttr");
231 :
232 : /* Now we are going to read back the values from the test table.
233 : We create two statement handles, one of which will be used
234 : to read the even table entries and the other for the odd
235 : table entries. */
236 :
237 1 : check(ret, SQL_HANDLE_DBC, dbc, "SQLAllocHandle 3");
238 :
239 : /* first the handle for the even entries */
240 :
241 : /* bind the columns before preparing the statement */
242 1 : ret = SQLBindCol(stmt, 1, SQL_C_SSHORT, &f1, sizeof(f1), NULL);
243 1 : check(ret, SQL_HANDLE_STMT, stmt, "SQLBindCol 1");
244 1 : ret = SQLBindCol(stmt, 2, SQL_C_CHAR, &f2, sizeof(f2), NULL);
245 1 : check(ret, SQL_HANDLE_STMT, stmt, "SQLBindCol 2");
246 1 : ret = SQLBindCol(stmt, 3, SQL_C_DOUBLE, &f3, sizeof(f3), NULL);
247 1 : check(ret, SQL_HANDLE_STMT, stmt, "SQLBindCol 3");
248 1 : ret = SQLBindCol(stmt, 4, SQL_C_TYPE_DATE, &f4, sizeof(f4), NULL);
249 1 : check(ret, SQL_HANDLE_STMT, stmt, "SQLBindCol 4");
250 1 : ret = SQLBindCol(stmt, 5, SQL_C_TYPE_TIME, &f5, sizeof(f5), NULL);
251 1 : check(ret, SQL_HANDLE_STMT, stmt, "SQLBindCol 5");
252 :
253 1 : ret = SQLPrepare(stmt, (SQLCHAR *) "SELECT * FROM odbcsampletest WHERE 2*(i/2) = i", SQL_NTS);
254 1 : check(ret, SQL_HANDLE_STMT, stmt, "SQLPrepare 2");
255 :
256 1 : ret = SQLExecute(stmt);
257 1 : check(ret, SQL_HANDLE_STMT, stmt, "SQLExecute 2");
258 :
259 : /* now the handle for the odd entries */
260 1 : ret = SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt2);
261 1 : check(ret, SQL_HANDLE_DBC, dbc, "SQLAllocHandle (STMT 2)");
262 :
263 1 : ret = SQLPrepare(stmt2, (SQLCHAR *) "SELECT * FROM odbcsampletest WHERE 2*(i/2) <> i", SQL_NTS);
264 1 : check(ret, SQL_HANDLE_STMT, stmt2, "SQLPrepare 3");
265 :
266 : /* bind the columns after preparing the statement */
267 1 : ret = SQLBindCol(stmt2, 1, SQL_C_SSHORT, &f1, sizeof(f1), NULL);
268 1 : check(ret, SQL_HANDLE_STMT, stmt2, "SQLBindCol 6");
269 1 : ret = SQLBindCol(stmt2, 2, SQL_C_CHAR, &f2, sizeof(f2), NULL);
270 1 : check(ret, SQL_HANDLE_STMT, stmt2, "SQLBindCol 7");
271 1 : ret = SQLBindCol(stmt2, 3, SQL_C_DOUBLE, &f3, sizeof(f3), NULL);
272 1 : check(ret, SQL_HANDLE_STMT, stmt2, "SQLBindCol 8");
273 1 : ret = SQLBindCol(stmt2, 4, SQL_C_TYPE_DATE, &f4, sizeof(f4), NULL);
274 1 : check(ret, SQL_HANDLE_STMT, stmt2, "SQLBindCol 9");
275 1 : ret = SQLBindCol(stmt2, 5, SQL_C_TYPE_TIME, &f5, sizeof(f5), NULL);
276 1 : check(ret, SQL_HANDLE_STMT, stmt2, "SQLBindCol 10");
277 :
278 1 : ret = SQLExecute(stmt2);
279 1 : check(ret, SQL_HANDLE_STMT, stmt2, "SQLExecute 3");
280 :
281 1 : i = 0;
282 1 : ff4 = (SQL_DATE_STRUCT) {
283 : .year = 2003,
284 : .month = 1,
285 : .day = 1,
286 : };
287 1 : ff5 = (SQL_TIME_STRUCT) {
288 : .hour = 0,
289 : .minute = 0,
290 : .second = 0,
291 : };
292 2001 : for (;;) {
293 : /* Alternate fetching an even and an odd entry. The
294 : end result should be that we get all entries in the
295 : correct order. */
296 1001 : ret = SQLFetchScroll(stmt, SQL_FETCH_NEXT, 0);
297 :
298 1001 : if (ret == SQL_NO_DATA)
299 : break;
300 1000 : check(ret, SQL_HANDLE_STMT, stmt, "SQLFetch 1");
301 1000 : snprintf(ff2, sizeof(ff2), "value \342\200\230%d\342\200\231", i);
302 1000 : ff4.day++;
303 1000 : if ((ff4.day == 29 && ff4.month == 2) || (ff4.day == 31 && (ff4.month == 4 || ff4.month == 6 || ff4.month == 9 || ff4.month == 11)) || ff4.day == 32) {
304 33 : ff4.day = 1;
305 33 : ff4.month++;
306 33 : if (ff4.month == 13) {
307 3 : ff4.month = 1;
308 3 : ff4.year++;
309 : }
310 : }
311 1000 : ff5.second++;
312 1000 : if (ff5.second == 60) {
313 0 : ff5.second = 0;
314 0 : ff5.minute++;
315 0 : if (ff5.minute == 60) {
316 0 : ff5.minute = 0;
317 0 : ff5.hour++;
318 0 : if (ff5.hour == 25)
319 0 : ff5.hour = 0;
320 : }
321 : }
322 1000 : if (f1 != i || strcmp(f2, ff2) != 0 || f3 != i * 1.5 || f4.year != ff4.year || f4.month != ff4.month || f4.day != ff4.day || f5.hour != ff5.hour || f5.minute != ff5.minute || f5.second != ff5.second)
323 0 : printf("%d %s %g %04d:%02d:%02d %02d-%02d-%02d\n", f1, f2, f3, f4.year, f4.month, f4.day, f5.hour, f5.minute, f5.second);
324 1000 : i++;
325 :
326 1000 : ret = SQLFetch(stmt2);
327 1000 : if (ret == SQL_NO_DATA)
328 : break;
329 1000 : check(ret, SQL_HANDLE_STMT, stmt2, "SQLFetch 2");
330 1000 : snprintf(ff2, sizeof(ff2), "value \342\200\230%d\342\200\231", i);
331 1000 : ff4.day++;
332 1000 : if ((ff4.day == 29 && ff4.month == 2) || (ff4.day == 31 && (ff4.month == 4 || ff4.month == 6 || ff4.month == 9 || ff4.month == 11)) || ff4.day == 32) {
333 32 : ff4.day = 1;
334 32 : ff4.month++;
335 32 : if (ff4.month == 13) {
336 2 : ff4.month = 1;
337 2 : ff4.year++;
338 : }
339 : }
340 1000 : ff5.second++;
341 1000 : if (ff5.second == 60) {
342 33 : ff5.second = 0;
343 33 : ff5.minute++;
344 33 : if (ff5.minute == 60) {
345 0 : ff5.minute = 0;
346 0 : ff5.hour++;
347 0 : if (ff5.hour == 25)
348 0 : ff5.hour = 0;
349 : }
350 : }
351 1000 : if (f1 != i || strcmp(f2, ff2) != 0 || f3 != i * 1.5 || f4.year != ff4.year || f4.month != ff4.month || f4.day != ff4.day || f5.hour != ff5.hour || f5.minute != ff5.minute || f5.second != ff5.second)
352 0 : printf("%d %s %g %04d:%02d:%02d %02d-%02d-%02d\n", f1, f2, f3, f4.year, f4.month, f4.day, f5.hour, f5.minute, f5.second);
353 1000 : i++;
354 :
355 : }
356 :
357 1 : ret = SQLCloseCursor(stmt);
358 1 : check(ret, SQL_HANDLE_STMT, stmt, "SQLCloseCursor");
359 :
360 : /* cleanup and disconnect */
361 1 : ret = SQLFreeHandle(SQL_HANDLE_STMT, stmt2);
362 1 : check(ret, SQL_HANDLE_STMT, stmt2, "SQLFreeHandle (STMT 2)");
363 :
364 : /* drop the test table */
365 1 : ret = SQLExecDirect(stmt, (SQLCHAR *) "DROP TABLE odbcsampletest", SQL_NTS);
366 1 : check(ret, SQL_HANDLE_STMT, stmt, "SQLExecDirect 3");
367 :
368 1 : ret = SQLFreeHandle(SQL_HANDLE_STMT, stmt);
369 1 : check(ret, SQL_HANDLE_STMT, stmt, "SQLFreeHandle (STMT 1)");
370 :
371 1 : ret = SQLDisconnect(dbc);
372 1 : check(ret, SQL_HANDLE_DBC, dbc, "SQLDisconnect");
373 :
374 1 : ret = SQLFreeHandle(SQL_HANDLE_DBC, dbc);
375 1 : check(ret, SQL_HANDLE_DBC, dbc, "SQLFreeHandle (DBC)");
376 :
377 1 : ret = SQLFreeHandle(SQL_HANDLE_ENV, env);
378 1 : check(ret, SQL_HANDLE_ENV, env, "SQLFreeHandle (ENV)");
379 :
380 1 : return 0;
381 : }
|
