freetds AT lists.ibiblio.org
Subject: FreeTDS Development Group
List archive
Re: [freetds] Problem retrieveing results from bottom to top
- From: Frediano Ziglio <freddy77 AT gmail.com>
- To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
- Subject: Re: [freetds] Problem retrieveing results from bottom to top
- Date: Wed, 18 Jun 2008 11:02:03 +0200
Il giorno mar, 17/06/2008 alle 18.28 -0400, James K. Lowden ha scritto:
> Daniel A. Veiga wrote:
> > I believe the approach of MSSQL ODBC driver (fetching the row
> > number only when needed) is better than the one I found in the
> > jDBC driver. So I implemented
> > SQLGetStmtAttr(SQL_ATTR_ROW_NUMBER) that way.
>
> Hi Daniel,
>
> I don't want your patch to get lost; it's nice work. When you don't see
> an answer to serious work on this list for a few days, it's usually
> because it need serious consideration and no one's been able to look at it
> carefully.
>
> Your patch is a little difficult to apply easily because of how it's
> generated. Could I ask you to redo it?
>
> The command you want is:
>
> $ diff -u /path/to/freetds-orig . > odbc.cursor7.diff
>
> while '.' represents the top of your patched FreeTDS source tree.
>
> A universal diff is much easier to review (for these eyes, at least) and
> easier to edit and apply. If you don't do that, I'll have to apply each
> patch one at at time, then use CVS to generate a universal (-u) diff, and
> start again.
>
yes, it would help, mainly for tds.h patch, it's not that easy to
understand where to put the added line...
> Unless Frediano objects, I expect to review and apply your patch more or
> less as is and let the chips fall where they may. If it creates a
> problem, we can always back it out. Of course, I'm not expecting
> problems.
>
I would change some small points but mainly the patch is fine:
- INIT_HTSMT is not necessary at that point
- tds_cursor_get_cursor_info should return TDS_SUCCEED/TDS_FAIL
attached an update patch.
result
Creating table #cursor7_test with 12 records.
CREATE TABLE #cursor7_test (i INT, c VARCHAR(20))
INSERT INTO #cursor7_test(i,c) VALUES(1, 'a1b1c1')
INSERT INTO #cursor7_test(i,c) VALUES(2, 'a2b2c2')
INSERT INTO #cursor7_test(i,c) VALUES(3, 'a3b3c3')
INSERT INTO #cursor7_test(i,c) VALUES(4, 'a4b4c4')
INSERT INTO #cursor7_test(i,c) VALUES(5, 'a5b5c5')
INSERT INTO #cursor7_test(i,c) VALUES(6, 'a6b6c6')
INSERT INTO #cursor7_test(i,c) VALUES(7, 'a7b7c7')
INSERT INTO #cursor7_test(i,c) VALUES(8, 'a8b8c8')
INSERT INTO #cursor7_test(i,c) VALUES(9, 'a9b9c9')
INSERT INTO #cursor7_test(i,c) VALUES(10, 'a10b10c10')
INSERT INTO #cursor7_test(i,c) VALUES(11, 'a11b11c11')
INSERT INTO #cursor7_test(i,c) VALUES(12, 'a12b12c12')
Reading records from last to first:
12, a12b12c12
11, a11b11c11
10, a10b10c10
9, a9b9c9
8, a8b8c8
---> We are in record No: 8
7, a7b7c7
6, a6b6c6
5, a5b5c5
4, a4b4c4
3, a3b3c3
---> We are in record No: 3
2, a2b2c2
1, a1b1c1
---> We are in record No: 1
which is correct!
Perhaps we should try to convert to int with tds_convert insted of doing
all data check in tds_cursor_get_cursor_info.
freddy77
Index: include/tds.h
===================================================================
RCS file: /cvsroot/freetds/freetds/include/tds.h,v
retrieving revision 1.288
diff -U10 -r1.288 tds.h
--- include/tds.h 27 May 2008 22:24:49 -0000 1.288
+++ include/tds.h 18 Jun 2008 08:51:26 -0000
@@ -1477,20 +1477,21 @@
int tds_submit_rpc(TDSSOCKET * tds, const char *rpc_name, TDSPARAMINFO * params);
int tds_submit_optioncmd(TDSSOCKET * tds, TDS_OPTION_CMD command, TDS_OPTION option, TDS_OPTION_ARG *param, TDS_INT param_size);
int tds_quote_id(TDSSOCKET * tds, char *buffer, const char *id, int idlen);
int tds_quote_string(TDSSOCKET * tds, char *buffer, const char *str, int len);
const char *tds_skip_quoted(const char *s);
int tds_cursor_declare(TDSSOCKET * tds, TDSCURSOR * cursor, TDSPARAMINFO *params, int *send);
int tds_cursor_setrows(TDSSOCKET * tds, TDSCURSOR * cursor, int *send);
int tds_cursor_open(TDSSOCKET * tds, TDSCURSOR * cursor, TDSPARAMINFO *params, int *send);
int tds_cursor_fetch(TDSSOCKET * tds, TDSCURSOR * cursor, TDS_CURSOR_FETCH fetch_type, TDS_INT i_row);
+int tds_cursor_get_cursor_info(TDSSOCKET * tds, TDSCURSOR * cursor, TDS_UINT * row_number, TDS_UINT * row_count);
int tds_cursor_close(TDSSOCKET * tds, TDSCURSOR * cursor);
int tds_cursor_dealloc(TDSSOCKET * tds, TDSCURSOR * cursor);
int tds_cursor_update(TDSSOCKET * tds, TDSCURSOR * cursor, TDS_CURSOR_OPERATION op, TDS_INT i_row, TDSPARAMINFO * params);
int tds_cursor_setname(TDSSOCKET * tds, TDSCURSOR * cursor);
int tds_multiple_init(TDSSOCKET *tds, TDSMULTIPLE *multiple, TDS_MULTIPLE_TYPE type);
int tds_multiple_done(TDSSOCKET *tds, TDSMULTIPLE *multiple);
int tds_multiple_query(TDSSOCKET *tds, TDSMULTIPLE *multiple, const char *query, TDSPARAMINFO * params);
int tds_multiple_execute(TDSSOCKET *tds, TDSMULTIPLE *multiple, TDSDYNAMIC * dyn);
Index: src/odbc/odbc.c
===================================================================
RCS file: /cvsroot/freetds/freetds/src/odbc/odbc.c,v
retrieving revision 1.482
diff -U10 -r1.482 odbc.c
--- src/odbc/odbc.c 12 Jun 2008 03:10:16 -0000 1.482
+++ src/odbc/odbc.c 18 Jun 2008 08:51:28 -0000
@@ -4087,21 +4129,27 @@
src = &stmt->ard->header.sql_desc_bind_offset_ptr;
break;
#if SQL_BIND_TYPE != SQL_ATTR_ROW_BIND_TYPE
case SQL_BIND_TYPE: /* although this is ODBC2 we must support this attribute */
#endif
case SQL_ATTR_ROW_BIND_TYPE:
size = sizeof(stmt->ard->header.sql_desc_bind_type);
src = &stmt->ard->header.sql_desc_bind_type;
break;
case SQL_ATTR_ROW_NUMBER:
- /* TODO update this value */
+ /* TODO do not get info every time, cache somewhere */
+ if (stmt->cursor && odbc_lock_statement(stmt)) {
+ TDS_UINT row_number, row_count;
+
+ tds_cursor_get_cursor_info(stmt->dbc->tds_socket, stmt->cursor, &row_number, &row_count);
+ stmt->attr.row_number = row_number;
+ }
size = sizeof(stmt->attr.row_number);
src = &stmt->attr.row_number;
break;
case SQL_ATTR_ROW_OPERATION_PTR:
size = sizeof(stmt->ard->header.sql_desc_array_status_ptr);
src = &stmt->ard->header.sql_desc_array_status_ptr;
break;
case SQL_ATTR_ROW_STATUS_PTR:
size = sizeof(stmt->ird->header.sql_desc_array_status_ptr);
src = &stmt->ird->header.sql_desc_array_status_ptr;
Index: src/odbc/unittests/cursor7.c
===================================================================
RCS file: /cvsroot/freetds/freetds/src/odbc/unittests/cursor7.c,v
retrieving revision 1.1
diff -U10 -r1.1 cursor7.c
--- src/odbc/unittests/cursor7.c 12 Jun 2008 03:52:05 -0000 1.1
+++ src/odbc/unittests/cursor7.c 18 Jun 2008 08:51:29 -0000
@@ -1,29 +1,30 @@
#include "common.h"
-/* Test SQLFetchScroll with no bound columns */
+/* Test SQLFetchScroll with a non-unitary rowset, using bottom-up direction */
static char software_version[] = "$Id: cursor7.c,v 1.1 2008/06/12 03:52:05 jklowden Exp $";
static void *no_unused_var_warn[] = { software_version, no_unused_var_warn };
static void
Test(void)
{
enum { ROWS=5 };
struct data_t {
SQLINTEGER i;
SQLLEN ind_i;
char c[20];
SQLLEN ind_c;
} data[ROWS];
SQLUSMALLINT statuses[ROWS];
SQLULEN num_row;
+ SQLULEN RowNumber;
int i;
SQLRETURN ErrCode;
ResetStatement();
CHK(SQLSetStmtAttr, (Statement, SQL_ATTR_CONCURRENCY, int2ptr(SQL_CONCUR_READ_ONLY), 0));
CHK(SQLSetStmtAttr, (Statement, SQL_ATTR_CURSOR_TYPE, int2ptr(SQL_CURSOR_STATIC), 0));
CHK(SQLPrepare, (Statement, (SQLCHAR *) "SELECT c, i FROM #cursor7_test", SQL_NTS));
@@ -33,30 +34,40 @@
CHK(SQLSetStmtAttr, (Statement, SQL_ATTR_ROW_STATUS_PTR, statuses, 0));
CHK(SQLSetStmtAttr, (Statement, SQL_ATTR_ROWS_FETCHED_PTR, &num_row, 0));
CHK(SQLBindCol, (Statement, 1, SQL_C_CHAR, &data[0].c, sizeof(data[0].c), &data[0].ind_c));
CHK(SQLBindCol, (Statement, 2, SQL_C_LONG, &data[0].i, sizeof(data[0].i), &data[0].ind_i));
/* Read records from last to first */
printf("\n\nReading records from last to first:\n");
ErrCode = SQLFetchScroll(Statement, SQL_FETCH_LAST, -ROWS);
while ((ErrCode == SQL_SUCCESS) || (ErrCode == SQL_SUCCESS_WITH_INFO)) {
+ SQLULEN RowNumber;
+
/* Print this set of rows */
for (i = ROWS - 1; i >= 0; i--) {
if (statuses[i] != SQL_ROW_NOROW)
printf("\t %d, %s\n", (int) data[i].i, data[i].c);
}
+ CHK(SQLGetStmtAttr, (Statement, SQL_ROW_NUMBER, (SQLPOINTER)(&RowNumber), sizeof(RowNumber), NULL));
+ printf("---> We are in record No: %u\n", (unsigned int) RowNumber);
+
/* Read next rowset */
- ErrCode = SQLFetchScroll(Statement, SQL_FETCH_RELATIVE, -ROWS);
+ if ( (RowNumber>1) && (RowNumber<ROWS) ) {
+ ErrCode=SQLFetchScroll(Statement, SQL_FETCH_RELATIVE, 1-RowNumber);
+ for (i=RowNumber-1; i<ROWS; ++i)
+ statuses[i] = SQL_ROW_NOROW;
+ } else {
+ ErrCode=SQLFetchScroll(Statement, SQL_FETCH_RELATIVE, -ROWS);
+ }
}
- printf("\nRecords 5, 4 and 3 are returned twice!!\n\n");
}
static void
Init(void)
{
int i;
char sql[128];
printf("\n\nCreating table #cursor7_test with 12 records.\n");
Index: src/tds/query.c
===================================================================
RCS file: /cvsroot/freetds/freetds/src/tds/query.c,v
retrieving revision 1.219
diff -U10 -r1.219 query.c
--- src/tds/query.c 13 Feb 2008 08:52:09 -0000 1.219
+++ src/tds/query.c 18 Jun 2008 08:51:31 -0000
@@ -2412,20 +2413,133 @@
tds->internal_sp_called = TDS_SP_CURSORFETCH;
return tds_query_flush_packet(tds);
}
tds_set_state(tds, TDS_IDLE);
return TDS_SUCCEED;
}
int
+tds_cursor_get_cursor_info(TDSSOCKET * tds, TDSCURSOR * cursor, TDS_UINT * row_number, TDS_UINT * row_count)
+{
+ int done_flags;
+ int retcode;
+ TDS_INT result_type;
+
+ CHECK_TDS_EXTRA(tds);
+
+ if (!cursor)
+ return TDS_FAIL;
+
+ tdsdump_log(TDS_DBG_INFO1, "tds_cursor_get_cursor_info() cursor id = %d\n", cursor->cursor_id);
+
+ /* Assume not known */
+ *row_number = 0;
+ *row_count = 0;
+
+ if (IS_TDS7_PLUS(tds)) {
+ /* Change state to quering */
+ if (tds_set_state(tds, TDS_QUERYING) != TDS_QUERYING)
+ return TDS_FAIL;
+
+ /* Remember the server has been sent a command for this cursor */
+ tds_set_cur_cursor(tds, cursor);
+
+ /* General initialization of server command */
+ tds->out_flag = TDS_RPC;
+ START_QUERY;
+
+ /* Create and send query to server */
+ if (IS_TDS8_PLUS(tds)) {
+ tds_put_smallint(tds, -1);
+ tds_put_smallint(tds, TDS_SP_CURSORFETCH);
+ } else {
+ tds_put_smallint(tds, 14);
+ TDS_PUT_N_AS_UCS2(tds, "sp_cursorfetch");
+ }
+
+ /* This flag tells the SP only to */
+ /* output a dummy metadata token */
+
+ tds_put_smallint(tds, 2);
+
+ /* input cursor handle (int) */
+
+ tds_put_byte(tds, 0); /* no parameter name */
+ tds_put_byte(tds, 0); /* input parameter */
+ tds_put_byte(tds, SYBINTN);
+ tds_put_byte(tds, 4);
+ tds_put_byte(tds, 4);
+ tds_put_int(tds, cursor->cursor_id);
+
+ tds_put_byte(tds, 0); /* no parameter name */
+ tds_put_byte(tds, 0); /* input parameter */
+ tds_put_byte(tds, SYBINTN);
+ tds_put_byte(tds, 4);
+ tds_put_byte(tds, 4);
+ tds_put_int(tds, 0x100); /* FETCH_INFO */
+
+ /* row number */
+ tds_put_byte(tds, 0); /* no parameter name */
+ tds_put_byte(tds, 1); /* output parameter */
+ tds_put_byte(tds, SYBINTN);
+ tds_put_byte(tds, 4);
+ tds_put_byte(tds, 0);
+
+ /* number of rows fetched */
+ tds_put_byte(tds, 0); /* no parameter name */
+ tds_put_byte(tds, 1); /* output parameter */
+ tds_put_byte(tds, SYBINTN);
+ tds_put_byte(tds, 4);
+ tds_put_byte(tds, 0);
+
+ /* Adjust current state */
+ tds->internal_sp_called = 0;
+ if ( (retcode=tds_query_flush_packet(tds)) != TDS_SUCCEED )
+ return retcode;
+
+ /* Process answer from server */
+ for (;;) {
+ retcode = tds_process_tokens(tds, &result_type, &done_flags, TDS_RETURN_PROC);
+ tdsdump_log(TDS_DBG_FUNC, "tds_cursor_get_cursor_info: tds_process_tokens returned %d\n", retcode);
+ tdsdump_log(TDS_DBG_FUNC, " result_type=%d, TDS_DONE_COUNT=%x, TDS_DONE_ERROR=%x\n", result_type, (done_flags & TDS_DONE_COUNT), (done_flags & TDS_DONE_ERROR));
+ switch (retcode) {
+ case TDS_NO_MORE_RESULTS:
+ return TDS_SUCCEED;
+ case TDS_CANCELLED:
+ case TDS_FAIL:
+ return TDS_FAIL;
+
+ case TDS_SUCCEED:
+ if (result_type==TDS_PARAM_RESULT) {
+ /* Status is updated when TDS_STATUS_RESULT token arrives, before the params are processed */
+ if (tds->has_status && tds->ret_status==0) {
+ TDSPARAMINFO *pinfo = tds->current_results;
+
+ /* Make sure the params retuned have the correct tipe and size */
+ if (pinfo && pinfo->num_cols==2 && pinfo->columns[0]->column_type==SYBINTN && pinfo->columns[1]->column_type==SYBINTN && pinfo->columns[0]->column_size==4 && pinfo->columns[1]->column_size==4) {
+ /* Take the values */
+ *row_number = (TDS_UINT)(*(TDS_INT *) pinfo->columns[0]->column_data);
+ *row_count = (TDS_UINT)(*(TDS_INT *) pinfo->columns[1]->column_data);
+ tdsdump_log(TDS_DBG_FUNC, "----------------> row_number=%u, row_count=%u\n", row_count, row_number);
+ }
+ }
+ }
+ }
+ }
+ }
+
+ return TDS_SUCCEED;
+}
+
+int
tds_cursor_close(TDSSOCKET * tds, TDSCURSOR * cursor)
{
CHECK_TDS_EXTRA(tds);
if (!cursor)
return TDS_FAIL;
tdsdump_log(TDS_DBG_INFO1, "tds_cursor_close() cursor id = %d\n", cursor->cursor_id);
if (tds_set_state(tds, TDS_QUERYING) != TDS_QUERYING)
-
Re: [freetds] Problem retrieveing results from bottom to top,
Daniel A. Veiga, 06/13/2008
-
Re: [freetds] Problem retrieveing results from bottom to top,
Daniel A. Veiga, 06/13/2008
-
Re: [freetds] Problem retrieveing results from bottom to top,
Frediano Ziglio, 06/14/2008
-
Re: [freetds] Problem retrieveing results from bottom to top,
Daniel A. Veiga, 06/15/2008
-
Re: [freetds] Problem retrieveing results from bottom to top,
James K. Lowden, 06/17/2008
-
Re: [freetds] Problem retrieveing results from bottom to top,
Christos Zoulas, 06/17/2008
-
Re: [freetds] Problem retrieveing results from bottom to top,
Daniel A. Veiga, 06/19/2008
- Re: [freetds] Problem retrieveing results from bottom to top, James K. Lowden, 06/19/2008
-
Re: [freetds] Problem retrieveing results from bottom to top,
Daniel A. Veiga, 06/19/2008
-
Re: [freetds] Problem retrieveing results from bottom to top,
Frediano Ziglio, 06/18/2008
- Re: [freetds] Problem retrieveing results from bottom to top, Frediano Ziglio, 06/18/2008
-
Re: [freetds] Problem retrieveing results from bottom to top,
Christos Zoulas, 06/17/2008
-
Re: [freetds] Problem retrieveing results from bottom to top,
James K. Lowden, 06/17/2008
-
Re: [freetds] Problem retrieveing results from bottom to top,
Daniel A. Veiga, 06/15/2008
-
Re: [freetds] Problem retrieveing results from bottom to top,
Frediano Ziglio, 06/14/2008
-
Re: [freetds] Problem retrieveing results from bottom to top,
Daniel A. Veiga, 06/13/2008
Archive powered by MHonArc 2.6.24.