1 // Copyright (c) 2003, 2018, Oracle and/or its affiliates. All rights reserved.
2 //
3 // This program is free software; you can redistribute it and/or modify
4 // it under the terms of the GNU General Public License, version 2.0, as
5 // published by the Free Software Foundation.
6 //
7 // This program is also distributed with certain software (including
8 // but not limited to OpenSSL) that is licensed under separate terms,
9 // as designated in a particular file or component or in included license
10 // documentation. The authors of MySQL hereby grant you an
11 // additional permission to link the program and your derivative works
12 // with the separately licensed software that they have included with
13 // MySQL.
14 //
15 // Without limiting anything contained in the foregoing, this file,
16 // which is part of <MySQL Product>, is also subject to the
17 // Universal FOSS Exception, version 1.0, a copy of which can be found at
18 // http://oss.oracle.com/licenses/universal-foss-exception.
19 //
20 // This program is distributed in the hope that it will be useful, but
21 // WITHOUT ANY WARRANTY; without even the implied warranty of
22 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
23 // See the GNU General Public License, version 2.0, for more details.
24 //
25 // You should have received a copy of the GNU General Public License
26 // along with this program; if not, write to the Free Software Foundation, Inc.,
27 // 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
28
29 #include "odbctap.h"
30
31 /* Testing SQL_FETCH_RELATIVE with row_set_size as 10 */
DECLARE_TEST(t_relative)32 DECLARE_TEST(t_relative)
33 {
34 SQLUINTEGER i, iarray[15];
35 SQLULEN nrows, index;
36 SQLCHAR name[21];
37
38 ok_stmt(hstmt, SQLSetStmtAttr(hstmt, SQL_ATTR_CURSOR_TYPE,
39 (SQLPOINTER)SQL_CURSOR_STATIC, 0));
40
41 ok_sql(hstmt, "DROP TABLE IF EXISTS t_relative");
42 ok_sql(hstmt, "CREATE TABLE t_relative (id INT, name CHAR(20))");
43
44 ok_stmt(hstmt, SQLPrepare(hstmt,
45 (SQLCHAR *)"INSERT INTO t_relative VALUES (?,?)",
46 SQL_NTS));
47
48 ok_stmt(hstmt, SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_ULONG,
49 SQL_INTEGER, 0, 0, &i, 0, NULL));
50 ok_stmt(hstmt, SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR,
51 SQL_CHAR, 20, 0, name, 20, NULL));
52
53 for (i= 1; i <= 50; i++)
54 {
55 sprintf((char *)name, "my%d", i);
56 ok_stmt(hstmt, SQLExecute(hstmt));
57 }
58
59 ok_stmt(hstmt, SQLFreeStmt(hstmt, SQL_RESET_PARAMS));
60 ok_stmt(hstmt, SQLFreeStmt(hstmt, SQL_CLOSE));
61
62 /* set row size as 10 */
63 ok_stmt(hstmt, SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE,
64 (SQLPOINTER)10, 0));
65
66 ok_stmt(hstmt, SQLSetStmtAttr(hstmt, SQL_ATTR_ROWS_FETCHED_PTR, &nrows, 0));
67
68 ok_sql(hstmt, "SELECT * FROM t_relative");
69
70 ok_stmt(hstmt, SQLBindCol(hstmt, 1, SQL_C_ULONG, &iarray, 0, NULL));
71
72 ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_NEXT, 0)); /* 1-10 */
73 is_num(nrows, 10);
74
75 for (index= 1; index <= nrows; index++)
76 is_num(iarray[index - 1], index);
77
78 ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_NEXT, 0)); /* 10-20 */
79 is_num(nrows, 10);
80
81 for (index= 1; index <= nrows; index++)
82 is_num(iarray[index - 1], index + 10);
83
84 ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_PREV, 0)); /* 1-10 */
85 is_num(nrows, 10);
86
87 for (index= 1; index <= nrows; index++)
88 is_num(iarray[index - 1], index);
89
90 ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, 1)); /* 2-11 */
91 is_num(nrows, 10);
92
93 for (index= 1; index <= nrows; index++)
94 is_num(iarray[index - 1], index + 1);
95
96 ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, -1)); /* 1-10 */
97 is_num(nrows, 10);
98
99 for (index= 1; index <= nrows; index++)
100 is_num(iarray[index - 1], index);
101
102 ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_FIRST, 0)); /* 1-10 */
103 is_num(nrows, 10);
104
105 for (index= 1; index <= nrows; index++)
106 is_num(iarray[index - 1], index);
107
108 expect_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, -1),
109 SQL_NO_DATA_FOUND); /* BOF */
110
111 ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, 1)); /* 1-10 */
112 is_num(nrows, 10);
113
114 for (index= 1; index <= nrows; index++)
115 is_num(iarray[index - 1], index);
116
117 ok_stmt(hstmt, SQLFreeStmt(hstmt, SQL_UNBIND));
118 ok_stmt(hstmt, SQLFreeStmt(hstmt, SQL_CLOSE));
119
120 /* reset row size */
121 ok_stmt(hstmt, SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE,
122 (SQLPOINTER)1, 0));
123
124 ok_sql(hstmt, "DROP TABLE IF EXISTS t_relative");
125
126 return OK;
127 }
128
129
130 /* Testing SQL_FETCH_RELATIVE with row_set_size as 1 */
DECLARE_TEST(t_relative1)131 DECLARE_TEST(t_relative1)
132 {
133 SQLULEN nrows;
134 SQLUINTEGER i;
135 const SQLUINTEGER max_rows= 10;
136
137 ok_stmt(hstmt, SQLSetStmtAttr(hstmt, SQL_ATTR_CURSOR_TYPE,
138 (SQLPOINTER)SQL_CURSOR_STATIC, 0));
139
140 ok_sql(hstmt, "DROP TABLE IF EXISTS t_relative1");
141 ok_sql(hstmt, "CREATE TABLE t_relative1 (id INT)");
142
143 ok_stmt(hstmt, SQLPrepare(hstmt,
144 (SQLCHAR *)"INSERT INTO t_relative1 VALUES (?)",
145 SQL_NTS));
146
147 ok_stmt(hstmt, SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_ULONG,
148 SQL_INTEGER, 0, 0, &i, 0, NULL));
149
150 for (i= 1; i <= max_rows; i++)
151 {
152 ok_stmt(hstmt, SQLExecute(hstmt));
153 }
154
155 ok_stmt(hstmt, SQLFreeStmt(hstmt, SQL_RESET_PARAMS));
156 ok_stmt(hstmt, SQLFreeStmt(hstmt, SQL_CLOSE));
157
158 /* set row_size as 1 */
159 ok_stmt(hstmt, SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE,
160 (SQLPOINTER)1, 0));
161
162 ok_stmt(hstmt, SQLSetStmtAttr(hstmt, SQL_ATTR_ROWS_FETCHED_PTR, &nrows, 0));
163
164 ok_sql(hstmt, "SELECT * FROM t_relative1");
165
166 ok_stmt(hstmt, SQLBindCol(hstmt, 1, SQL_C_LONG, &i, 0, NULL));
167
168 /* row 1 */
169 ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_NEXT, 0));
170 is_num(i, 1);
171
172 /* Before start */
173 expect_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, -1),
174 SQL_NO_DATA_FOUND);
175
176 /* jump to last row */
177 ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, max_rows));
178 is_num(i, max_rows);
179
180 /* jump to last row+1 */
181 expect_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, 1),
182 SQL_NO_DATA_FOUND);
183
184 /* goto first row */
185 ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_FIRST, 1));
186 is_num(i, 1);
187
188 /* before start */
189 expect_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, -1),
190 SQL_NO_DATA_FOUND);
191
192 /* goto fifth row */
193 ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, 5));
194 is_num(i, 5);
195
196 /* goto after end */
197 expect_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, max_rows),
198 SQL_NO_DATA_FOUND);
199
200 /* the scenarios from ODBC spec */
201
202 /* CASE 1 */
203 ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_FIRST, 1));
204 is_num(i, 1);
205
206 expect_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, -1),
207 SQL_NO_DATA_FOUND);
208
209 /* BeforeStart AND FetchOffset <= 0 */
210 expect_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, -20),
211 SQL_NO_DATA_FOUND);
212
213 expect_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, -1),
214 SQL_NO_DATA_FOUND);
215
216 expect_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, 0),
217 SQL_NO_DATA_FOUND);
218
219 /* case 1: Before start AND FetchOffset > 0 */
220 ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, 1));
221 is_num(i, 1);
222
223 /* CASE 2 */
224 ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_LAST, 1));
225 is_num(i, max_rows);
226
227 expect_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, 1),
228 SQL_NO_DATA_FOUND);
229
230 /* After end AND FetchOffset >= 0 */
231 expect_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, 10),
232 SQL_NO_DATA_FOUND);
233
234 expect_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, 20),
235 SQL_NO_DATA_FOUND);
236
237 expect_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, 1),
238 SQL_NO_DATA_FOUND);
239
240 expect_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, 0),
241 SQL_NO_DATA_FOUND);
242
243 /* After end AND FetchOffset < 0 */
244 ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, -1));
245 is_num(i, max_rows);
246
247 /* CASE 3 */
248 ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_FIRST, 1));
249 is_num(i, 1);
250
251 /* CurrRowsetStart = 1 AND FetchOffset < 0 */
252 ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, 0));
253 is_num(i, 1);
254
255 expect_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, -1),
256 SQL_NO_DATA_FOUND);
257
258 /* CASE 4 */
259 /* CurrRowsetStart > 1 AND CurrRowsetStart + FetchOffset < 1 AND
260 | FetchOffset | > RowsetSize
261 */
262 ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_FIRST, 1));
263 is_num(i, 1);
264
265 ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, 3));
266 is_num(i, 4);
267
268 /* the following call satisfies 4 > 1 AND (3-4) < 1 AND |-4| > 1 */
269 expect_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, -4),
270 SQL_NO_DATA_FOUND);
271
272 /* CASE 5 */
273 /* 1 <= CurrRowsetStart + FetchOffset <= LastResultRow */
274 ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_FIRST, 1));
275 is_num(i, 1);
276
277 ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, 5));
278 is_num(i, 6);
279
280 /* 1 <= 6-2 <= 10 */
281 ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, -2));
282 is_num(i, 4);
283
284 /* CASE 6 */
285 /* CurrRowsetStart > 1 AND CurrRowsetStart + FetchOffset < 1 AND
286 | FetchOffset | <= RowsetSize
287 */
288 ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_FIRST, 1));
289 is_num(i, 1);
290
291 ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, 3));
292 is_num(i, 4);
293
294 /* 4 >1 AND 4-4 <1 AND |-4| <=10 */
295 expect_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, -4),
296 SQL_NO_DATA_FOUND);
297
298 ok_stmt(hstmt, SQLFreeStmt(hstmt, SQL_UNBIND));
299 ok_stmt(hstmt, SQLFreeStmt(hstmt, SQL_CLOSE));
300
301 ok_sql(hstmt, "DROP TABLE IF EXISTS t_relative1");
302
303 return OK;
304 }
305
306
307 /* Testing SQL_FETCH_RELATIVE with row_set_size as 2 */
DECLARE_TEST(t_relative2)308 DECLARE_TEST(t_relative2)
309 {
310 SQLRETURN rc;
311 SQLULEN nrows;
312 SQLUINTEGER i, iarray[15];
313 const SQLUINTEGER max_rows=10;
314
315 ok_stmt(hstmt, SQLSetStmtAttr(hstmt, SQL_ATTR_CURSOR_TYPE,
316 (SQLPOINTER)SQL_CURSOR_STATIC, 0));
317
318 ok_sql(hstmt, "DROP TABLE IF EXISTS t_relative2");
319
320 ok_sql(hstmt, "create table t_relative2(id int)");
321
322 ok_stmt(hstmt, SQLPrepare(hstmt,
323 (SQLCHAR *) "insert into t_relative2 values(?)",
324 SQL_NTS));
325
326 rc = SQLBindParameter(hstmt,1,SQL_PARAM_INPUT, SQL_C_ULONG,
327 SQL_INTEGER,0,0,&i,0,NULL);
328 mystmt(hstmt,rc);
329
330 for ( i = 1; i <= max_rows; i++ )
331 {
332 rc = SQLExecute(hstmt);
333 mystmt(hstmt,rc);
334 }
335
336 SQLFreeStmt(hstmt,SQL_RESET_PARAMS);
337 SQLFreeStmt(hstmt,SQL_CLOSE);
338
339 rc = SQLEndTran(SQL_HANDLE_DBC,hdbc,SQL_COMMIT);
340 mycon(hdbc,rc);
341
342 /* set row_size as 2 */
343 rc = SQLSetStmtAttr(hstmt,SQL_ATTR_ROW_ARRAY_SIZE,(SQLPOINTER)2,0);
344 mystmt(hstmt,rc);
345
346 rc = SQLSetStmtAttr(hstmt,SQL_ATTR_ROWS_FETCHED_PTR,&nrows,0);
347 mystmt(hstmt,rc);
348
349 ok_sql(hstmt, "select * from t_relative2");
350 mystmt(hstmt,rc);
351
352 rc = SQLBindCol(hstmt,1,SQL_C_LONG,&iarray,0,NULL);
353 mystmt(hstmt,rc);
354
355 /* row 1 */
356 rc = SQLFetchScroll(hstmt,SQL_FETCH_NEXT,0);/* 1 */
357 mystmt(hstmt,rc);
358 my_assert(nrows == 2);
359 my_assert(iarray[0]==1);
360 my_assert(iarray[1]==2);
361
362
363 /* Before start */
364 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-1);/* before start */
365 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
366
367 /* jump to last row */
368 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,max_rows);/* last row */
369 mystmt(hstmt,rc);
370 my_assert(nrows == 1);
371 my_assert(iarray[0]==max_rows);
372
373 /* jump to last row+1 */
374 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,1);/* after last */
375 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
376
377 /* goto first row */
378 rc = SQLFetchScroll(hstmt,SQL_FETCH_FIRST,1);/* 1 */
379 mystmt(hstmt,rc);
380 my_assert(nrows == 2);
381 my_assert(iarray[0]==1);
382 my_assert(iarray[1]==2);
383
384 /* before start */
385 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-1);/* before start */
386 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
387
388 /* goto fifth row */
389 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,5);/* 5 */
390 mystmt(hstmt,rc);
391 my_assert(nrows == 2);
392 my_assert(iarray[0]==5);
393 my_assert(iarray[1]==6);
394
395 /* goto after end */
396 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,max_rows);/* after last */
397 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
398
399 /*
400 the scenarios from ODBC spec
401 */
402
403 /* CASE 1 */
404 rc = SQLFetchScroll(hstmt,SQL_FETCH_FIRST,1);/* 1 */
405 mystmt(hstmt,rc);
406 my_assert(nrows == 2);
407 my_assert(iarray[0]==1);
408 my_assert(iarray[1]==2);
409
410 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-1);/* before start */
411 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
412
413 /* BeforeStart AND FetchOffset <= 0 */
414 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-20);/* before start */
415 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
416
417 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-1);/* before start */
418 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
419
420 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,0);/* before start */
421 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
422
423 /* case 1: Before start AND FetchOffset > 0 */
424 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,1);/* 1 */
425 mystmt(hstmt,rc);
426 my_assert(nrows == 2);
427 my_assert(iarray[0]==1);
428 my_assert(iarray[1]==2);
429
430 /* CASE 2 */
431 rc = SQLFetchScroll(hstmt,SQL_FETCH_LAST,1);/* last row */
432 mystmt(hstmt,rc);
433 my_assert(nrows == 2);
434 my_assert(iarray[0]==max_rows-1);
435 my_assert(iarray[1]==max_rows);
436
437 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,1);/* last row */
438 mystmt(hstmt,rc);
439 my_assert(nrows == 1);
440 my_assert(iarray[0]==max_rows);
441
442 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,1);/* after last row */
443 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
444
445 /* After end AND FetchOffset >= 0 */
446 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,10);/* after end */
447 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
448
449 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,20);/* after end */
450 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
451
452 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,1);/* after end */
453 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
454
455 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,0);/* after end */
456 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
457
458 /* After end AND FetchOffset < 0 */
459 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-1);/* last row */
460 mystmt(hstmt,rc);
461 my_assert(nrows == 1);
462 my_assert(iarray[0]==max_rows);
463
464
465 /* CASE 3 */
466 rc = SQLFetchScroll(hstmt,SQL_FETCH_FIRST,1);/* first row */
467 mystmt(hstmt,rc);
468 my_assert(nrows == 2);
469 my_assert(iarray[0]==1);
470 my_assert(iarray[1]==2);
471
472 /* CurrRowsetStart = 1 AND FetchOffset < 0 */
473 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,0);/* first row */
474 mystmt(hstmt,rc);
475 my_assert(nrows == 2);
476 my_assert(iarray[0]==1);
477 my_assert(iarray[1]==2);
478
479 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-1);/* before start */
480 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
481
482 /* CASE 4 */
483 /* CurrRowsetStart > 1 AND CurrRowsetStart + FetchOffset < 1 AND
484 | FetchOffset | > RowsetSize
485 */
486 rc = SQLFetchScroll(hstmt,SQL_FETCH_FIRST,1);/* first row */
487 mystmt(hstmt,rc);
488 my_assert(nrows == 2);
489 my_assert(iarray[0]==1);
490 my_assert(iarray[1]==2);
491
492 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,3);/* fourth row */
493 mystmt(hstmt,rc);
494 my_assert(nrows == 2);
495 my_assert(iarray[0]==4);
496 my_assert(iarray[1]==5);
497
498 /* the following call satisfies 4 > 1 AND (3-4) < 1 AND |-4| > 1 */
499 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-4);/* before start */
500 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
501
502 /* CASE 5 */
503 /* 1 <= CurrRowsetStart + FetchOffset <= LastResultRow */
504 rc = SQLFetchScroll(hstmt,SQL_FETCH_FIRST,1);/* first row */
505 mystmt(hstmt,rc);
506 my_assert(nrows == 2);
507 my_assert(iarray[0]==1);
508 my_assert(iarray[1]==2);
509
510 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,5);/* sixth row */
511 mystmt(hstmt,rc);
512 my_assert(nrows == 2);
513 my_assert(iarray[0]==6);
514 my_assert(iarray[1]==7);
515
516 /* 1 <= 6-2 <= 10 */
517 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-2);/* fourth row */
518 mystmt(hstmt,rc);
519 my_assert(nrows == 2);
520 my_assert(iarray[0]==4);
521 my_assert(iarray[1]==5);
522
523 /* CASE 6 */
524 /* CurrRowsetStart > 1 AND CurrRowsetStart + FetchOffset < 1 AND
525 | FetchOffset | <= RowsetSize
526 */
527 rc = SQLFetchScroll(hstmt,SQL_FETCH_FIRST,1);/* first row */
528 mystmt(hstmt,rc);
529 my_assert(nrows == 2);
530 my_assert(iarray[0]==1);
531 my_assert(iarray[1]==2);
532
533 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,3);/* fourth row */
534 mystmt(hstmt,rc);
535 my_assert(nrows == 2);
536 my_assert(iarray[0]==4);
537 my_assert(iarray[1]==5);
538
539 /* 4 >1 AND 4-4 <1 AND |-4| <=10 */
540 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-4);/* before start */
541 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
542
543
544 SQLFreeStmt(hstmt,SQL_UNBIND);
545 SQLFreeStmt(hstmt,SQL_CLOSE);
546
547 rc = SQLSetStmtAttr(hstmt,SQL_ATTR_ROW_ARRAY_SIZE,(SQLPOINTER)1,0);
548 mystmt(hstmt,rc);
549
550 ok_sql(hstmt, "DROP TABLE IF EXISTS t_relative2");
551
552 return OK;
553 }
554
555
DECLARE_TEST(t_rows_fetched_ptr)556 DECLARE_TEST(t_rows_fetched_ptr)
557 {
558 SQLRETURN rc;
559 SQLULEN rowsFetched, rowsSize;
560 long i;
561
562 ok_sql(hstmt, "DROP TABLE IF EXISTS t_rows_fetched_ptr");
563
564 ok_sql(hstmt,"create table t_rows_fetched_ptr(a int)");
565
566 ok_sql(hstmt,"insert into t_rows_fetched_ptr values(0)");
567 ok_sql(hstmt,"insert into t_rows_fetched_ptr values(1)");
568 ok_sql(hstmt,"insert into t_rows_fetched_ptr values(2)");
569 ok_sql(hstmt,"insert into t_rows_fetched_ptr values(3)");
570 ok_sql(hstmt,"insert into t_rows_fetched_ptr values(4)");
571 ok_sql(hstmt,"insert into t_rows_fetched_ptr values(5)");
572
573 rowsSize= 1;
574 rc = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER)rowsSize, 0);
575 mystmt(hstmt,rc);
576
577 rc = SQLSetStmtAttr(hstmt, SQL_ATTR_ROWS_FETCHED_PTR, &rowsFetched, 0);
578 mystmt(hstmt,rc);
579
580 ok_sql(hstmt, "SELECT * FROM t_rows_fetched_ptr");
581
582 i= 0;
583 rc = SQLFetchScroll(hstmt,SQL_FETCH_NEXT,0);
584 while (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
585 {
586 printMessage("\n total rows fetched: %ld", rowsFetched);
587 myassert(rowsFetched == rowsSize);
588 i++; rowsFetched= 0;
589 rc = SQLFetchScroll(hstmt,SQL_FETCH_NEXT,0);
590 }
591 myassert( i == 6);
592 SQLFreeStmt(hstmt, SQL_CLOSE);
593
594 rowsSize= 2;
595 rc = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER)rowsSize, 0);
596 mystmt(hstmt,rc);
597
598 rc = SQLSetStmtAttr(hstmt, SQL_ATTR_ROWS_FETCHED_PTR, &rowsFetched, 0);
599 mystmt(hstmt,rc);
600
601 ok_sql(hstmt, "SELECT * FROM t_rows_fetched_ptr");
602 mystmt(hstmt,rc);
603
604 i= 0;
605 rc = SQLFetchScroll(hstmt,SQL_FETCH_NEXT,0);
606 while (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
607 {
608 printMessage("\n total rows fetched: %ld", rowsFetched);
609 myassert(rowsFetched == rowsSize);
610 i++;rowsFetched= 0;
611 rc = SQLFetchScroll(hstmt,SQL_FETCH_NEXT,0);
612 }
613 myassert( i == 3);
614 SQLFreeStmt(hstmt, SQL_CLOSE);
615
616 rowsSize= 3;
617 rc = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER)rowsSize, 0);
618 mystmt(hstmt,rc);
619
620 rc = SQLSetStmtAttr(hstmt, SQL_ATTR_ROWS_FETCHED_PTR, &rowsFetched, 0);
621 mystmt(hstmt,rc);
622
623 ok_sql(hstmt, "SELECT * FROM t_rows_fetched_ptr");
624 mystmt(hstmt,rc);
625
626 i= 0;
627 rc = SQLFetchScroll(hstmt,SQL_FETCH_NEXT,0);
628 while (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
629 {
630 printMessage("\n total rows fetched: %ld", rowsFetched);
631 myassert(rowsFetched == rowsSize);
632 i++;rowsFetched= 0;
633 rc = SQLFetchScroll(hstmt,SQL_FETCH_NEXT,0);
634 }
635 myassert( i == 2);
636 SQLFreeStmt(hstmt, SQL_CLOSE);
637
638 rowsSize= 4;
639 rc = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER)rowsSize, 0);
640 mystmt(hstmt,rc);
641
642 rc = SQLSetStmtAttr(hstmt, SQL_ATTR_ROWS_FETCHED_PTR, &rowsFetched, 0);
643 mystmt(hstmt,rc);
644
645 ok_sql(hstmt, "SELECT * FROM t_rows_fetched_ptr");
646 mystmt(hstmt,rc);
647
648 rc = SQLFetch(hstmt);
649 mystmt(hstmt,rc);
650
651 printMessage("\n total rows fetched: %ld", rowsFetched);
652 myassert(rowsFetched == rowsSize);
653
654 rc = SQLFetch(hstmt);
655 mystmt(hstmt,rc);
656
657 printMessage("\n total rows fetched: %ld", rowsFetched);
658 myassert(rowsFetched == 2);
659
660 rc = SQLFetch(hstmt);
661 myassert(rc == SQL_NO_DATA);
662
663 SQLFreeStmt(hstmt, SQL_CLOSE);
664
665 rc = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER)1, 0);/* reset */
666 mystmt(hstmt,rc);
667
668 rc = SQLSetStmtAttr(hstmt, SQL_ATTR_ROWS_FETCHED_PTR, NULL, 0);
669 mystmt(hstmt,rc);
670
671 ok_sql(hstmt, "DROP TABLE IF EXISTS t_rows_fetched_ptr");
672
673 return OK;
674 }
675
676
DECLARE_TEST(t_rows_fetched_ptr1)677 DECLARE_TEST(t_rows_fetched_ptr1)
678 {
679 SQLRETURN rc;
680 SQLULEN rowsFetched, rowsSize;
681 SQLINTEGER i;
682
683 ok_sql(hstmt, "DROP TABLE IF EXISTS t_rows_fetched_ptr");
684
685 ok_sql(hstmt, "create table t_rows_fetched_ptr(a int)");
686 ok_sql(hstmt, "insert into t_rows_fetched_ptr values(0)");
687 ok_sql(hstmt, "insert into t_rows_fetched_ptr values(1)");
688 ok_sql(hstmt, "insert into t_rows_fetched_ptr values(2)");
689 ok_sql(hstmt, "insert into t_rows_fetched_ptr values(3)");
690 ok_sql(hstmt, "insert into t_rows_fetched_ptr values(4)");
691 ok_sql(hstmt, "insert into t_rows_fetched_ptr values(5)");
692
693 rowsSize= 1;
694 rc = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER)rowsSize, 0);
695 mystmt(hstmt,rc);
696
697 rc = SQLSetStmtAttr(hstmt, SQL_ATTR_ROWS_FETCHED_PTR, &rowsFetched, 0);
698 mystmt(hstmt,rc);
699
700 ok_sql(hstmt, "SELECT * FROM t_rows_fetched_ptr");
701 mystmt(hstmt,rc);
702
703 i= 0;
704 rc = SQLFetchScroll(hstmt,SQL_FETCH_NEXT,0);
705 while (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
706 {
707 fprintf(stdout,"total rows fetched: %ld\n", (long)rowsFetched);
708 myassert(rowsFetched == rowsSize);
709 i++; rowsFetched= 0;
710 rc = SQLFetchScroll(hstmt, SQL_FETCH_NEXT, 0);
711 }
712 myassert( i == 6);
713 SQLFreeStmt(hstmt, SQL_CLOSE);
714
715 rowsSize= 2;
716 rc = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER)rowsSize, 0);
717 mystmt(hstmt,rc);
718
719 rc = SQLSetStmtAttr(hstmt, SQL_ATTR_ROWS_FETCHED_PTR, &rowsFetched, 0);
720 mystmt(hstmt,rc);
721
722 ok_sql(hstmt, "SELECT * FROM t_rows_fetched_ptr");
723 mystmt(hstmt,rc);
724
725 i= 0;
726 rc = SQLFetchScroll(hstmt,SQL_FETCH_NEXT,0);
727 while (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
728 {
729 fprintf(stdout,"total rows fetched: %ld\n", (long)rowsFetched);
730 myassert(rowsFetched == rowsSize);
731 i++;rowsFetched= 0;
732 rc = SQLFetchScroll(hstmt,SQL_FETCH_NEXT,0);
733 }
734 myassert( i == 3);
735 SQLFreeStmt(hstmt, SQL_CLOSE);
736
737 rowsSize= 3;
738 rc = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER)rowsSize, 0);
739 mystmt(hstmt,rc);
740
741 rc = SQLSetStmtAttr(hstmt, SQL_ATTR_ROWS_FETCHED_PTR, &rowsFetched, 0);
742 mystmt(hstmt,rc);
743
744 ok_sql(hstmt, "SELECT * FROM t_rows_fetched_ptr");
745 mystmt(hstmt,rc);
746
747 i= 0;
748 rc = SQLFetchScroll(hstmt,SQL_FETCH_NEXT,0);
749 while (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
750 {
751 printMessage("total rows fetched: %ld\n", rowsFetched);
752 myassert(rowsFetched == rowsSize);
753 i++;rowsFetched= 0;
754 rc = SQLFetchScroll(hstmt, SQL_FETCH_NEXT, 0);
755 }
756 myassert( i == 2);
757 SQLFreeStmt(hstmt, SQL_CLOSE);
758
759 rowsSize= 4;
760 rc = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER)rowsSize, 0);
761 mystmt(hstmt,rc);
762
763 rc = SQLSetStmtAttr(hstmt, SQL_ATTR_ROWS_FETCHED_PTR, &rowsFetched, 0);
764 mystmt(hstmt,rc);
765
766 ok_sql(hstmt, "SELECT * FROM t_rows_fetched_ptr");
767 mystmt(hstmt,rc);
768
769 rc = SQLFetch(hstmt);
770 mystmt(hstmt,rc);
771
772 printMessage("total rows fetched: %ld\n", rowsFetched);
773 myassert(rowsFetched == rowsSize);
774
775 rc = SQLFetch(hstmt);
776 mystmt(hstmt,rc);
777
778 printMessage("total rows fetched: %ld\n", rowsFetched);
779 myassert(rowsFetched == 2);
780
781 rc = SQLFetch(hstmt);
782 myassert(rc == SQL_NO_DATA);
783
784 SQLFreeStmt(hstmt, SQL_CLOSE);
785
786 rc = SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER)1, 0);/* reset */
787 mystmt(hstmt,rc);
788
789 rc = SQLSetStmtAttr(hstmt, SQL_ATTR_ROWS_FETCHED_PTR, NULL, 0);
790 mystmt(hstmt,rc);
791
792 ok_sql(hstmt, "DROP TABLE IF EXISTS t_rows_fetched_ptr");
793
794 return OK;
795 }
796
797
798 BEGIN_TESTS
799 ADD_TEST(t_relative)
800 ADD_TEST(t_relative1)
801 ADD_TEST(t_relative2)
802 ADD_TEST(t_rows_fetched_ptr)
803 ADD_TEST(t_rows_fetched_ptr1)
804 END_TESTS
805
806
807 RUN_TESTS
808