FreeTDS Development Group

["Daniel A. Veiga" <dveiga AT 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, jklowden AT freetds.org ("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
>
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)
 {