Class MSCursorResultSet

  • All Implemented Interfaces:
    java.lang.AutoCloseable, java.sql.ResultSet, java.sql.Wrapper

    public class MSCursorResultSet
    extends JtdsResultSet
    This class extends the JtdsResultSet to support scrollable and or updateable cursors on Microsoft servers.

    The undocumented Microsoft sp_cursor procedures are used.

    Implementation notes:

    1. All of Alin's cursor result set logic is incorporated here.
    2. This logic was originally implemented in the JtdsResultSet class but on reflection it seems that Alin's original approch of having a dedicated cursor class leads to a more flexible and maintainable design.
    Version:
    $Id: MSCursorResultSet.java,v 1.59 2007-07-11 20:02:45 bheineman Exp $
    Author:
    Alin Sinpalean, Mike Hutchinson
    • Field Detail

      • FETCH_FIRST

        private static final java.lang.Integer FETCH_FIRST
      • FETCH_NEXT

        private static final java.lang.Integer FETCH_NEXT
      • FETCH_PREVIOUS

        private static final java.lang.Integer FETCH_PREVIOUS
      • FETCH_LAST

        private static final java.lang.Integer FETCH_LAST
      • FETCH_ABSOLUTE

        private static final java.lang.Integer FETCH_ABSOLUTE
      • FETCH_RELATIVE

        private static final java.lang.Integer FETCH_RELATIVE
      • FETCH_REPEAT

        private static final java.lang.Integer FETCH_REPEAT
      • FETCH_INFO

        private static final java.lang.Integer FETCH_INFO
      • CURSOR_TYPE_FASTFORWARDONLY

        private static final int CURSOR_TYPE_FASTFORWARDONLY
        See Also:
        Constant Field Values
      • CURSOR_TYPE_PARAMETERIZED

        private static final int CURSOR_TYPE_PARAMETERIZED
        See Also:
        Constant Field Values
      • CURSOR_TYPE_AUTO_FETCH

        private static final int CURSOR_TYPE_AUTO_FETCH
        See Also:
        Constant Field Values
      • CURSOR_CONCUR_READ_ONLY

        private static final int CURSOR_CONCUR_READ_ONLY
        See Also:
        Constant Field Values
      • CURSOR_CONCUR_SCROLL_LOCKS

        private static final int CURSOR_CONCUR_SCROLL_LOCKS
        See Also:
        Constant Field Values
      • CURSOR_CONCUR_OPTIMISTIC

        private static final int CURSOR_CONCUR_OPTIMISTIC
        See Also:
        Constant Field Values
      • CURSOR_CONCUR_OPTIMISTIC_VALUES

        private static final int CURSOR_CONCUR_OPTIMISTIC_VALUES
        See Also:
        Constant Field Values
      • CURSOR_OP_INSERT

        private static final java.lang.Integer CURSOR_OP_INSERT
      • CURSOR_OP_UPDATE

        private static final java.lang.Integer CURSOR_OP_UPDATE
      • CURSOR_OP_DELETE

        private static final java.lang.Integer CURSOR_OP_DELETE
      • SQL_ROW_DIRTY

        private static final java.lang.Integer SQL_ROW_DIRTY
        The row is dirty and needs to be reloaded (internal state).
      • SQL_ROW_SUCCESS

        private static final java.lang.Integer SQL_ROW_SUCCESS
        The row is valid.
      • SQL_ROW_DELETED

        private static final java.lang.Integer SQL_ROW_DELETED
        The row has been deleted.
      • onInsertRow

        private boolean onInsertRow
        Set when moveToInsertRow() was called.
      • insertRow

        private ParamInfo[] insertRow
        The "insert row".
      • updateRow

        private ParamInfo[] updateRow
        The "update row".
      • cursorPos

        private int cursorPos
        Actual position of the cursor.
      • asyncCursor

        private boolean asyncCursor
        The cursor is being built asynchronously.
      • PARAM_CURSOR_HANDLE

        private final ParamInfo PARAM_CURSOR_HANDLE
        Cursor handle parameter.
      • PARAM_FETCHTYPE

        private final ParamInfo PARAM_FETCHTYPE
        sp_cursorfetch fetchtype parameter.
      • PARAM_ROWNUM_IN

        private final ParamInfo PARAM_ROWNUM_IN
        sp_cursorfetch rownum IN parameter (for actual fetches).
      • PARAM_NUMROWS_IN

        private final ParamInfo PARAM_NUMROWS_IN
        sp_cursorfetch numrows IN parameter (for actual fetches).
      • PARAM_ROWNUM_OUT

        private final ParamInfo PARAM_ROWNUM_OUT
        sp_cursorfetch rownum OUT parameter (for FETCH_INFO).
      • PARAM_NUMROWS_OUT

        private final ParamInfo PARAM_NUMROWS_OUT
        sp_cursorfetch numrows OUT parameter (for FETCH_INFO).
      • PARAM_OPTYPE

        private final ParamInfo PARAM_OPTYPE
        sp_cursor optype parameter.
      • PARAM_ROWNUM

        private final ParamInfo PARAM_ROWNUM
        sp_cursor rownum parameter.
      • PARAM_TABLE

        private final ParamInfo PARAM_TABLE
        sp_cursor table parameter.
    • Constructor Detail

      • MSCursorResultSet

        MSCursorResultSet​(JtdsStatement statement,
                          java.lang.String sql,
                          java.lang.String procName,
                          ParamInfo[] procedureParams,
                          int resultSetType,
                          int concurrency)
                   throws java.sql.SQLException
        Construct a cursor result set using Microsoft sp_cursorcreate etc.
        Parameters:
        statement - The parent statement object or null.
        resultSetType - one of FORWARD_ONLY, SCROLL_INSENSITIVE, SCROLL_SENSITIVE.
        concurrency - One of CONCUR_READ_ONLY, CONCUR_UPDATE.
        Throws:
        java.sql.SQLException
    • Method Detail

      • setColValue

        protected java.lang.Object setColValue​(int colIndex,
                                               int jdbcType,
                                               java.lang.Object value,
                                               int length)
                                        throws java.sql.SQLException
        Set the specified column's data value.
        Overrides:
        setColValue in class JtdsResultSet
        Parameters:
        colIndex - index of the column
        value - new column value
        length - the length of a stream parameter
        Returns:
        the value, possibly converted to an internal type
        Throws:
        java.sql.SQLException
      • getColumn

        protected java.lang.Object getColumn​(int index)
                                      throws java.sql.SQLException
        Get the specified column's data item.
        Overrides:
        getColumn in class JtdsResultSet
        Parameters:
        index - the column index in the row
        Returns:
        the column value as an Object
        Throws:
        java.sql.SQLException - if the index is out of bounds or there is no current row
      • getCursorScrollOpt

        static int getCursorScrollOpt​(int resultSetType,
                                      int resultSetConcurrency,
                                      boolean parameterized)
        Translates a JDBC result set type into SQL Server native @scrollOpt value for use with stored procedures such as sp_cursoropen, sp_cursorprepare or sp_cursorprepexec.
        Parameters:
        resultSetType - JDBC result set type (one of the ResultSet.TYPE_XXX values)
        resultSetConcurrency - JDBC result set concurrency (one of the ResultSet.CONCUR_XXX values)
        Returns:
        a value for the @scrollOpt parameter
      • getCursorConcurrencyOpt

        static int getCursorConcurrencyOpt​(int resultSetConcurrency)
        Translates a JDBC result set concurrency into SQL Server native @ccOpt value for use with stored procedures such as sp_cursoropen, sp_cursorprepare or sp_cursorprepexec.
        Parameters:
        resultSetConcurrency - JDBC result set concurrency (one of the ResultSet.CONCUR_XXX values)
        Returns:
        a value for the @scrollOpt parameter
      • cursorCreate

        private void cursorCreate​(java.lang.String sql,
                                  java.lang.String procName,
                                  ParamInfo[] parameters)
                           throws java.sql.SQLException
        Create a new Cursor result set using the internal sp_cursoropen procedure.
        Parameters:
        sql - The SQL SELECT statement.
        procName - Optional procedure name for cursors based on a stored procedure.
        parameters - Optional stored procedure parameters.
        Throws:
        java.sql.SQLException
      • cursorFetch

        private boolean cursorFetch​(java.lang.Integer fetchType,
                                    int rowNum)
                             throws java.sql.SQLException
        Fetch the next result row from a cursor using the internal sp_cursorfetch procedure.
        Parameters:
        fetchType - The type of fetch eg FETCH_ABSOLUTE.
        rowNum - The row number to fetch.
        Returns:
        boolean true if a result set row is returned.
        Throws:
        java.sql.SQLException
      • cursor

        private void cursor​(java.lang.Integer opType,
                            ParamInfo[] row)
                     throws java.sql.SQLException
        Support general cursor operations such as delete, update etc.
        Parameters:
        opType - the type of operation to perform
        row - the row number to update
        Throws:
        java.sql.SQLException
      • cursorClose

        private void cursorClose()
                          throws java.sql.SQLException
        Close a server side cursor.
        Throws:
        java.sql.SQLException
      • processOutput

        private void processOutput​(TdsCore tds,
                                   boolean setMeta)
                            throws java.sql.SQLException
        Processes the output of a cursor open or fetch operation. Fetches a batch of rows from the TdsCore, loading them into the row cache and optionally sets the column meta data (if called on cursor open). Consumes all the response and checks for server returned errors.
        Parameters:
        tds - the TdsCore instance
        setMeta - whether column meta data needs to be loaded (cursor open)
        Throws:
        java.sql.SQLException - if an error occurs or an error message is returned by the server
      • afterLast

        public void afterLast()
                       throws java.sql.SQLException
        Specified by:
        afterLast in interface java.sql.ResultSet
        Overrides:
        afterLast in class JtdsResultSet
        Throws:
        java.sql.SQLException
      • beforeFirst

        public void beforeFirst()
                         throws java.sql.SQLException
        Specified by:
        beforeFirst in interface java.sql.ResultSet
        Overrides:
        beforeFirst in class JtdsResultSet
        Throws:
        java.sql.SQLException
      • cancelRowUpdates

        public void cancelRowUpdates()
                              throws java.sql.SQLException
        Specified by:
        cancelRowUpdates in interface java.sql.ResultSet
        Overrides:
        cancelRowUpdates in class JtdsResultSet
        Throws:
        java.sql.SQLException
      • close

        public void close()
                   throws java.sql.SQLException
        Specified by:
        close in interface java.lang.AutoCloseable
        Specified by:
        close in interface java.sql.ResultSet
        Overrides:
        close in class JtdsResultSet
        Throws:
        java.sql.SQLException
      • deleteRow

        public void deleteRow()
                       throws java.sql.SQLException
        Specified by:
        deleteRow in interface java.sql.ResultSet
        Overrides:
        deleteRow in class JtdsResultSet
        Throws:
        java.sql.SQLException
      • insertRow

        public void insertRow()
                       throws java.sql.SQLException
        Specified by:
        insertRow in interface java.sql.ResultSet
        Overrides:
        insertRow in class JtdsResultSet
        Throws:
        java.sql.SQLException
      • moveToCurrentRow

        public void moveToCurrentRow()
                              throws java.sql.SQLException
        Specified by:
        moveToCurrentRow in interface java.sql.ResultSet
        Overrides:
        moveToCurrentRow in class JtdsResultSet
        Throws:
        java.sql.SQLException
      • moveToInsertRow

        public void moveToInsertRow()
                             throws java.sql.SQLException
        Specified by:
        moveToInsertRow in interface java.sql.ResultSet
        Overrides:
        moveToInsertRow in class JtdsResultSet
        Throws:
        java.sql.SQLException
      • refreshRow

        public void refreshRow()
                        throws java.sql.SQLException
        Specified by:
        refreshRow in interface java.sql.ResultSet
        Overrides:
        refreshRow in class JtdsResultSet
        Throws:
        java.sql.SQLException
      • updateRow

        public void updateRow()
                       throws java.sql.SQLException
        Specified by:
        updateRow in interface java.sql.ResultSet
        Overrides:
        updateRow in class JtdsResultSet
        Throws:
        java.sql.SQLException
      • first

        public boolean first()
                      throws java.sql.SQLException
        Specified by:
        first in interface java.sql.ResultSet
        Overrides:
        first in class JtdsResultSet
        Throws:
        java.sql.SQLException
      • isLast

        public boolean isLast()
                       throws java.sql.SQLException
        Specified by:
        isLast in interface java.sql.ResultSet
        Overrides:
        isLast in class JtdsResultSet
        Throws:
        java.sql.SQLException
      • last

        public boolean last()
                     throws java.sql.SQLException
        Specified by:
        last in interface java.sql.ResultSet
        Overrides:
        last in class JtdsResultSet
        Throws:
        java.sql.SQLException
      • next

        public boolean next()
                     throws java.sql.SQLException
        Specified by:
        next in interface java.sql.ResultSet
        Overrides:
        next in class JtdsResultSet
        Throws:
        java.sql.SQLException
      • previous

        public boolean previous()
                         throws java.sql.SQLException
        Specified by:
        previous in interface java.sql.ResultSet
        Overrides:
        previous in class JtdsResultSet
        Throws:
        java.sql.SQLException
      • rowDeleted

        public boolean rowDeleted()
                           throws java.sql.SQLException
        Specified by:
        rowDeleted in interface java.sql.ResultSet
        Overrides:
        rowDeleted in class JtdsResultSet
        Throws:
        java.sql.SQLException
      • rowInserted

        public boolean rowInserted()
                            throws java.sql.SQLException
        Specified by:
        rowInserted in interface java.sql.ResultSet
        Overrides:
        rowInserted in class JtdsResultSet
        Throws:
        java.sql.SQLException
      • rowUpdated

        public boolean rowUpdated()
                           throws java.sql.SQLException
        Specified by:
        rowUpdated in interface java.sql.ResultSet
        Overrides:
        rowUpdated in class JtdsResultSet
        Throws:
        java.sql.SQLException
      • absolute

        public boolean absolute​(int row)
                         throws java.sql.SQLException
        Specified by:
        absolute in interface java.sql.ResultSet
        Overrides:
        absolute in class JtdsResultSet
        Throws:
        java.sql.SQLException
      • relative

        public boolean relative​(int row)
                         throws java.sql.SQLException
        Specified by:
        relative in interface java.sql.ResultSet
        Overrides:
        relative in class JtdsResultSet
        Throws:
        java.sql.SQLException
      • getCurrentRow

        protected java.lang.Object[] getCurrentRow()
        Description copied from class: JtdsResultSet
        Retrieve the current row data.
        Overrides:
        getCurrentRow in class JtdsResultSet
        Returns:
        The current row data as an Object[].