Re: Problem retrieveing results from bottom to top
"Daniel A. Veiga" <[email protected]>
| Newsgroups | gmane.comp.db.tds.freetds |
|---|---|
| Message-ID | <50659.190.31.181.246.1213913469.squirrel@www.advtechnology.com.ar> |
Christos,
Sory for the delay in answering, I had to travel and I didn´t
have access to my computer. I'm attaching the patch in the
format you asked for.
I don't think the patch is going to create problems, because
as you can see from the code, if something goes wrong,
SQLGetStmtAttr(SQL_ATTR_ROW_NUMBER) returns 0 (unknown) that's
what it always returned before this change.
Please let me know if you need anything else. I haven't used
diff too much, so if its still not what you're looking for, I
can always generate a new one. Bye
Daniel
> On Jun 17, 6:28pm, [email protected] ("James K. Lowden") wrote:
> -- Subject: Re: [freetds] Problem retrieveing results from bottom to
> top
>
> | 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.
> |
> | 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.
> |
>
> diff -ru
>
> christos
>
_______________________________________________
FreeTDS mailing list
[email protected]
http://lists.ibiblio.org/mailman/listinfo/freetds
odbc.cursor7.diff
(text/plain, 7.2 KB)
diff -ru ./freetds.original/include/tds.h ./freetds/include/tds.h
--- ./freetds.original/include/tds.h 2008-05-27 19:24:49.000000000 -0300
+++ ./freetds/include/tds.h 2008-06-15 11:40:36.000000000 -0300
@@ -1484,6 +1484,7 @@
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);
diff -ru ./freetds.original/src/odbc/odbc.c ./freetds/src/odbc/odbc.c
--- ./freetds.original/src/odbc/odbc.c 2008-06-12 00:10:16.000000000 -0300
+++ ./freetds/src/odbc/odbc.c 2008-06-15 19:25:35.000000000 -0300
@@ -4094,7 +4094,19 @@
src = &stmt->ard->header.sql_desc_bind_type;
break;
case SQL_ATTR_ROW_NUMBER:
- /* TODO update this value */
+ {
+ INIT_HSTMT;
+
+ if (stmt->cursor && odbc_lock_statement(stmt)) {
+ TDS_UINT RowNumber;
+ TDS_UINT RowCount;
+ TDSCURSOR *cursor = stmt->cursor;
+ TDSSOCKET *tds = stmt->dbc->tds_socket;
+
+ tds_cursor_get_cursor_info(tds, cursor, &RowNumber, &RowCount);
+ stmt->attr.row_number = (SQLULEN)(RowNumber);
+ }
+ }
size = sizeof(stmt->attr.row_number);
src = &stmt->attr.row_number;
break;
diff -ru ./freetds.original/src/odbc/unittests/cursor7.c ./freetds/src/odbc/unittests/cursor7.c
--- ./freetds.original/src/odbc/unittests/cursor7.c 2008-06-12 00:52:05.000000000 -0300
+++ ./freetds/src/odbc/unittests/cursor7.c 2008-06-15 19:21:25.000000000 -0300
@@ -1,8 +1,8 @@
#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 char software_version[] = "$Id: cursor7.c,v 1.0 2008/06/13 16:08:46 dveiga Exp $";
static void *no_unused_var_warn[] = { software_version, no_unused_var_warn };
static void
@@ -17,6 +17,7 @@
} data[ROWS];
SQLUSMALLINT statuses[ROWS];
SQLULEN num_row;
+ SQLULEN RowNumber;
int i;
SQLRETURN ErrCode;
@@ -46,10 +47,21 @@
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", 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
diff -ru ./freetds.original/src/tds/query.c ./freetds/src/tds/query.c
--- ./freetds.original/src/tds/query.c 2008-02-13 06:52:09.000000000 -0200
+++ ./freetds/src/tds/query.c 2008-06-15 12:28:54.000000000 -0300
@@ -2418,6 +2418,127 @@
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_CMD_DONE;
+ case TDS_CANCELLED:
+ case TDS_FAIL:
+ return TDS_CMD_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);
+ }
+ tds_free_param_results(tds->current_results);
+ tds->current_results=NULL;
+ }
+ }
+ }
+ }
+
+ /* Switch back to idle */
+ tds_set_state(tds, TDS_IDLE);
+ }
+
+ return TDS_SUCCEED;
+}
+
+
+
int
tds_cursor_close(TDSSOCKET * tds, TDSCURSOR * cursor)
{