net.sourceforge.jtds.jdbc

Class MSCursorResultSet

Implemented Interfaces:
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 $
Authors:
Alin Sinpalean
Mike Hutchinson

Field Summary

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 Integer
CURSOR_OP_DELETE
private static Integer
CURSOR_OP_INSERT
private static 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 static Integer
FETCH_ABSOLUTE
private static Integer
FETCH_FIRST
private static Integer
FETCH_INFO
private static Integer
FETCH_LAST
private static Integer
FETCH_NEXT
private static Integer
FETCH_PREVIOUS
private static Integer
FETCH_RELATIVE
private static Integer
FETCH_REPEAT
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 static Integer
SQL_ROW_DELETED
The row has been deleted.
private static Integer
SQL_ROW_DIRTY
The row is dirty and needs to be reloaded (internal state).
private static Integer
SQL_ROW_SUCCESS
The row is valid.
private boolean
asyncCursor
The cursor is being built asynchronously.
private int
cursorPos
Actual position of the cursor.
private ParamInfo[]
insertRow
The "insert row".
private boolean
onInsertRow
Set when moveToInsertRow() was called.
private Object[][]
rowCache
The row cache used instead JtdsResultSet.currentRow.
private ParamInfo[]
updateRow
The "update row".

Fields inherited from class net.sourceforge.jtds.jdbc.JtdsResultSet

CLOSE_CURSORS_AT_COMMIT, HOLD_CURSORS_OVER_COMMIT, INITIAL_ROW_COUNT, POS_AFTER_LAST, POS_BEFORE_FIRST, cancelled, closed, columnCount, columnMap, columns, concurrency, currentRow, cursorName, direction, f, fetchDirection, fetchSize, pos, resultSetType, rowData, rowPtr, rowsInResult, statement, wasNull

Constructor Summary

MSCursorResultSet(JtdsStatement statement, String sql, 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(Integer opType, ParamInfo[] row)
Support general cursor operations such as delete, update etc.
private void
cursorClose()
Close a server side cursor.
private void
cursorCreate(String sql, String procName, ParamInfo[] parameters)
Create a new Cursor result set using the internal sp_cursoropen procedure.
private boolean
cursorFetch(Integer fetchType, int rowNum)
Fetch the next result row from a cursor using the internal sp_cursorfetch procedure.
void
deleteRow()
boolean
first()
protected Object
getColumn(int index)
Get the specified column's data item.
protected 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 Object
setColValue(int colIndex, int jdbcType, Object value, int length)
Set the specified column's data value.
void
updateRow()

Methods inherited from class net.sourceforge.jtds.jdbc.JtdsResultSet

absolute, afterLast, beforeFirst, cacheResultSetRows, cancelRowUpdates, checkOpen, checkScrollable, checkUpdateable, clearWarnings, close, copyInfo, copyRow, deleteRow, findColumn, first, getArray, getArray, getAsciiStream, getAsciiStream, getBigDecimal, getBigDecimal, getBigDecimal, getBigDecimal, getBinaryStream, getBinaryStream, getBlob, getBlob, getBoolean, getBoolean, getByte, getByte, getBytes, getBytes, getCharacterStream, getCharacterStream, getClob, getClob, getColumn, getColumnCount, getColumns, getConcurrency, getConnection, getCurrentRow, 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, getURL, getURL, getUnicodeStream, getUnicodeStream, getWarnings, insertRow, isAfterLast, isBeforeFirst, isFirst, isLast, last, moveToCurrentRow, moveToInsertRow, newRow, next, notImplemented, previous, refreshRow, relative, rowDeleted, rowInserted, rowUpdated, setColLabel, setColName, setColType, setColValue, 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, updateRow, updateShort, updateShort, updateString, updateString, updateTime, updateTime, updateTimestamp, updateTimestamp, wasNull

Field Details

CURSOR_CONCUR_OPTIMISTIC

private static final int CURSOR_CONCUR_OPTIMISTIC
Field Value:
4

CURSOR_CONCUR_OPTIMISTIC_VALUES

private static final int CURSOR_CONCUR_OPTIMISTIC_VALUES
Field Value:
8

CURSOR_CONCUR_READ_ONLY

private static final int CURSOR_CONCUR_READ_ONLY
Field Value:
1

CURSOR_CONCUR_SCROLL_LOCKS

private static final int CURSOR_CONCUR_SCROLL_LOCKS
Field Value:
2

CURSOR_OP_DELETE

private static final Integer CURSOR_OP_DELETE

CURSOR_OP_INSERT

private static final Integer CURSOR_OP_INSERT

CURSOR_OP_UPDATE

private static final Integer CURSOR_OP_UPDATE

CURSOR_TYPE_AUTO_FETCH

private static final int CURSOR_TYPE_AUTO_FETCH
Field Value:
8192

CURSOR_TYPE_DYNAMIC

private static final int CURSOR_TYPE_DYNAMIC
Field Value:
2

CURSOR_TYPE_FASTFORWARDONLY

private static final int CURSOR_TYPE_FASTFORWARDONLY
Field Value:
16

CURSOR_TYPE_FORWARD

private static final int CURSOR_TYPE_FORWARD
Field Value:
4

CURSOR_TYPE_KEYSET

private static final int CURSOR_TYPE_KEYSET
Field Value:
1

CURSOR_TYPE_PARAMETERIZED

private static final int CURSOR_TYPE_PARAMETERIZED
Field Value:
4096

CURSOR_TYPE_STATIC

private static final int CURSOR_TYPE_STATIC
Field Value:
8

FETCH_ABSOLUTE

private static final Integer FETCH_ABSOLUTE

FETCH_FIRST

private static final Integer FETCH_FIRST

FETCH_INFO

private static final Integer FETCH_INFO

FETCH_LAST

private static final Integer FETCH_LAST

FETCH_NEXT

private static final Integer FETCH_NEXT

FETCH_PREVIOUS

private static final Integer FETCH_PREVIOUS

FETCH_RELATIVE

private static final Integer FETCH_RELATIVE

FETCH_REPEAT

private static final Integer FETCH_REPEAT

PARAM_CURSOR_HANDLE

private final ParamInfo PARAM_CURSOR_HANDLE
Cursor handle parameter.

PARAM_FETCHTYPE

private final ParamInfo PARAM_FETCHTYPE
sp_cursorfetch fetchtype parameter.

PARAM_NUMROWS_IN

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

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_ROWNUM_IN

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

PARAM_ROWNUM_OUT

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

PARAM_TABLE

private final ParamInfo PARAM_TABLE
sp_cursor table parameter.

SQL_ROW_DELETED

private static final Integer SQL_ROW_DELETED
The row has been deleted.

SQL_ROW_DIRTY

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

SQL_ROW_SUCCESS

private static final Integer SQL_ROW_SUCCESS
The row is valid.

asyncCursor

private boolean asyncCursor
The cursor is being built asynchronously.

cursorPos

private int cursorPos
Actual position of the cursor.

insertRow

private ParamInfo[] insertRow
The "insert row".

onInsertRow

private boolean onInsertRow
Set when moveToInsertRow() was called.

rowCache

private Object[][] rowCache

updateRow

private ParamInfo[] updateRow
The "update row".

Constructor Details

MSCursorResultSet

(package private)  MSCursorResultSet(JtdsStatement statement,
                                     String sql,
                                     String procName,
                                     ParamInfo[] procedureParams,
                                     int resultSetType,
                                     int concurrency)
            throws 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.

Method Details

absolute

public boolean absolute(int row)
            throws SQLException
Overrides:
absolute in interface JtdsResultSet

afterLast

public void afterLast()
            throws SQLException
Overrides:
afterLast in interface JtdsResultSet

beforeFirst

public void beforeFirst()
            throws SQLException
Overrides:
beforeFirst in interface JtdsResultSet

cancelRowUpdates

public void cancelRowUpdates()
            throws SQLException
Overrides:
cancelRowUpdates in interface JtdsResultSet

close

public void close()
            throws SQLException
Overrides:
close in interface JtdsResultSet

cursor

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

cursorClose

private void cursorClose()
            throws SQLException
Close a server side cursor.

cursorCreate

private void cursorCreate(String sql,
                          String procName,
                          ParamInfo[] parameters)
            throws 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.

cursorFetch

private boolean cursorFetch(Integer fetchType,
                            int rowNum)
            throws 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.

deleteRow

public void deleteRow()
            throws SQLException
Overrides:
deleteRow in interface JtdsResultSet

first

public boolean first()
            throws SQLException
Overrides:
first in interface JtdsResultSet

getColumn

protected Object getColumn(int index)
            throws SQLException
Get the specified column's data item.
Overrides:
getColumn in interface JtdsResultSet
Parameters:
index - the column index in the row
Returns:
the column value as an Object

getCurrentRow

protected Object[] getCurrentRow()
Retrieve the current row data.
Overrides:
getCurrentRow in interface JtdsResultSet
Returns:
The current row data as an Object[].

getCursorConcurrencyOpt

(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.
Parameters:
resultSetConcurrency - JDBC result set concurrency (one of the ResultSet.CONCUR_XXX values)
Returns:
a value for the @scrollOpt parameter

getCursorScrollOpt

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

insertRow

public void insertRow()
            throws SQLException
Overrides:
insertRow in interface JtdsResultSet

isLast

public boolean isLast()
            throws SQLException
Overrides:
isLast in interface JtdsResultSet

last

public boolean last()
            throws SQLException
Overrides:
last in interface JtdsResultSet

moveToCurrentRow

public void moveToCurrentRow()
            throws SQLException
Overrides:
moveToCurrentRow in interface JtdsResultSet

moveToInsertRow

public void moveToInsertRow()
            throws SQLException
Overrides:
moveToInsertRow in interface JtdsResultSet

next

public boolean next()
            throws SQLException
Overrides:
next in interface JtdsResultSet

previous

public boolean previous()
            throws SQLException
Overrides:
previous in interface JtdsResultSet

processOutput

private void processOutput(TdsCore tds,
                           boolean setMeta)
            throws 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)

refreshRow

public void refreshRow()
            throws SQLException
Overrides:
refreshRow in interface JtdsResultSet

relative

public boolean relative(int row)
            throws SQLException
Overrides:
relative in interface JtdsResultSet

rowDeleted

public boolean rowDeleted()
            throws SQLException
Overrides:
rowDeleted in interface JtdsResultSet

rowInserted

public boolean rowInserted()
            throws SQLException
Overrides:
rowInserted in interface JtdsResultSet

rowUpdated

public boolean rowUpdated()
            throws SQLException
Overrides:
rowUpdated in interface JtdsResultSet

setColValue

protected Object setColValue(int colIndex,
                             int jdbcType,
                             Object value,
                             int length)
            throws SQLException
Set the specified column's data value.
Overrides:
setColValue in interface JtdsResultSet
Parameters:
colIndex - index of the column
value - new column value
Returns:
the value, possibly converted to an internal type

updateRow

public void updateRow()
            throws SQLException
Overrides:
updateRow in interface JtdsResultSet

Generated on September 18 2009