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.