Re: Problem retrieveing results from bottom to top

Frediano Ziglio <[email protected]>
Newsgroups gmane.comp.db.tds.freetds
Message-ID <1213779723.7316.10.camel@freddy> 
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

_______________________________________________
FreeTDS mailing list
[email protected]
http://lists.ibiblio.org/mailman/listinfo/freetds
cursor7.diff (text/x-patch, 10.1 KB) 
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)
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.