[freetds] Problem retrieveing results from bottom to top

Daniel A. Veiga dveiga at advtechnology.com.ar
Fri Jun 13 21:16:25 EDT 2008


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







More information about the FreeTDS mailing list