Skip to Content.
Sympa Menu

freetds - Re: [freetds] Problem retrieveing results from bottom to top

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • 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)



Archive powered by MHonArc 2.6.24.

Top of Page