net.sourceforge.jtds.jdbc
Class MSCursorResultSet

java.lang.Object
  net.sourceforge.jtds.jdbc.JtdsResultSet
      net.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, 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[].