[freetds] Problem retrieveing results from bottom to top

Frediano Ziglio freddy77 at gmail.com
Sat Jun 14 04:48:18 EDT 2008


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





More information about the FreeTDS mailing list