net.sourceforge.jtds.jdbc

Class MSCursorResultSet

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

All of Alin's cursor result set logic is incorporated here.
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.

$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()`

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

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

The row cache used instead JtdsResultSet.currentRow.

updateRow

private ParamInfo[] updateRow

The "update row".

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.

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

public boolean next()
            throws SQLException

Overrides:
next in interface JtdsResultSet

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 June 12 2008

Overview Package Class Use Tree Index Deprecated About
Prev Class \| Next Class	Frames \| No Frames
Summary: Nested \| Field \| Method \| Constr	Detail: Nested \| Field \| Method \| Constr

net.sourceforge.jtds.jdbc

Class MSCursorResultSet

Field Summary

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

Constructor Summary

Method Summary

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

Field Details

CURSOR_CONCUR_OPTIMISTIC

CURSOR_CONCUR_OPTIMISTIC_VALUES

CURSOR_CONCUR_READ_ONLY

CURSOR_CONCUR_SCROLL_LOCKS

CURSOR_OP_DELETE

CURSOR_OP_INSERT

CURSOR_OP_UPDATE

CURSOR_TYPE_AUTO_FETCH

CURSOR_TYPE_DYNAMIC

CURSOR_TYPE_FASTFORWARDONLY

CURSOR_TYPE_FORWARD

CURSOR_TYPE_KEYSET

CURSOR_TYPE_PARAMETERIZED

CURSOR_TYPE_STATIC

FETCH_ABSOLUTE

FETCH_FIRST

FETCH_INFO

FETCH_LAST

FETCH_NEXT

FETCH_PREVIOUS

FETCH_RELATIVE

FETCH_REPEAT

PARAM_CURSOR_HANDLE

PARAM_FETCHTYPE

PARAM_NUMROWS_IN

PARAM_NUMROWS_OUT

PARAM_OPTYPE

PARAM_ROWNUM

PARAM_ROWNUM_IN

PARAM_ROWNUM_OUT

PARAM_TABLE

SQL_ROW_DELETED

SQL_ROW_DIRTY

SQL_ROW_SUCCESS

asyncCursor

cursorPos

insertRow

onInsertRow

rowCache

updateRow

Constructor Details

MSCursorResultSet

Method Details

absolute

afterLast

beforeFirst

cancelRowUpdates

close

cursor

cursorClose

cursorCreate

cursorFetch

deleteRow

first

getColumn

getCurrentRow

getCursorConcurrencyOpt

getCursorScrollOpt

insertRow

isLast

last

moveToCurrentRow

moveToInsertRow

next

previous

processOutput

refreshRow

relative

rowDeleted

rowInserted

rowUpdated

setColValue