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 basic scrolling feature */
DECLARE_TEST(t_scroll)32 DECLARE_TEST(t_scroll)
33 {
34 SQLUINTEGER i;
35
36 ok_sql(hstmt, "DROP TABLE IF EXISTS t_scroll");
37 ok_sql(hstmt, "CREATE TABLE t_scroll (col1 INT)");
38 ok_sql(hstmt, "INSERT INTO t_scroll VALUES (1),(2),(3),(4),(5)");
39
40 ok_stmt(hstmt, SQLFreeStmt(hstmt, SQL_CLOSE));
41 ok_stmt(hstmt, SQLSetStmtAttr(hstmt, SQL_ATTR_CURSOR_TYPE,
42 (SQLPOINTER)SQL_CURSOR_STATIC, 0));
43
44 ok_sql(hstmt, "SELECT * FROM t_scroll");
45
46 ok_stmt(hstmt, SQLBindCol(hstmt, 1, SQL_C_ULONG, &i, 0, NULL));
47
48 ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_LAST, 0)); /* 5 */
49 is_num(i, 5);
50
51 ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_PREV, 0));/* 4 */
52 is_num(i, 4);
53
54 ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, -3));/* 1 */
55 is_num(i, 1);
56
57 expect_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, -1),
58 SQL_NO_DATA_FOUND); /* 0 */
59
60 expect_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_PREV, 1),
61 SQL_NO_DATA_FOUND); /* 0 */
62
63 ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_FIRST, -1));/* 1 */
64 is_num(i, 1);
65
66 ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_ABSOLUTE, 4));/* 4 */
67 is_num(i, 4);
68
69 expect_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, 2),
70 SQL_NO_DATA_FOUND); /* 6 */
71
72 ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_PREV, 2));/* last */
73 is_num(i, 5);
74
75 expect_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_NEXT, 2),
76 SQL_NO_DATA_FOUND); /* last + 1 */
77
78 expect_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_ABSOLUTE, -7),
79 SQL_NO_DATA_FOUND); /* 0 */
80
81 ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_FIRST, 2));/* 1 */
82 is_num(i, 1);
83
84 expect_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_PREV, 2),
85 SQL_NO_DATA_FOUND); /* 0 */
86
87 ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_NEXT, 0));/* 1 */
88 is_num(i, 1);
89
90 expect_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_PREV, 0),
91 SQL_NO_DATA_FOUND); /* 0 */
92
93 expect_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, -1),
94 SQL_NO_DATA_FOUND); /* 0 */
95
96 ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, 1));/* 1 */
97 is_num(i, 1);
98
99 expect_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, -1),
100 SQL_NO_DATA_FOUND); /* 0 */
101
102 ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, 1));/* 1 */
103 is_num(i, 1);
104
105 ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, 1));/* 1 */
106 is_num(i, 2);
107
108 expect_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, -2),
109 SQL_NO_DATA_FOUND); /* 0 */
110
111 expect_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, 6),
112 SQL_NO_DATA_FOUND); /* last + 1 */
113
114 ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_PREV, 6));/* 1 */
115 is_num(i, 5);
116
117 ok_stmt(hstmt, SQLFreeStmt(hstmt, SQL_CLOSE));
118
119 ok_sql(hstmt, "DROP TABLE IF EXISTS t_scroll");
120
121 return OK;
122 }
123
124
125 /* Testing SQL_FETCH_RELATIVE with row_set_size as 10 */
DECLARE_TEST(t_array_relative_10)126 DECLARE_TEST(t_array_relative_10)
127 {
128 SQLRETURN rc;
129 SQLINTEGER iarray[15];
130 SQLLEN nrows, index;
131 SQLUINTEGER i;
132 char name[21];
133
134 ok_stmt(hstmt, SQLSetStmtAttr(hstmt, SQL_ATTR_CURSOR_TYPE,
135 (SQLPOINTER)SQL_CURSOR_STATIC, 0));
136
137 ok_sql(hstmt, "DROP TABLE IF EXISTS t_array_relative_10");
138
139 ok_sql(hstmt, "create table t_array_relative_10(id int,name char(20))");
140
141 rc = SQLPrepare(hstmt,(SQLCHAR *)"insert into t_array_relative_10 values(?,?)",SQL_NTS);
142 mystmt(hstmt,rc);
143
144 rc = SQLBindParameter(hstmt,1,SQL_PARAM_INPUT, SQL_C_ULONG,
145 SQL_INTEGER,0,0,&i,0,NULL);
146 mystmt(hstmt,rc);
147 rc = SQLBindParameter(hstmt,2,SQL_PARAM_INPUT, SQL_C_CHAR,
148 SQL_CHAR,20,0,name,20,NULL);
149 mystmt(hstmt,rc);
150
151 for ( i = 1; i <= 50; i++ )
152 {
153 sprintf(name,"my%d",i);
154 rc = SQLExecute(hstmt);
155 mystmt(hstmt,rc);
156 }
157
158 SQLFreeStmt(hstmt,SQL_RESET_PARAMS);
159 SQLFreeStmt(hstmt,SQL_CLOSE);
160
161 rc = SQLEndTran(SQL_HANDLE_DBC,hdbc,SQL_COMMIT);
162 mycon(hdbc,rc);
163
164 /* set row size as 10 */
165 rc = SQLSetStmtAttr(hstmt,SQL_ATTR_ROW_ARRAY_SIZE,(SQLPOINTER)10,0);
166 mystmt(hstmt,rc);
167
168 /* According to http://support.microsoft.com/kb/298678, the storage
169 pointed to if SQL_ATTR_ROWS_FETCHED_PTR should be SQLLEN */
170 rc = SQLSetStmtAttr(hstmt,SQL_ATTR_ROWS_FETCHED_PTR,&nrows,0);
171 mystmt(hstmt,rc);
172
173 ok_sql(hstmt, "select * from t_array_relative_10");
174
175 rc = SQLBindCol(hstmt,1,SQL_C_LONG,iarray,0,NULL);
176 mystmt(hstmt,rc);
177
178 rc = SQLFetchScroll(hstmt,SQL_FETCH_NEXT,0);/* 1-10 */
179 mystmt(hstmt,rc);
180
181 printMessage("1-10, total rows:%ld\n",(long)nrows);
182
183 for (index=1; index<=nrows; index++)
184 {
185 printMessage(" %d ",iarray[index-1]);
186 myassert(iarray[index-1] == index);
187 }
188
189 rc = SQLFetchScroll(hstmt,SQL_FETCH_NEXT,0);/* 10-20 */
190 mystmt(hstmt,rc);
191
192 printMessage("\n10-20, total rows:%ld\n",(long)nrows);
193
194 for (index=1; index<=nrows; index++)
195 {
196 printMessage(" %d ",iarray[index-1]);
197 myassert(iarray[index-1] == index+10);
198 }
199
200 rc = SQLFetchScroll(hstmt,SQL_FETCH_PREV,0);/* 1-10 */
201 mystmt(hstmt,rc);
202
203 printMessage("\n1-10, total rows:%ld\n",(long)nrows);
204
205 for (index=1; index<=nrows; index++)
206 {
207 printMessage(" %d ",iarray[index-1]);
208 myassert(iarray[index-1] == index);
209 }
210
211 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,1);/* 2-11 */
212 mystmt(hstmt,rc);
213
214 printMessage("\n2-12, total rows:%ld\n",(long)nrows);
215
216 for (index=1; index<=nrows; index++)
217 {
218 printMessage(" %d ",iarray[index-1]);
219 myassert(iarray[index-1] == index+1);
220 }
221
222 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-1);/* 1-10 */
223 mystmt(hstmt,rc);
224
225 printMessage("\n1-10, total rows:%ld\n",(long)nrows);
226
227 for (index=1; index<=nrows; index++)
228 {
229 printMessage(" %d",iarray[index-1]);
230 myassert(iarray[index-1] == index);
231 }
232
233 rc = SQLFetchScroll(hstmt,SQL_FETCH_FIRST,0);/* 1-10 */
234 mystmt(hstmt,rc);
235
236 printMessage("\n1-10, total rows:%ld\n",(long)nrows);
237
238 for (index=1; index<=nrows; index++)
239 {
240 printMessage(" %d",iarray[index-1]);
241 myassert(iarray[index-1] == index);
242 }
243
244 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-1);/* BOF */
245 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
246
247 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,1);/* 1-10 */
248 mystmt(hstmt,rc);
249
250 printMessage("\n1-10, total rows:%ld\n",(long)nrows);
251
252 for (index=1; index<=nrows; index++)
253 {
254 printMessage(" %d",iarray[index-1]);
255 myassert(iarray[index-1] == index);
256 }
257
258 SQLFreeStmt(hstmt,SQL_UNBIND);
259 SQLFreeStmt(hstmt,SQL_CLOSE);
260
261 rc = SQLSetStmtAttr(hstmt,SQL_ATTR_ROW_ARRAY_SIZE,(SQLPOINTER)1,0);
262 mystmt(hstmt,rc);
263
264 ok_sql(hstmt, "DROP TABLE IF EXISTS t_array_relative_10");
265
266 return OK;
267 }
268
269
270 /* Testing SQL_FETCH_RELATIVE with row_set_size as 1 */
DECLARE_TEST(t_relative_1)271 DECLARE_TEST(t_relative_1)
272 {
273 SQLRETURN rc;
274 SQLLEN nrows;
275 SQLUINTEGER i;
276 const SQLUINTEGER max_rows=10;
277
278 ok_stmt(hstmt, SQLSetStmtAttr(hstmt, SQL_ATTR_CURSOR_TYPE,
279 (SQLPOINTER)SQL_CURSOR_STATIC, 0));
280
281 ok_sql(hstmt, "DROP TABLE IF EXISTS t_relative_1");
282
283 ok_sql(hstmt, "create table t_relative_1(id int)");
284
285 rc = SQLPrepare(hstmt, (SQLCHAR *)"insert into t_relative_1 values(?)",SQL_NTS);
286 mystmt(hstmt,rc);
287
288 rc = SQLBindParameter(hstmt,1,SQL_PARAM_INPUT, SQL_C_ULONG,
289 SQL_INTEGER,0,0,&i,0,NULL);
290 mystmt(hstmt,rc);
291
292 for ( i = 1; i <= max_rows; i++ )
293 {
294 rc = SQLExecute(hstmt);
295 mystmt(hstmt,rc);
296 }
297
298 SQLFreeStmt(hstmt,SQL_RESET_PARAMS);
299 SQLFreeStmt(hstmt,SQL_CLOSE);
300
301 rc = SQLEndTran(SQL_HANDLE_DBC,hdbc,SQL_COMMIT);
302 mycon(hdbc,rc);
303
304 /* set row_size as 1 */
305 rc = SQLSetStmtAttr(hstmt,SQL_ATTR_ROW_ARRAY_SIZE,(SQLPOINTER)1,0);
306 mystmt(hstmt,rc);
307
308 rc = SQLSetStmtAttr(hstmt,SQL_ATTR_ROWS_FETCHED_PTR,&nrows,0);
309 mystmt(hstmt,rc);
310
311 ok_sql(hstmt, "select * from t_relative_1");
312
313 rc = SQLBindCol(hstmt,1,SQL_C_LONG,&i,0,NULL);
314 mystmt(hstmt,rc);
315
316 /* row 1 */
317 rc = SQLFetchScroll(hstmt,SQL_FETCH_NEXT,0);/* 1 */
318 mystmt(hstmt,rc);
319 my_assert(i==1);
320
321 /* Before start */
322 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-1);/* before start */
323 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
324
325 /* jump to last row */
326 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,max_rows);/* last row */
327 mystmt(hstmt,rc);
328 my_assert(i==max_rows);
329
330 /* jump to last row+1 */
331 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,1);/* after last */
332 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
333
334 /* goto first row */
335 rc = SQLFetchScroll(hstmt,SQL_FETCH_FIRST,1);/* 1 */
336 mystmt(hstmt,rc);
337 my_assert(i==1);
338
339 /* before start */
340 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-1);/* before start */
341 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
342
343 /* goto fifth row */
344 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,5);/* 5 */
345 mystmt(hstmt,rc);
346 my_assert(i==5);
347
348 /* goto after end */
349 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,max_rows);/* after last */
350 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
351
352 /*
353 the scenarios from ODBC spec
354 */
355
356 /* CASE 1 */
357 rc = SQLFetchScroll(hstmt,SQL_FETCH_FIRST,1);/* 1 */
358 mystmt(hstmt,rc);
359 my_assert(i==1);
360
361 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-1);/* before start */
362 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
363
364 /* BeforeStart AND FetchOffset <= 0 */
365 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-20);/* before start */
366 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
367
368 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-1);/* before start */
369 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
370
371 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,0);/* before start */
372 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
373
374 /* case 1: Before start AND FetchOffset > 0 */
375 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,1);/* 1 */
376 mystmt(hstmt,rc);
377 my_assert(i==1);
378
379 /* CASE 2 */
380 rc = SQLFetchScroll(hstmt,SQL_FETCH_LAST,1);/* last row */
381 mystmt(hstmt,rc);
382 my_assert(i==max_rows);
383
384 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,1);/* after end */
385 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
386
387 /* After end AND FetchOffset >= 0 */
388 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,10);/* after end */
389 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
390
391 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,20);/* after end */
392 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
393
394 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,1);/* after end */
395 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
396
397 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,0);/* after end */
398 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
399
400 /* After end AND FetchOffset < 0 */
401 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-1);/* last row */
402 mystmt(hstmt,rc);
403 my_assert(i==max_rows);
404
405
406 /* CASE 3 */
407 rc = SQLFetchScroll(hstmt,SQL_FETCH_FIRST,1);/* first row */
408 mystmt(hstmt,rc);
409 my_assert(i==1);
410
411 /* CurrRowsetStart = 1 AND FetchOffset < 0 */
412 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,0);/* first row */
413 mystmt(hstmt,rc);
414 my_assert(i==1);
415
416 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-1);/* before start */
417 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
418
419 /* CASE 4 */
420 /* CurrRowsetStart > 1 AND CurrRowsetStart + FetchOffset < 1 AND
421 | FetchOffset | > RowsetSize
422 */
423 rc = SQLFetchScroll(hstmt,SQL_FETCH_FIRST,1);/* first row */
424 mystmt(hstmt,rc);
425 my_assert(i==1);
426
427 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,3);/* fourth row */
428 mystmt(hstmt,rc);
429 my_assert(i==4);
430
431 /* the following call satisfies 4 > 1 AND (3-4) < 1 AND |-4| > 1 */
432 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-4);/* before start */
433 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
434
435 /* CASE 5 */
436 /* 1 <= CurrRowsetStart + FetchOffset <= LastResultRow */
437 rc = SQLFetchScroll(hstmt,SQL_FETCH_FIRST,1);/* first row */
438 mystmt(hstmt,rc);
439 my_assert(i==1);
440
441 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,5);/* sixth row */
442 mystmt(hstmt,rc);
443 my_assert(i==6);
444
445 /* 1 <= 6-2 <= 10 */
446 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-2);/* fourth row */
447 mystmt(hstmt,rc);
448 my_assert(i==4);
449
450 /* CASE 6 */
451 /* CurrRowsetStart > 1 AND CurrRowsetStart + FetchOffset < 1 AND
452 | FetchOffset | <= RowsetSize
453 */
454 rc = SQLFetchScroll(hstmt,SQL_FETCH_FIRST,1);/* first row */
455 mystmt(hstmt,rc);
456 my_assert(i==1);
457
458 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,3);/* fourth row */
459 mystmt(hstmt,rc);
460 my_assert(i==4);
461
462 /* 4 >1 AND 4-4 <1 AND |-4| <=10 */
463 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-4);/* before start */
464 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
465
466
467 SQLFreeStmt(hstmt,SQL_UNBIND);
468 SQLFreeStmt(hstmt,SQL_CLOSE);
469
470 rc = SQLSetStmtAttr(hstmt,SQL_ATTR_ROW_ARRAY_SIZE,(SQLPOINTER)1,0);
471 mystmt(hstmt,rc);
472
473 ok_sql(hstmt, "DROP TABLE IF EXISTS t_relative_1");
474
475 return OK;
476 }
477
478
479 /* Testing SQL_FETCH_RELATIVE with row_set_size as 2 */
DECLARE_TEST(t_array_relative_2)480 DECLARE_TEST(t_array_relative_2)
481 {
482 SQLRETURN rc;
483 SQLUINTEGER i;
484 SQLLEN nrows;
485 SQLINTEGER iarray[15];
486 const SQLUINTEGER max_rows=10;
487
488 ok_stmt(hstmt, SQLSetStmtAttr(hstmt, SQL_ATTR_CURSOR_TYPE,
489 (SQLPOINTER)SQL_CURSOR_STATIC, 0));
490
491 ok_sql(hstmt, "DROP TABLE IF EXISTS t_array_relative_2");
492
493 ok_sql(hstmt,"create table t_array_relative_2(id int)");
494
495 rc = SQLPrepare(hstmt, (SQLCHAR *)"insert into t_array_relative_2 values(?)",SQL_NTS);
496 mystmt(hstmt,rc);
497
498 rc = SQLBindParameter(hstmt,1,SQL_PARAM_INPUT, SQL_C_ULONG,
499 SQL_INTEGER,0,0,&i,0,NULL);
500 mystmt(hstmt,rc);
501
502 for ( i = 1; i <= max_rows; i++ )
503 {
504 rc = SQLExecute(hstmt);
505 mystmt(hstmt,rc);
506 }
507
508 SQLFreeStmt(hstmt,SQL_RESET_PARAMS);
509 SQLFreeStmt(hstmt,SQL_CLOSE);
510
511 rc = SQLEndTran(SQL_HANDLE_DBC,hdbc,SQL_COMMIT);
512 mycon(hdbc,rc);
513
514 /* set row_size as 2 */
515 rc = SQLSetStmtAttr(hstmt,SQL_ATTR_ROW_ARRAY_SIZE,(SQLPOINTER)2,0);
516 mystmt(hstmt,rc);
517
518 rc = SQLSetStmtAttr(hstmt,SQL_ATTR_ROWS_FETCHED_PTR,&nrows,0);
519 mystmt(hstmt,rc);
520
521 ok_sql(hstmt, "select * from t_array_relative_2");
522
523 rc = SQLBindCol(hstmt,1,SQL_C_LONG,iarray,0,NULL);
524 mystmt(hstmt,rc);
525
526 /* row 1 */
527 rc = SQLFetchScroll(hstmt,SQL_FETCH_NEXT,0);/* 1 */
528 mystmt(hstmt,rc);
529 my_assert(nrows == 2);
530 my_assert(iarray[0]==1);
531 my_assert(iarray[1]==2);
532
533
534 /* Before start */
535 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-1);/* before start */
536 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
537
538 /* jump to last row */
539 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,max_rows);/* last row */
540 mystmt(hstmt,rc);
541 my_assert(nrows == 1);
542 my_assert(iarray[0]==max_rows);
543
544 /* jump to last row+1 */
545 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,1);/* after last */
546 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
547
548 /* goto first row */
549 rc = SQLFetchScroll(hstmt,SQL_FETCH_FIRST,1);/* 1 */
550 mystmt(hstmt,rc);
551 my_assert(nrows == 2);
552 my_assert(iarray[0]==1);
553 my_assert(iarray[1]==2);
554
555 /* before start */
556 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-1);/* before start */
557 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
558
559 /* goto fifth row */
560 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,5);/* 5 */
561 mystmt(hstmt,rc);
562 my_assert(nrows == 2);
563 my_assert(iarray[0]==5);
564 my_assert(iarray[1]==6);
565
566 /* goto after end */
567 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,max_rows);/* after last */
568 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
569
570 /*
571 the scenarios from ODBC spec
572 */
573
574 /* CASE 1 */
575 rc = SQLFetchScroll(hstmt,SQL_FETCH_FIRST,1);/* 1 */
576 mystmt(hstmt,rc);
577 my_assert(nrows == 2);
578 my_assert(iarray[0]==1);
579 my_assert(iarray[1]==2);
580
581 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-1);/* before start */
582 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
583
584 /* BeforeStart AND FetchOffset <= 0 */
585 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-20);/* before start */
586 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
587
588 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-1);/* before start */
589 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
590
591 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,0);/* before start */
592 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
593
594 /* case 1: Before start AND FetchOffset > 0 */
595 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,1);/* 1 */
596 mystmt(hstmt,rc);
597 my_assert(nrows == 2);
598 my_assert(iarray[0]==1);
599 my_assert(iarray[1]==2);
600
601 /* CASE 2 */
602 rc = SQLFetchScroll(hstmt,SQL_FETCH_LAST,1);/* last row */
603 mystmt(hstmt,rc);
604 my_assert(nrows == 2);
605 my_assert(iarray[0]==max_rows-1);
606 my_assert(iarray[1]==max_rows);
607
608 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,1);/* last row */
609 mystmt(hstmt,rc);
610 my_assert(nrows == 1);
611 my_assert(iarray[0]==max_rows);
612
613 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,1);/* after last row */
614 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
615
616 /* After end AND FetchOffset >= 0 */
617 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,10);/* after end */
618 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
619
620 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,20);/* after end */
621 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
622
623 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,1);/* after end */
624 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
625
626 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,0);/* after end */
627 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
628
629 /* After end AND FetchOffset < 0 */
630 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-1);/* last row */
631 mystmt(hstmt,rc);
632 my_assert(nrows == 1);
633 my_assert(iarray[0]==max_rows);
634
635
636 /* CASE 3 */
637 rc = SQLFetchScroll(hstmt,SQL_FETCH_FIRST,1);/* first row */
638 mystmt(hstmt,rc);
639 my_assert(nrows == 2);
640 my_assert(iarray[0]==1);
641 my_assert(iarray[1]==2);
642
643 /* CurrRowsetStart = 1 AND FetchOffset < 0 */
644 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,0);/* first row */
645 mystmt(hstmt,rc);
646 my_assert(nrows == 2);
647 my_assert(iarray[0]==1);
648 my_assert(iarray[1]==2);
649
650 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-1);/* before start */
651 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
652
653 /* CASE 4 */
654 /* CurrRowsetStart > 1 AND CurrRowsetStart + FetchOffset < 1 AND
655 | FetchOffset | > RowsetSize
656 */
657 rc = SQLFetchScroll(hstmt,SQL_FETCH_FIRST,1);/* first row */
658 mystmt(hstmt,rc);
659 my_assert(nrows == 2);
660 my_assert(iarray[0]==1);
661 my_assert(iarray[1]==2);
662
663 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,3);/* fourth row */
664 mystmt(hstmt,rc);
665 my_assert(nrows == 2);
666 my_assert(iarray[0]==4);
667 my_assert(iarray[1]==5);
668
669 /* the following call satisfies 4 > 1 AND (3-4) < 1 AND |-4| > 1 */
670 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-4);/* before start */
671 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
672
673 /* CASE 5 */
674 /* 1 <= CurrRowsetStart + FetchOffset <= LastResultRow */
675 rc = SQLFetchScroll(hstmt,SQL_FETCH_FIRST,1);/* first row */
676 mystmt(hstmt,rc);
677 my_assert(nrows == 2);
678 my_assert(iarray[0]==1);
679 my_assert(iarray[1]==2);
680
681 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,5);/* sixth row */
682 mystmt(hstmt,rc);
683 my_assert(nrows == 2);
684 my_assert(iarray[0]==6);
685 my_assert(iarray[1]==7);
686
687 /* 1 <= 6-2 <= 10 */
688 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-2);/* fourth row */
689 mystmt(hstmt,rc);
690 my_assert(nrows == 2);
691 my_assert(iarray[0]==4);
692 my_assert(iarray[1]==5);
693
694 /* CASE 6 */
695 /* CurrRowsetStart > 1 AND CurrRowsetStart + FetchOffset < 1 AND
696 | FetchOffset | <= RowsetSize
697 */
698 rc = SQLFetchScroll(hstmt,SQL_FETCH_FIRST,1);/* first row */
699 mystmt(hstmt,rc);
700 my_assert(nrows == 2);
701 my_assert(iarray[0]==1);
702 my_assert(iarray[1]==2);
703
704 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,3);/* fourth row */
705 mystmt(hstmt,rc);
706 my_assert(nrows == 2);
707 my_assert(iarray[0]==4);
708 my_assert(iarray[1]==5);
709
710 /* 4 >1 AND 4-4 <1 AND |-4| <=10 */
711 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-4);/* before start */
712 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
713
714
715 SQLFreeStmt(hstmt,SQL_UNBIND);
716 SQLFreeStmt(hstmt,SQL_CLOSE);
717
718 /***
719 for rowset_size > max_rows...
720 */
721 rc = SQLSetStmtAttr(hstmt,SQL_ATTR_ROW_ARRAY_SIZE,(SQLPOINTER)25,0);
722 mystmt(hstmt,rc);
723
724 rc = SQLSetStmtAttr(hstmt,SQL_ATTR_ROWS_FETCHED_PTR,&nrows,0);
725 mystmt(hstmt,rc);
726
727 ok_sql(hstmt, "select * from t_array_relative_2");
728 mystmt(hstmt,rc);
729
730 rc = SQLBindCol(hstmt,1,SQL_C_LONG,iarray,0,NULL);
731 mystmt(hstmt,rc);
732
733 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,2);/* 2 */
734 mystmt(hstmt,rc);
735 my_assert(nrows == max_rows-1);
736 my_assert(iarray[0]==2);
737 my_assert(iarray[max_rows-2]==10);
738
739 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,1);/* 1 */
740 mystmt(hstmt,rc);
741 my_assert(nrows == max_rows);
742 my_assert(iarray[0]==1);
743 my_assert(iarray[max_rows-1]==max_rows);
744
745 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,5);/* 5 */
746 mystmt(hstmt,rc);
747 my_assert(nrows == max_rows-4);
748 my_assert(iarray[0]==5);
749 my_assert(iarray[max_rows-5]==max_rows);
750
751
752 /* CurrRowsetStart > 1 AND
753 CurrRowsetStart + FetchOffset < 1 AND
754 | FetchOffset | > RowsetSize
755
756 ==> before start
757 */
758 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-30);/* 1 */
759 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
760
761 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,2);/* 2 */
762 mystmt(hstmt,rc);
763 my_assert(nrows == max_rows-1);
764 my_assert(iarray[0]==2);
765 my_assert(iarray[max_rows-2]==10);
766
767 /* CurrRowsetStart > 1 AND
768 CurrRowsetStart + FetchOffset < 1 AND
769 | FetchOffset | <= RowsetSize
770
771 ==> 1
772 */
773 rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-13);/* 1 */
774 mystmt(hstmt,rc);
775 my_assert(nrows == max_rows);
776 my_assert(iarray[0]==1);
777 my_assert(iarray[max_rows-1]==max_rows);
778
779 SQLFreeStmt(hstmt,SQL_UNBIND);
780 SQLFreeStmt(hstmt,SQL_CLOSE);
781
782 rc = SQLSetStmtAttr(hstmt,SQL_ATTR_ROW_ARRAY_SIZE,(SQLPOINTER)1,0);
783 mystmt(hstmt,rc);
784
785 ok_sql(hstmt, "DROP TABLE IF EXISTS t_array_relative_2");
786
787 return OK;
788 }
789
790
791 /* Testing SQL_FETCH_ABSOLUTE with row_set_size as 1 */
DECLARE_TEST(t_absolute_1)792 DECLARE_TEST(t_absolute_1)
793 {
794 SQLRETURN rc;
795 SQLLEN nrows;
796 SQLUINTEGER i;
797 const SQLUINTEGER max_rows=10;
798
799 ok_stmt(hstmt, SQLSetStmtAttr(hstmt, SQL_ATTR_CURSOR_TYPE,
800 (SQLPOINTER)SQL_CURSOR_STATIC, 0));
801
802 ok_sql(hstmt, "DROP TABLE IF EXISTS t_absolute_1");
803
804 ok_sql(hstmt, "create table t_absolute_1(id int)");
805
806 rc = SQLPrepare(hstmt, (SQLCHAR *)"insert into t_absolute_1 values(?)",SQL_NTS);
807 mystmt(hstmt,rc);
808
809 rc = SQLBindParameter(hstmt,1,SQL_PARAM_INPUT, SQL_C_ULONG,
810 SQL_INTEGER,0,0,&i,0,NULL);
811 mystmt(hstmt,rc);
812
813 for ( i = 1; i <= max_rows; i++ )
814 {
815 rc = SQLExecute(hstmt);
816 mystmt(hstmt,rc);
817 }
818
819 SQLFreeStmt(hstmt,SQL_RESET_PARAMS);
820 SQLFreeStmt(hstmt,SQL_CLOSE);
821
822 rc = SQLEndTran(SQL_HANDLE_DBC,hdbc,SQL_COMMIT);
823 mycon(hdbc,rc);
824
825 /* set row_size as 1 */
826 rc = SQLSetStmtAttr(hstmt,SQL_ATTR_ROW_ARRAY_SIZE,(SQLPOINTER)1,0);
827 mystmt(hstmt,rc);
828
829 rc = SQLSetStmtAttr(hstmt,SQL_ATTR_ROWS_FETCHED_PTR,&nrows,0);
830 mystmt(hstmt,rc);
831
832 ok_sql(hstmt, "select * from t_absolute_1");
833
834 rc = SQLBindCol(hstmt,1,SQL_C_LONG,&i,0,NULL);
835 mystmt(hstmt,rc);
836
837 /* row 1 */
838 rc = SQLFetchScroll(hstmt,SQL_FETCH_NEXT,0);/* 1 */
839 mystmt(hstmt,rc);
840 my_assert(i==1);
841
842 /* Before start */
843 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,-12);/* before start */
844 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
845
846 /* jump to last row */
847 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,max_rows);/* last row */
848 mystmt(hstmt,rc);
849 my_assert(i==max_rows);
850
851 /* jump to last row+1 */
852 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,max_rows+1);/* after last */
853 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
854
855 /* goto first row */
856 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,1);/* 1 */
857 mystmt(hstmt,rc);
858 my_assert(i==1);
859
860 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,0);/* before start */
861 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
862
863 /* before start */
864 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,-15);/* before start */
865 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
866
867 /* goto fifth row */
868 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,5);/* 5 */
869 mystmt(hstmt,rc);
870 my_assert(i==5);
871
872 /* goto after end */
873 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,max_rows+5);/* after last */
874 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
875
876 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,0);/* before start */
877 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
878
879 /*
880 the scenarios from ODBC spec
881 */
882
883 /* CASE 1 */
884 rc = SQLFetchScroll(hstmt,SQL_FETCH_FIRST,1);/* 1 */
885 mystmt(hstmt,rc);
886 my_assert(i==1);
887
888 /* FetchOffset < 0 AND | FetchOffset | <= LastResultRow ,
889 ==> should yield LastResultRow + FetchOffset + 1
890 */
891 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,-1);
892 mystmt(hstmt,rc);
893 my_assert(i==(max_rows-1+1));
894
895 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,-4);
896 mystmt(hstmt,rc);
897 my_assert(i==(max_rows-4+1));
898
899 /* CASE 2 :
900 FetchOffset < 0 AND
901 | FetchOffset | > LastResultRow AND
902 | FetchOffset | > RowsetSize
903
904 ==> Before start
905 */
906 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,-11);
907 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
908
909 /* CASE 4:
910
911 FetchOffset = 0
912
913 ==> before start
914 */
915 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,0);/* before start */
916 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
917
918 /* CASE 5:
919 1 <= FetchOffset <= LastResultRow
920
921 ==> FetchOffset
922 */
923 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,2);/* 2 */
924 mystmt(hstmt,rc);
925 my_assert(i==2);
926
927 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,9);/* 9 */
928 mystmt(hstmt,rc);
929 my_assert(i==9);
930
931 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,6);/* 6 */
932 mystmt(hstmt,rc);
933 my_assert(i==6);
934
935 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,0);/* BOF */
936 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
937
938 /* CASE 6:
939 FetchOffset > LastResultRow
940
941 ==> after end
942 */
943 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,max_rows);/* last row */
944 mystmt(hstmt,rc);
945 my_assert(i==max_rows);
946
947 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,max_rows+1);/* after end */
948 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
949
950 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,max_rows+max_rows);/* after end */
951 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
952
953 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,5);/* 5 */
954 mystmt(hstmt,rc);
955 my_assert(i==5);
956
957 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,12);/* 5 */
958 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
959
960 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,0);/* before start */
961 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
962
963 SQLFreeStmt(hstmt,SQL_UNBIND);
964 SQLFreeStmt(hstmt,SQL_CLOSE);
965
966 rc = SQLSetStmtAttr(hstmt,SQL_ATTR_ROW_ARRAY_SIZE,(SQLPOINTER)1,0);
967 mystmt(hstmt,rc);
968
969 ok_sql(hstmt, "DROP TABLE IF EXISTS t_absolute_1");
970
971 return OK;
972 }
973
974
975 /* Testing SQL_FETCH_ABSOLUTE with row_set_size as 2 */
DECLARE_TEST(t_absolute_2)976 DECLARE_TEST(t_absolute_2)
977 {
978 SQLRETURN rc;
979 SQLLEN nrows;
980 SQLINTEGER iarray[15];
981 const SQLUINTEGER max_rows=10;
982 SQLUINTEGER i;
983
984 ok_stmt(hstmt, SQLSetStmtAttr(hstmt, SQL_ATTR_CURSOR_TYPE,
985 (SQLPOINTER)SQL_CURSOR_STATIC, 0));
986
987 ok_sql(hstmt, "DROP TABLE IF EXISTS t_absolute_2");
988
989 ok_sql(hstmt, "create table t_absolute_2(id int)");
990
991 rc = SQLPrepare(hstmt, (SQLCHAR *)"insert into t_absolute_2 values(?)",SQL_NTS);
992 mystmt(hstmt,rc);
993
994 rc = SQLBindParameter(hstmt,1,SQL_PARAM_INPUT, SQL_C_ULONG,
995 SQL_INTEGER,0,0,&i,0,NULL);
996 mystmt(hstmt,rc);
997
998 for ( i = 1; i <= max_rows; i++ )
999 {
1000 rc = SQLExecute(hstmt);
1001 mystmt(hstmt,rc);
1002 }
1003
1004 SQLFreeStmt(hstmt,SQL_RESET_PARAMS);
1005 SQLFreeStmt(hstmt,SQL_CLOSE);
1006
1007 rc = SQLEndTran(SQL_HANDLE_DBC,hdbc,SQL_COMMIT);
1008 mycon(hdbc,rc);
1009
1010 /* set row_size as 1 */
1011 rc = SQLSetStmtAttr(hstmt,SQL_ATTR_ROW_ARRAY_SIZE,(SQLPOINTER)2,0);
1012 mystmt(hstmt,rc);
1013
1014 rc = SQLSetStmtAttr(hstmt,SQL_ATTR_ROWS_FETCHED_PTR,&nrows,0);
1015 mystmt(hstmt,rc);
1016
1017 ok_sql(hstmt, "select * from t_absolute_2");
1018 mystmt(hstmt,rc);
1019
1020 rc = SQLBindCol(hstmt,1,SQL_C_LONG,iarray,0,NULL);
1021 mystmt(hstmt,rc);
1022
1023 /* row 1 */
1024 rc = SQLFetchScroll(hstmt,SQL_FETCH_NEXT,0);/* 1 */
1025 mystmt(hstmt,rc);
1026 my_assert(nrows == 2);
1027 my_assert(iarray[0]==1);
1028 my_assert(iarray[1]==2);
1029
1030 /* Before start */
1031 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,-12);/* before start */
1032 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
1033
1034 /* jump to last row */
1035 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,max_rows);/* last row */
1036 mystmt(hstmt,rc);
1037 my_assert(nrows == 1);
1038 my_assert(iarray[0]==max_rows);
1039
1040
1041 /* jump to last row+1 */
1042 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,max_rows+1);/* after last */
1043 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
1044
1045 /* goto first row */
1046 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,1);/* 1 */
1047 mystmt(hstmt,rc);
1048 my_assert(nrows == 2);
1049 my_assert(iarray[0]==1);
1050 my_assert(iarray[1]==2);
1051
1052 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,0);/* before start */
1053 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
1054
1055 /* before start */
1056 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,-15);/* before start */
1057 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
1058
1059 /* goto fifth row */
1060 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,5);/* 5 */
1061 mystmt(hstmt,rc);
1062 my_assert(nrows == 2);
1063 my_assert(iarray[0]==5);
1064 my_assert(iarray[1]==6);
1065
1066 /* goto after end */
1067 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,max_rows+5);/* after last */
1068 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
1069
1070 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,0);/* before start */
1071 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
1072
1073 /*
1074 the scenarios from ODBC spec
1075 */
1076
1077 /* CASE 1 */
1078 rc = SQLFetchScroll(hstmt,SQL_FETCH_FIRST,1);/* 1 */
1079 mystmt(hstmt,rc);
1080 my_assert(nrows == 2);
1081 my_assert(iarray[0]==1);
1082 my_assert(iarray[1]==2);
1083
1084 /* FetchOffset < 0 AND | FetchOffset | <= LastResultRow ,
1085 ==> should yield LastResultRow + FetchOffset + 1
1086 */
1087 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,-1);
1088 mystmt(hstmt,rc);
1089 my_assert(nrows == 1);
1090 my_assert(iarray[0]==(max_rows-1+1));
1091
1092 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,-4);
1093 mystmt(hstmt,rc);
1094 my_assert(nrows == 2);
1095 my_assert(iarray[0]==(max_rows-4+1));
1096 my_assert(iarray[1]==(max_rows-4+1+1));
1097
1098 /* CASE 2 :
1099 FetchOffset < 0 AND
1100 | FetchOffset | > LastResultRow AND
1101 | FetchOffset | > RowsetSize
1102
1103 ==> Before start
1104 */
1105 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,-11);
1106 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
1107
1108 /* CASE 4:
1109
1110 FetchOffset = 0
1111
1112 ==> before start
1113 */
1114 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,0);/* before start */
1115 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
1116
1117 /* CASE 5:
1118 1 <= FetchOffset <= LastResultRow
1119
1120 ==> FetchOffset
1121 */
1122 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,2);/* 2 */
1123 mystmt(hstmt,rc);
1124 my_assert(nrows == 2);
1125 my_assert(iarray[0]==2);
1126 my_assert(iarray[1]==3);
1127
1128 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,9);/* 9 */
1129 mystmt(hstmt,rc);
1130 my_assert(nrows == 2);
1131 my_assert(iarray[0]==9);
1132 my_assert(iarray[1]==10);
1133
1134 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,6);/* 6 */
1135 mystmt(hstmt,rc);
1136 my_assert(nrows == 2);
1137 my_assert(iarray[0]==6);
1138 my_assert(iarray[1]==7);
1139
1140 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,0);/* BOF */
1141 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
1142
1143 /* CASE 6:
1144 FetchOffset > LastResultRow
1145
1146 ==> after end
1147 */
1148 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,max_rows);/* last row */
1149 mystmt(hstmt,rc);
1150 my_assert(nrows == 1);
1151 my_assert(iarray[0]==max_rows);
1152
1153 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,max_rows+1);/* after end */
1154 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
1155
1156 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,max_rows+max_rows);/* after end */
1157 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
1158
1159 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,5);/* 5 */
1160 mystmt(hstmt,rc);
1161 my_assert(nrows == 2);
1162 my_assert(iarray[0]==5);
1163 my_assert(iarray[1]==6);
1164
1165 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,12);/* 5 */
1166 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
1167
1168 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,0);/* before start */
1169 mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
1170
1171 SQLFreeStmt(hstmt,SQL_UNBIND);
1172 SQLFreeStmt(hstmt,SQL_CLOSE);
1173
1174 rc = SQLSetStmtAttr(hstmt,SQL_ATTR_ROW_ARRAY_SIZE,(SQLPOINTER)1,0);
1175 mystmt(hstmt,rc);
1176
1177 /* for rowset_size > max_rows...*/
1178 rc = SQLSetStmtAttr(hstmt,SQL_ATTR_ROW_ARRAY_SIZE,(SQLPOINTER)25,0);
1179 mystmt(hstmt,rc);
1180
1181 rc = SQLSetStmtAttr(hstmt,SQL_ATTR_ROWS_FETCHED_PTR,&nrows,0);
1182 mystmt(hstmt,rc);
1183
1184 ok_sql(hstmt, "select * from t_absolute_2");
1185 mystmt(hstmt,rc);
1186
1187 rc = SQLBindCol(hstmt,1,SQL_C_LONG,iarray,0,NULL);
1188 mystmt(hstmt,rc);
1189
1190 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,2);/* 2 */
1191 mystmt(hstmt,rc);
1192 my_assert(nrows == max_rows-1);
1193 my_assert(iarray[0]==2);
1194 my_assert(iarray[max_rows-2]==10);
1195
1196 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,1);/* 1 */
1197 mystmt(hstmt,rc);
1198 my_assert(nrows == max_rows);
1199 my_assert(iarray[0]==1);
1200 my_assert(iarray[max_rows-1]==max_rows);
1201
1202 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,5);/* 5 */
1203 mystmt(hstmt,rc);
1204 my_assert(nrows == max_rows-4);
1205 my_assert(iarray[0]==5);
1206 my_assert(iarray[max_rows-5]==max_rows);
1207
1208
1209 /* FetchOffset < 0 AND
1210 | FetchOffset | > LastResultRow AND
1211 | FetchOffset | <= RowsetSize
1212 */
1213 rc = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,-13);/* 1 */
1214 mystmt(hstmt,rc);
1215 my_assert(nrows == max_rows);
1216 my_assert(iarray[0]==1);
1217 my_assert(iarray[max_rows-1]==max_rows);
1218
1219 SQLFreeStmt(hstmt,SQL_UNBIND);
1220 SQLFreeStmt(hstmt,SQL_CLOSE);
1221
1222 rc = SQLSetStmtAttr(hstmt,SQL_ATTR_ROW_ARRAY_SIZE,(SQLPOINTER)1,0);
1223 mystmt(hstmt,rc);
1224
1225 ok_sql(hstmt, "DROP TABLE IF EXISTS t_absolute_2");
1226
1227 return OK;
1228 }
1229
1230
1231 BEGIN_TESTS
1232 ADD_TEST(t_scroll)
1233 ADD_TEST(t_array_relative_10)
1234 ADD_TEST(t_array_relative_2)
1235 ADD_TEST(t_relative_1)
1236 ADD_TEST(t_absolute_1)
1237 ADD_TEST(t_absolute_2)
1238 END_TESTS
1239
1240
1241 RUN_TESTS
1242