25
25
/* Testing SQL_FETCH_RELATIVE with row_set_size as 10 */
26
26
DECLARE_TEST(t_relative)
30
SQLINTEGER iarray[15];
34
SQLExecDirect(hstmt,(SQLCHAR *)"drop table t_relative",SQL_NTS);
36
rc = SQLExecDirect(hstmt,(SQLCHAR *)"create table t_relative(id int,name char(20))",SQL_NTS);
39
rc = SQLPrepare(hstmt,(SQLCHAR *)"insert into t_relative values(?,?)",SQL_NTS);
42
rc = SQLBindParameter(hstmt,1,SQL_PARAM_INPUT, SQL_C_ULONG,
43
SQL_INTEGER,0,0,&i,0,NULL);
45
rc = SQLBindParameter(hstmt,2,SQL_PARAM_INPUT, SQL_C_CHAR,
46
SQL_CHAR,20,0,name,20,NULL);
49
for ( i = 1; i <= 50; i++ )
51
sprintf(name,"my%d",i);
52
rc = SQLExecute(hstmt);
56
SQLFreeStmt(hstmt,SQL_RESET_PARAMS);
57
SQLFreeStmt(hstmt,SQL_CLOSE);
59
rc = SQLEndTran(SQL_HANDLE_DBC,hdbc,SQL_COMMIT);
62
/* set row size as 10 */
63
rc = SQLSetStmtAttr(hstmt,SQL_ATTR_ROW_ARRAY_SIZE,(SQLPOINTER)10,0);
66
rc = SQLSetStmtAttr(hstmt,SQL_ATTR_ROWS_FETCHED_PTR,&nrows,0);
69
rc = SQLExecDirect(hstmt,(SQLCHAR *)"select * from t_relative",SQL_NTS);
72
rc = SQLBindCol(hstmt,1,SQL_C_LONG,&iarray,0,NULL);
75
rc = SQLFetchScroll(hstmt,SQL_FETCH_NEXT,0);/* 1-10 */
78
printMessage("1-10, total rows:%ld\n",nrows);
79
myassert(nrows == 10);
81
for (index=1; index<=nrows; index++)
83
printMessage("\n %d",iarray[index-1]);
84
myassert(iarray[index-1] == index);
87
rc = SQLFetchScroll(hstmt,SQL_FETCH_NEXT,0);/* 10-20 */
90
printMessage("\n10-20, total rows:%d\n",nrows);
92
for (index=1; index<=nrows; index++)
94
printMessage(" %d ",iarray[index-1]);
95
myassert(iarray[index-1] == index+10);
98
rc = SQLFetchScroll(hstmt,SQL_FETCH_PREV,0);/* 1-10 */
101
printMessage("\n1-10, total rows:%d\n",nrows);
103
for (index=1; index<=nrows; index++)
105
printMessage(" %d ",iarray[index-1]);
106
myassert(iarray[index-1] == index);
109
rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,1);/* 2-11 */
112
printMessage("\n2-12, total rows:%d\n",nrows);
114
for (index=1; index<=nrows; index++)
116
printMessage(" %d ",iarray[index-1]);
117
myassert(iarray[index-1] == index+1);
120
rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-1);/* 1-10 */
123
printMessage("\n1-10, total rows:%d\n",nrows);
125
for (index=1; index<=nrows; index++)
127
printMessage(" %d",iarray[index-1]);
128
myassert(iarray[index-1] == index);
131
rc = SQLFetchScroll(hstmt,SQL_FETCH_FIRST,0);/* 1-10 */
134
printMessage("\n1-10, total rows:%d\n",nrows);
136
for (index=1; index<=nrows; index++)
138
printMessage(" %d",iarray[index-1]);
139
myassert(iarray[index-1] == index);
142
rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-1);/* BOF */
143
mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
145
rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,1);/* 1-10 */
148
printMessage("\n1-10, total rows:%d\n",nrows);
150
for (index=1; index<=nrows; index++)
152
printMessage(" %d",iarray[index-1]);
153
myassert(iarray[index-1] == index);
156
SQLFreeStmt(hstmt,SQL_UNBIND);
157
SQLFreeStmt(hstmt,SQL_CLOSE);
159
rc = SQLSetStmtAttr(hstmt,SQL_ATTR_ROW_ARRAY_SIZE,(SQLPOINTER)1,0);
28
SQLUINTEGER i, iarray[15];
32
ok_stmt(hstmt, SQLSetStmtAttr(hstmt, SQL_ATTR_CURSOR_TYPE,
33
(SQLPOINTER)SQL_CURSOR_STATIC, 0));
35
ok_sql(hstmt, "DROP TABLE IF EXISTS t_relative");
36
ok_sql(hstmt, "CREATE TABLE t_relative (id INT, name CHAR(20))");
38
ok_stmt(hstmt, SQLPrepare(hstmt,
39
(SQLCHAR *)"INSERT INTO t_relative VALUES (?,?)",
42
ok_stmt(hstmt, SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_ULONG,
43
SQL_INTEGER, 0, 0, &i, 0, NULL));
44
ok_stmt(hstmt, SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR,
45
SQL_CHAR, 20, 0, name, 20, NULL));
47
for (i= 1; i <= 50; i++)
49
sprintf((char *)name, "my%d", i);
50
ok_stmt(hstmt, SQLExecute(hstmt));
53
ok_stmt(hstmt, SQLFreeStmt(hstmt, SQL_RESET_PARAMS));
54
ok_stmt(hstmt, SQLFreeStmt(hstmt, SQL_CLOSE));
56
/* set row size as 10 */
57
ok_stmt(hstmt, SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE,
60
ok_stmt(hstmt, SQLSetStmtAttr(hstmt, SQL_ATTR_ROWS_FETCHED_PTR, &nrows, 0));
62
ok_sql(hstmt, "SELECT * FROM t_relative");
64
ok_stmt(hstmt, SQLBindCol(hstmt, 1, SQL_C_ULONG, &iarray, 0, NULL));
66
ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_NEXT, 0)); /* 1-10 */
69
for (index= 1; index <= nrows; index++)
70
is_num(iarray[index - 1], index);
72
ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_NEXT, 0)); /* 10-20 */
75
for (index= 1; index <= nrows; index++)
76
is_num(iarray[index - 1], index + 10);
78
ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_PREV, 0)); /* 1-10 */
81
for (index= 1; index <= nrows; index++)
82
is_num(iarray[index - 1], index);
84
ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, 1)); /* 2-11 */
87
for (index= 1; index <= nrows; index++)
88
is_num(iarray[index - 1], index + 1);
90
ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, -1)); /* 1-10 */
93
for (index= 1; index <= nrows; index++)
94
is_num(iarray[index - 1], index);
96
ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_FIRST, 0)); /* 1-10 */
99
for (index= 1; index <= nrows; index++)
100
is_num(iarray[index - 1], index);
102
expect_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, -1),
103
SQL_NO_DATA_FOUND); /* BOF */
105
ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, 1)); /* 1-10 */
108
for (index= 1; index <= nrows; index++)
109
is_num(iarray[index - 1], index);
111
ok_stmt(hstmt, SQLFreeStmt(hstmt, SQL_UNBIND));
112
ok_stmt(hstmt, SQLFreeStmt(hstmt, SQL_CLOSE));
115
ok_stmt(hstmt, SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE,
118
ok_sql(hstmt, "DROP TABLE IF EXISTS t_relative");
166
124
/* Testing SQL_FETCH_RELATIVE with row_set_size as 1 */
167
125
DECLARE_TEST(t_relative1)
172
const int max_rows=10;
174
SQLExecDirect(hstmt,(SQLCHAR *)"drop table t_relative1",SQL_NTS);
176
rc = SQLExecDirect(hstmt,(SQLCHAR *)"create table t_relative1(id int)",SQL_NTS);
179
rc = SQLPrepare(hstmt,(SQLCHAR *)"insert into t_relative1 values(?)",SQL_NTS);
182
rc = SQLBindParameter(hstmt,1,SQL_PARAM_INPUT, SQL_C_ULONG,
183
SQL_INTEGER,0,0,&i,0,NULL);
186
for ( i = 1; i <= max_rows; i++ )
188
rc = SQLExecute(hstmt);
192
SQLFreeStmt(hstmt,SQL_RESET_PARAMS);
193
SQLFreeStmt(hstmt,SQL_CLOSE);
195
rc = SQLEndTran(SQL_HANDLE_DBC,hdbc,SQL_COMMIT);
198
/* set row_size as 1 */
199
rc = SQLSetStmtAttr(hstmt,SQL_ATTR_ROW_ARRAY_SIZE,(SQLPOINTER)1,0);
202
rc = SQLSetStmtAttr(hstmt,SQL_ATTR_ROWS_FETCHED_PTR,&nrows,0);
205
rc = SQLExecDirect(hstmt,(SQLCHAR *)"select * from t_relative1",SQL_NTS);
208
rc = SQLBindCol(hstmt,1,SQL_C_LONG,&i,0,NULL);
212
rc = SQLFetchScroll(hstmt,SQL_FETCH_NEXT,0);/* 1 */
217
rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-1);/* before start */
218
mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
220
/* jump to last row */
221
rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,max_rows);/* last row */
223
my_assert(i==max_rows);
225
/* jump to last row+1 */
226
rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,1);/* after last */
227
mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
230
rc = SQLFetchScroll(hstmt,SQL_FETCH_FIRST,1);/* 1 */
235
rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-1);/* before start */
236
mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
239
rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,5);/* 5 */
244
rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,max_rows);/* after last */
245
mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
248
the scenarios from ODBC spec
252
rc = SQLFetchScroll(hstmt,SQL_FETCH_FIRST,1);/* 1 */
256
rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-1);/* before start */
257
mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
259
/* BeforeStart AND FetchOffset <= 0 */
260
rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-20);/* before start */
261
mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
263
rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-1);/* before start */
264
mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
266
rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,0);/* before start */
267
mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
269
/* case 1: Before start AND FetchOffset > 0 */
270
rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,1);/* 1 */
275
rc = SQLFetchScroll(hstmt,SQL_FETCH_LAST,1);/* last row */
277
my_assert(i==max_rows);
279
rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,1);/* after end */
280
mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
282
/* After end AND FetchOffset >= 0 */
283
rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,10);/* after end */
284
mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
286
rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,20);/* after end */
287
mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
289
rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,1);/* after end */
290
mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
292
rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,0);/* after end */
293
mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
295
/* After end AND FetchOffset < 0 */
296
rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-1);/* last row */
298
my_assert(i==max_rows);
302
rc = SQLFetchScroll(hstmt,SQL_FETCH_FIRST,1);/* first row */
306
/* CurrRowsetStart = 1 AND FetchOffset < 0 */
307
rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,0);/* first row */
311
rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-1);/* before start */
312
mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
315
/* CurrRowsetStart > 1 AND CurrRowsetStart + FetchOffset < 1 AND
316
| FetchOffset | > RowsetSize
318
rc = SQLFetchScroll(hstmt,SQL_FETCH_FIRST,1);/* first row */
322
rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,3);/* fourth row */
326
/* the following call satisfies 4 > 1 AND (3-4) < 1 AND |-4| > 1 */
327
rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-4);/* before start */
328
mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
331
/* 1 <= CurrRowsetStart + FetchOffset <= LastResultRow */
332
rc = SQLFetchScroll(hstmt,SQL_FETCH_FIRST,1);/* first row */
336
rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,5);/* sixth row */
341
rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-2);/* fourth row */
346
/* CurrRowsetStart > 1 AND CurrRowsetStart + FetchOffset < 1 AND
347
| FetchOffset | <= RowsetSize
349
rc = SQLFetchScroll(hstmt,SQL_FETCH_FIRST,1);/* first row */
353
rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,3);/* fourth row */
357
/* 4 >1 AND 4-4 <1 AND |-4| <=10 */
358
rc = SQLFetchScroll(hstmt,SQL_FETCH_RELATIVE,-4);/* before start */
359
mystmt_err(hstmt,rc==SQL_NO_DATA_FOUND,rc);
362
SQLFreeStmt(hstmt,SQL_UNBIND);
363
SQLFreeStmt(hstmt,SQL_CLOSE);
365
rc = SQLSetStmtAttr(hstmt,SQL_ATTR_ROW_ARRAY_SIZE,(SQLPOINTER)1,0);
129
const SQLUINTEGER max_rows= 10;
131
ok_stmt(hstmt, SQLSetStmtAttr(hstmt, SQL_ATTR_CURSOR_TYPE,
132
(SQLPOINTER)SQL_CURSOR_STATIC, 0));
134
ok_sql(hstmt, "DROP TABLE IF EXISTS t_relative1");
135
ok_sql(hstmt, "CREATE TABLE t_relative1 (id INT)");
137
ok_stmt(hstmt, SQLPrepare(hstmt,
138
(SQLCHAR *)"INSERT INTO t_relative1 VALUES (?)",
141
ok_stmt(hstmt, SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_ULONG,
142
SQL_INTEGER, 0, 0, &i, 0, NULL));
144
for (i= 1; i <= max_rows; i++)
146
ok_stmt(hstmt, SQLExecute(hstmt));
149
ok_stmt(hstmt, SQLFreeStmt(hstmt, SQL_RESET_PARAMS));
150
ok_stmt(hstmt, SQLFreeStmt(hstmt, SQL_CLOSE));
152
/* set row_size as 1 */
153
ok_stmt(hstmt, SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE,
156
ok_stmt(hstmt, SQLSetStmtAttr(hstmt, SQL_ATTR_ROWS_FETCHED_PTR, &nrows, 0));
158
ok_sql(hstmt, "SELECT * FROM t_relative1");
160
ok_stmt(hstmt, SQLBindCol(hstmt, 1, SQL_C_LONG, &i, 0, NULL));
163
ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_NEXT, 0));
167
expect_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, -1),
170
/* jump to last row */
171
ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, max_rows));
174
/* jump to last row+1 */
175
expect_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, 1),
179
ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_FIRST, 1));
183
expect_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, -1),
187
ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, 5));
191
expect_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, max_rows),
194
/* the scenarios from ODBC spec */
197
ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_FIRST, 1));
200
expect_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, -1),
203
/* BeforeStart AND FetchOffset <= 0 */
204
expect_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, -20),
207
expect_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, -1),
210
expect_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, 0),
213
/* case 1: Before start AND FetchOffset > 0 */
214
ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, 1));
218
ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_LAST, 1));
221
expect_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, 1),
224
/* After end AND FetchOffset >= 0 */
225
expect_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, 10),
228
expect_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, 20),
231
expect_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, 1),
234
expect_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, 0),
237
/* After end AND FetchOffset < 0 */
238
ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, -1));
242
ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_FIRST, 1));
245
/* CurrRowsetStart = 1 AND FetchOffset < 0 */
246
ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, 0));
249
expect_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, -1),
253
/* CurrRowsetStart > 1 AND CurrRowsetStart + FetchOffset < 1 AND
254
| FetchOffset | > RowsetSize
256
ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_FIRST, 1));
259
ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, 3));
262
/* the following call satisfies 4 > 1 AND (3-4) < 1 AND |-4| > 1 */
263
expect_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, -4),
267
/* 1 <= CurrRowsetStart + FetchOffset <= LastResultRow */
268
ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_FIRST, 1));
271
ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, 5));
275
ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, -2));
279
/* CurrRowsetStart > 1 AND CurrRowsetStart + FetchOffset < 1 AND
280
| FetchOffset | <= RowsetSize
282
ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_FIRST, 1));
285
ok_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, 3));
288
/* 4 >1 AND 4-4 <1 AND |-4| <=10 */
289
expect_stmt(hstmt, SQLFetchScroll(hstmt, SQL_FETCH_RELATIVE, -4),
292
ok_stmt(hstmt, SQLFreeStmt(hstmt, SQL_UNBIND));
293
ok_stmt(hstmt, SQLFreeStmt(hstmt, SQL_CLOSE));
295
ok_sql(hstmt, "DROP TABLE IF EXISTS t_relative1");