net.sourceforge.jtds.jdbc
Class MSCursorResultSet

java.lang.Object
  extended bynet.sourceforge.jtds.jdbc.JtdsResultSet
      extended bynet.sourceforge.jtds.jdbc.MSCursorResultSet
All Implemented Interfaces:
java.sql.ResultSet

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 Summary
private  boolean asyncCursor
          The cursor is being built asynchronously.
private static int CURSOR_CONCUR_OPTIMISTIC
           
private static int CURSOR_CONCUR_OPTIMISTIC_VALUES
           
private static int CURSOR_CONCUR_READ_ONLY
           
private static int CURSOR_CONCUR_SCROLL_LOCKS
           
private static java.lang.Integer CURSOR_OP_DELETE
           
private static java.lang.Integer CURSOR_OP_INSERT
           
private static java.lang.Integer CURSOR_OP_UPDATE
           
private static int CURSOR_TYPE_AUTO_FETCH
           
private static int CURSOR_TYPE_DYNAMIC
           
private static int CURSOR_TYPE_FASTFORWARDONLY
           
private static int CURSOR_TYPE_FORWARD
           
private static int CURSOR_TYPE_KEYSET
           
private static int CURSOR_TYPE_PARAMETERIZED
           
private static int CURSOR_TYPE_STATIC
           
private  int cursorPos
          Actual position of the cursor.
private static java.lang.Integer FETCH_ABSOLUTE
           
private static java.lang.Integer FETCH_FIRST
           
private static java.lang.Integer FETCH_INFO
           
private static java.lang.Integer FETCH_LAST
           
private static java.lang.Integer FETCH_NEXT
           
private static java.lang.Integer FETCH_PREVIOUS
           
private static java.lang.Integer FETCH_RELATIVE
           
private static java.lang.Integer FETCH_REPEAT
           
private  ParamInfo[] insertRow
          The "insert row".
private  boolean onInsertRow
          Set when moveToInsertRow() was called.
private  ParamInfo PARAM_CURSOR_HANDLE
          Cursor handle parameter.
private  ParamInfo PARAM_FETCHTYPE
          sp_cursorfetch fetchtype parameter.
private  ParamInfo PARAM_NUMROWS_IN
          sp_cursorfetch numrows IN parameter (for actual fetches).
private  ParamInfo PARAM_NUMROWS_OUT
          sp_cursorfetch numrows OUT parameter (for FETCH_INFO).
private  ParamInfo PARAM_OPTYPE
          sp_cursor optype parameter.
private  ParamInfo PARAM_ROWNUM
          sp_cursor rownum parameter.
private  ParamInfo PARAM_ROWNUM_IN
          sp_cursorfetch rownum IN parameter (for actual fetches).
private  ParamInfo PARAM_ROWNUM_OUT
          sp_cursorfetch rownum OUT parameter (for FETCH_INFO).
private  ParamInfo PARAM_TABLE
          sp_cursor table parameter.
private  java.lang.Object[][] rowCache
          The row cache used instead JtdsResultSet.currentRow.
private static java.lang.Integer SQL_ROW_DELETED
          The row has been deleted.
private static java.lang.Integer SQL_ROW_DIRTY
          The row is dirty and needs to be reloaded (internal state).
private static java.lang.Integer SQL_ROW_SUCCESS
          The row is valid.
private  ParamInfo[] updateRow
          The "update row".
 
Fields inherited from class net.sourceforge.jtds.jdbc.JtdsResultSet
cancelled, CLOSE_CURSORS_AT_COMMIT, closed, columnCount, columns, concurrency, currentRow, cursorName, direction, fetchDirection, fetchSize, HOLD_CURSORS_OVER_COMMIT, INITIAL_ROW_COUNT, pos, POS_AFTER_LAST, POS_BEFORE_FIRST, resultSetType, rowData, rowPtr, rowsInResult, statement, wasNull
 
Fields inherited from interface java.sql.ResultSet
CONCUR_READ_ONLY, CONCUR_UPDATABLE, FETCH_FORWARD, FETCH_REVERSE, FETCH_UNKNOWN, TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE, TYPE_SCROLL_SENSITIVE
 
Constructor Summary
(package private) MSCursorResultSet(JtdsStatement statement, java.lang.String sql, java.lang.String procName, ParamInfo[] procedureParams, int resultSetType, int concurrency)
          Construct a cursor result set using Microsoft sp_cursorcreate etc.
 
Method Summary
 boolean absolute(int row)
           
 void afterLast()
           
 void beforeFirst()
           
 void cancelRowUpdates()
           
 void close()
           
private  void cursor(java.lang.Integer opType, ParamInfo[] row)
          Support general cursor operations such as delete, update etc.
private  void cursorClose()
          Close a server side cursor.
private  void cursorCreate(java.lang.String sql, java.lang.String procName, ParamInfo[] parameters)
          Create a new Cursor result set using the internal sp_cursoropen procedure.
private  boolean cursorFetch(java.lang.Integer fetchType, int rowNum)
          Fetch the next result row from a cursor using the internal sp_cursorfetch procedure.
 void deleteRow()
           
 boolean first()
           
protected  java.lang.Object getColumn(int index)
          Get the specified column's data item.
protected  java.lang.Object[] getCurrentRow()
          Retrieve the current row data.
(package private) 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.
(package private) 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.
 void insertRow()
           
 boolean isLast()
           
 boolean last()
           
 void moveToCurrentRow()
           
 void moveToInsertRow()
           
 boolean next()
           
 boolean previous()
           
private  void processOutput(TdsCore tds, boolean setMeta)
          Processes the output of a cursor open or fetch operation.
 void refreshRow()
           
 boolean relative(int row)
           
 boolean rowDeleted()
           
 boolean rowInserted()
           
 boolean rowUpdated()
           
protected  java.lang.Object setColValue(int colIndex, int jdbcType, java.lang.Object value, int length)
          Set the specified column's data value.
 void updateRow()
           
 
Methods inherited from class net.sourceforge.jtds.jdbc.JtdsResultSet
cacheResultSetRows, checkOpen, checkScrollable, checkUpdateable, clearWarnings, copyInfo, copyRow, findColumn, getArray, getArray, getAsciiStream, getAsciiStream, getBigDecimal, getBigDecimal, getBigDecimal, getBigDecimal, getBinaryStream, getBinaryStream, getBlob, getBlob, getBoolean, getBoolean, getByte, getByte, getBytes, getBytes, getCharacterStream, getCharacterStream, getClob, getClob, getColumnCount, getColumns, getConcurrency, getCursorName, getDate, getDate, getDate, getDate, getDouble, getDouble, getFetchDirection, getFetchSize, getFloat, getFloat, getInt, getInt, getLong, getLong, getMetaData, getObject, getObject, getObject, getObject, getRef, getRef, getRow, getShort, getShort, getStatement, getString, getString, getTime, getTime, getTime, getTime, getTimestamp, getTimestamp, getTimestamp, getTimestamp, getType, getUnicodeStream, getUnicodeStream, getURL, getURL, getWarnings, isAfterLast, isBeforeFirst, isClosed, isFirst, newRow, notImplemented, setColLabel, setColName, setColType, setColumnCount, setFetchDirection, setFetchSize, updateArray, updateArray, updateAsciiStream, updateAsciiStream, updateBigDecimal, updateBigDecimal, updateBinaryStream, updateBinaryStream, updateBlob, updateBlob, updateBoolean, updateBoolean, updateByte, updateByte, updateBytes, updateBytes, updateCharacterStream, updateCharacterStream, updateClob, updateClob, updateDate, updateDate, updateDouble, updateDouble, updateFloat, updateFloat, updateInt, updateInt, updateLong, updateLong, updateNull, updateNull, updateObject, updateObject, updateObject, updateObject, updateRef, updateRef, updateShort, updateShort, updateString, updateString, updateTime, updateTime, updateTimestamp, updateTimestamp, wasNull
 
Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
 

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_KEYSET

private static final int CURSOR_TYPE_KEYSET
See Also:
Constant Field Values

CURSOR_TYPE_DYNAMIC

private static final int CURSOR_TYPE_DYNAMIC
See Also:
Constant Field Values

CURSOR_TYPE_FORWARD

private static final int CURSOR_TYPE_FORWARD
See Also:
Constant Field Values

CURSOR_TYPE_STATIC

private static final int CURSOR_TYPE_STATIC
See Also:
Constant Field Values

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".


rowCache

private java.lang.Object[][] rowCache
The row cache used instead JtdsResultSet.currentRow.


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.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[].


Generated on October 27 2012