[freetds] Problem retrieveing results from bottom to top

Daniel A. Veiga dveiga at advtechnology.com.ar
Sun Jun 15 18:59:42 EDT 2008


Frediano,
         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. To do it I did
the following changes:

/src/tds/query.c:
    - I added a new function called tds_cursor_get_cursor_info(). This
function builds the packet with the query to the server, sends the
command, waits for the answer and parses it. Finally it returns the
row number.

/include/tds.h:
    - I added the prototype for the new function.

/src/odbc/odbc.c:
    - Where it said "/* TODO update this value */" (for the row_number)
I added code to call the new function.

/src/odbc/unittests/cursor7.c:
    - I modified it to test how it worked.


     Its working Ok now!!! I'm attaching a file (diffs.tgz) with the
diffs between the modified sources and the latest I found on the
CVS, as I think this functionality could be usefull for others.

     I leave a couple of TODOs for those with more experience in TDS
and the project itself:

1) The new function (tds_cursor_get_cursor_info) only querys the server
for the row number if the protocol is 7+, otherwise it returns 0
(unknown). I don't know if this functionality is available in previous
versions of the TDS protocol nor do I have the possibility of testing
it, as I only have an SQL Server 2K. If anybody knows that previous
versions support cursor info, it might be usefull to change the "if"
clause to include those versions.

2) There is a variable, part of the stmt->attr structure, called
row_number. I left it as it was, but as I only update it when somebody
calls SQLGetStmtAttr, it has an invalid value as soon as the cursor is
moved and until SQLGetStmtAttr is called again. The truth is that
nobody uses that variable anywhere else, so this is not a problem for
the operation, but I personally dislike these situations. In the
future, someone modifying the code can easily get confused by a
variable with an invalid value most of the time. I believe we have the
following alternatives:
  a) Remove the variable from the structure: If the final
implementation will query the server for the row_number whenever the
user requests it, there is no need for that variable.
  b) Find all the places where the cursor is moved, and zero this
variable (0=unknown). This could give an additional performance
enhacement as SQLGetStmtAttr(SQL_ATTR_ROW_NUMBER) could be easilly
modified to query the server only if row_number is zero, avoiding
quering the server more than once if
SQLGetStmtAttr(SQL_ATTR_ROW_NUMBER) is called several times without
moving the cursor.

Bye,

                                     Daniel









> Il giorno ven, 13/06/2008 alle 22.16 -0300, Daniel A. Veiga ha scritto:
>> I found this piece of code at koders.com that is supposed to be part
>> of
>> the "jTDS JDBC Driver for Microsoft SQL Server and Sybase". The
>> function that retrieves data from the server follows:
>>
>> /**
>>      * Fetch the next result row from a cursor using the internal
>> sp_cursorfetch procedure.
>>      *
>>      * @param fetchType The type of fetch eg FETCH_ABSOLUTE.
>>      * @param rowNum The row number to fetch.
>>      * @return <code>boolean</code> true if a result set row is
>> returned.
>>      * @throws SQLException
>>      */
>>     private boolean cursorFetch(Integer fetchType, int rowNum)
>>             throws SQLException {
>>         TdsCore tds = statement.getTds();
>>
>>         statement.clearWarnings();
>>
>>         if (fetchType != FETCH_ABSOLUTE && fetchType !=
>> FETCH_RELATIVE) {
>>             rowNum = 1;
>>         }
>>
>>         ParamInfo[] param = new ParamInfo[4];
>>         // Setup cursor handle param
>>         param[0] = PARAM_CURSOR_HANDLE;
>>
>>         // Setup fetchtype param
>>         PARAM_FETCHTYPE.value = fetchType;
>>         param[1] = PARAM_FETCHTYPE;
>>
>>         // Setup rownum
>>         PARAM_ROWNUM_IN.value = new Integer(rowNum);
>>         param[2] = PARAM_ROWNUM_IN;
>>         // Setup numRows parameter
>>         if (((Integer) PARAM_NUMROWS_IN.value).intValue() !=
>> fetchSize) {
>>             // If the fetch size changed, update the parameter and
>> cache size
>>             PARAM_NUMROWS_IN.value = new Integer(fetchSize);
>>             rowCache = new Object[fetchSize][];
>>         }
>>         param[3] = PARAM_NUMROWS_IN;
>>
>>         synchronized (tds) {
>>             // No meta data, no timeout (we're not sending it yet),
>> no row
>>             // limit, don't send yet
>>             tds.executeSQL(null, "sp_cursorfetch", param, true, 0, 0,
>>                     statement.getMaxFieldSize(), false);
>>
>>             // Setup fetchtype param
>>             PARAM_FETCHTYPE.value = FETCH_INFO;
>>             param[1] = PARAM_FETCHTYPE;
>>
>>             // Setup rownum
>>             PARAM_ROWNUM_OUT.clearOutValue();
>>             param[2] = PARAM_ROWNUM_OUT;
>>             // Setup numRows parameter
>>             PARAM_NUMROWS_OUT.clearOutValue();
>>             param[3] = PARAM_NUMROWS_OUT;
>>
>>             // No meta data, use the statement timeout, leave max
>> rows
>> as it is
>>             // (no limit), leave max field size as it is, send now
>>             tds.executeSQL(null, "sp_cursorfetch", param, true,
>>                     statement.getQueryTimeout(), -1, -1, true);
>>         }
>>
>>         // Load rows
>>         processOutput(tds, false);
>>
>>         cursorPos = ((Integer)
>> PARAM_ROWNUM_OUT.getOutValue()).intValue();
>>         if (fetchType != FETCH_REPEAT) {
>>             // Do not change ResultSet position when refreshing
>>             pos = cursorPos;
>>         }
>>         rowsInResult = ((Integer)
>> PARAM_NUMROWS_OUT.getOutValue()).intValue();
>>         if (rowsInResult < 0) {
>>             // -1 = Dynamic cursor number of rows cannot be known.
>>             // -n = Async cursor = rows loaded so far
>>             rowsInResult = 0 - rowsInResult;
>>         }
>>
>>         return getCurrentRow() != null;
>>     }
>>
>>
>> I do not understand TDS protocol very well by now, but comparing it
>> to
>> the fetch function in FreeTDS's query.c I noticed that in the fetch
>> this driver is requesting the server to send the current row as a
>> result in the result set. I'm wondering if we can we do something
>> similar to retrieve the row number in FreeTDS? If we can, we could
>> update the apropieate field (stmt->attr.row_number) of the statement
>> structure and implement SQLGetStmtAttr(SQL_ATTR_ROW_NUMBER)!
>>
>> Bye,
>>
>>
>>                               Daniel
>>
>
> The driver send 2 request, one for fetch and another for retrieving
> state (row number and row count). Currently as you state
> SQL_ATTR_ROW_NUMBER is not implemented. It could be implemented in
> another way than setting stmt->attr.row_number. jTDS as code reveals
> always request state to server after every fetch while ms odbc driver
> does a lazy evaluation requesting row number when SQLGetStmtAttr is
> called. So jTDS send more data fetching rows while ms odbc require an
> extra round trip when SQLGetStmtAttr is called...
> The key however is to call sp_cursorfetch with 0x100 (result info) in
> order to get state. I tried to mix asking state and fetching (adding a
> "| 0x100" and setting parameters to output) but server doesn't like
> this... so 2 requests are necessary!
>
> freddy77
>
>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: diffs.tgz
Type: application/x-gzip-compressed
Size: 2300 bytes
Desc: not available
Url : http://lists.ibiblio.org/pipermail/freetds/attachments/20080615/d876421c/attachment.bin 


More information about the FreeTDS mailing list