0001: // jTDS JDBC Driver for Microsoft SQL Server and Sybase
0002: // Copyright (C) 2004 The jTDS Project
0003: //
0004: // This library is free software; you can redistribute it and/or
0005: // modify it under the terms of the GNU Lesser General Public
0006: // License as published by the Free Software Foundation; either
0007: // version 2.1 of the License, or (at your option) any later version.
0008: //
0009: // This library is distributed in the hope that it will be useful,
0010: // but WITHOUT ANY WARRANTY; without even the implied warranty of
0011: // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
0012: // Lesser General Public License for more details.
0013: //
0014: // You should have received a copy of the GNU Lesser General Public
0015: // License along with this library; if not, write to the Free Software
0016: // Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
0017: //
0018: package net.sourceforge.jtds.jdbc;
0019:
0020: import java.math.BigDecimal;
0021: import java.sql.SQLException;
0022: import java.sql.SQLWarning;
0023: import java.sql.Types;
0024: import java.sql.ResultSet;
0025:
0026: /**
0027: * This class extends the JtdsResultSet to support scrollable and or
0028: * updateable cursors on Microsoft servers.
0029: * <p>The undocumented Microsoft sp_cursor procedures are used.
0030: * <p>
0031: * Implementation notes:
0032: * <ol>
0033: * <li>All of Alin's cursor result set logic is incorporated here.
0034: * <li>This logic was originally implemented in the JtdsResultSet class but on reflection
0035: * it seems that Alin's original approch of having a dedicated cursor class leads to a more
0036: * flexible and maintainable design.
0037: * </ol>
0038: *
0039: * @author Alin Sinpalean
0040: * @author Mike Hutchinson
0041: * @version $Id: MSCursorResultSet.java,v 1.59 2007/07/11 20:02:45 bheineman Exp $
0042: */
0043: public class MSCursorResultSet extends JtdsResultSet {
0044: /*
0045: * Constants
0046: */
0047: private static final Integer FETCH_FIRST = new Integer(1);
0048: private static final Integer FETCH_NEXT = new Integer(2);
0049: private static final Integer FETCH_PREVIOUS = new Integer(4);
0050: private static final Integer FETCH_LAST = new Integer(8);
0051: private static final Integer FETCH_ABSOLUTE = new Integer(16);
0052: private static final Integer FETCH_RELATIVE = new Integer(32);
0053: private static final Integer FETCH_REPEAT = new Integer(128);
0054: private static final Integer FETCH_INFO = new Integer(256);
0055:
0056: private static final int CURSOR_TYPE_KEYSET = 0x01;
0057: private static final int CURSOR_TYPE_DYNAMIC = 0x02;
0058: private static final int CURSOR_TYPE_FORWARD = 0x04;
0059: private static final int CURSOR_TYPE_STATIC = 0x08;
0060: private static final int CURSOR_TYPE_FASTFORWARDONLY = 0x10;
0061: private static final int CURSOR_TYPE_PARAMETERIZED = 0x1000;
0062: private static final int CURSOR_TYPE_AUTO_FETCH = 0x2000;
0063:
0064: private static final int CURSOR_CONCUR_READ_ONLY = 1;
0065: private static final int CURSOR_CONCUR_SCROLL_LOCKS = 2;
0066: private static final int CURSOR_CONCUR_OPTIMISTIC = 4;
0067: private static final int CURSOR_CONCUR_OPTIMISTIC_VALUES = 8;
0068:
0069: private static final Integer CURSOR_OP_INSERT = new Integer(4);
0070: private static final Integer CURSOR_OP_UPDATE = new Integer(33);
0071: private static final Integer CURSOR_OP_DELETE = new Integer(34);
0072:
0073: /**
0074: * The row is dirty and needs to be reloaded (internal state).
0075: */
0076: private static final Integer SQL_ROW_DIRTY = new Integer(0);
0077:
0078: /**
0079: * The row is valid.
0080: */
0081: private static final Integer SQL_ROW_SUCCESS = new Integer(1);
0082:
0083: /**
0084: * The row has been deleted.
0085: */
0086: private static final Integer SQL_ROW_DELETED = new Integer(2);
0087:
0088: /*
0089: * Instance variables.
0090: */
0091: /** Set when <code>moveToInsertRow()</code> was called. */
0092: private boolean onInsertRow;
0093: /** The "insert row". */
0094: private ParamInfo[] insertRow;
0095: /** The "update row". */
0096: private ParamInfo[] updateRow;
0097: /** The row cache used instead {@link #currentRow}. */
0098: private Object[][] rowCache;
0099: /** Actual position of the cursor. */
0100: private int cursorPos;
0101: /** The cursor is being built asynchronously. */
0102: private boolean asyncCursor;
0103:
0104: //
0105: // Fixed sp_XXX parameters
0106: //
0107: /** Cursor handle parameter. */
0108: private final ParamInfo PARAM_CURSOR_HANDLE = new ParamInfo(
0109: Types.INTEGER, null, ParamInfo.INPUT);
0110:
0111: /** <code>sp_cursorfetch</code> fetchtype parameter. */
0112: private final ParamInfo PARAM_FETCHTYPE = new ParamInfo(
0113: Types.INTEGER, null, ParamInfo.INPUT);
0114:
0115: /** <code>sp_cursorfetch</code> rownum IN parameter (for actual fetches). */
0116: private final ParamInfo PARAM_ROWNUM_IN = new ParamInfo(
0117: Types.INTEGER, null, ParamInfo.INPUT);
0118:
0119: /** <code>sp_cursorfetch</code> numrows IN parameter (for actual fetches). */
0120: private final ParamInfo PARAM_NUMROWS_IN = new ParamInfo(
0121: Types.INTEGER, null, ParamInfo.INPUT);
0122:
0123: /** <code>sp_cursorfetch</code> rownum OUT parameter (for FETCH_INFO). */
0124: private final ParamInfo PARAM_ROWNUM_OUT = new ParamInfo(
0125: Types.INTEGER, null, ParamInfo.OUTPUT);
0126:
0127: /** <code>sp_cursorfetch</code> numrows OUT parameter (for FETCH_INFO). */
0128: private final ParamInfo PARAM_NUMROWS_OUT = new ParamInfo(
0129: Types.INTEGER, null, ParamInfo.OUTPUT);
0130:
0131: /** <code>sp_cursor</code> optype parameter. */
0132: private final ParamInfo PARAM_OPTYPE = new ParamInfo(Types.INTEGER,
0133: null, ParamInfo.INPUT);
0134:
0135: /** <code>sp_cursor</code> rownum parameter. */
0136: private final ParamInfo PARAM_ROWNUM = new ParamInfo(Types.INTEGER,
0137: new Integer(1), ParamInfo.INPUT);
0138:
0139: /** <code>sp_cursor</code> table parameter. */
0140: private final ParamInfo PARAM_TABLE = new ParamInfo(Types.VARCHAR,
0141: "", ParamInfo.UNICODE);
0142:
0143: /**
0144: * Construct a cursor result set using Microsoft sp_cursorcreate etc.
0145: *
0146: * @param statement The parent statement object or null.
0147: * @param resultSetType one of FORWARD_ONLY, SCROLL_INSENSITIVE, SCROLL_SENSITIVE.
0148: * @param concurrency One of CONCUR_READ_ONLY, CONCUR_UPDATE.
0149: * @throws SQLException
0150: */
0151: MSCursorResultSet(JtdsStatement statement, String sql,
0152: String procName, ParamInfo[] procedureParams,
0153: int resultSetType, int concurrency) throws SQLException {
0154: super (statement, resultSetType, concurrency, null);
0155:
0156: PARAM_NUMROWS_IN.value = new Integer(fetchSize);
0157: rowCache = new Object[fetchSize][];
0158:
0159: cursorCreate(sql, procName, procedureParams);
0160: if (asyncCursor) {
0161: // Obtain a provisional row count for the result set
0162: cursorFetch(FETCH_REPEAT, 0);
0163: }
0164: }
0165:
0166: /**
0167: * Set the specified column's data value.
0168: *
0169: * @param colIndex index of the column
0170: * @param value new column value
0171: * @return the value, possibly converted to an internal type
0172: */
0173: protected Object setColValue(int colIndex, int jdbcType,
0174: Object value, int length) throws SQLException {
0175:
0176: value = super .setColValue(colIndex, jdbcType, value, length);
0177:
0178: if (!onInsertRow && getCurrentRow() == null) {
0179: throw new SQLException(Messages
0180: .get("error.resultset.norow"), "24000");
0181: }
0182: colIndex--;
0183: ParamInfo pi;
0184: ColInfo ci = columns[colIndex];
0185:
0186: if (onInsertRow) {
0187: pi = insertRow[colIndex];
0188: } else {
0189: if (updateRow == null) {
0190: updateRow = new ParamInfo[columnCount];
0191: }
0192: pi = updateRow[colIndex];
0193: }
0194:
0195: if (pi == null) {
0196: pi = new ParamInfo(-1, TdsData.isUnicode(ci));
0197: pi.name = '@' + ci.realName;
0198: pi.collation = ci.collation;
0199: pi.charsetInfo = ci.charsetInfo;
0200: if (onInsertRow) {
0201: insertRow[colIndex] = pi;
0202: } else {
0203: updateRow[colIndex] = pi;
0204: }
0205: }
0206:
0207: if (value == null) {
0208: pi.value = null;
0209: pi.length = 0;
0210: pi.jdbcType = ci.jdbcType;
0211: pi.isSet = true;
0212: if (pi.jdbcType == Types.NUMERIC
0213: || pi.jdbcType == Types.DECIMAL) {
0214: pi.scale = TdsData.DEFAULT_SCALE;
0215: } else {
0216: pi.scale = 0;
0217: }
0218: } else {
0219: pi.value = value;
0220: pi.length = length;
0221: pi.isSet = true;
0222: pi.jdbcType = jdbcType;
0223: pi.isUnicode = "ntext".equals(ci.sqlType)
0224: || "nchar".equals(ci.sqlType)
0225: || "nvarchar".equals(ci.sqlType);
0226: if (pi.value instanceof BigDecimal) {
0227: pi.scale = ((BigDecimal) pi.value).scale();
0228: } else {
0229: pi.scale = 0;
0230: }
0231: }
0232:
0233: return value;
0234: }
0235:
0236: /**
0237: * Get the specified column's data item.
0238: *
0239: * @param index the column index in the row
0240: * @return the column value as an <code>Object</code>
0241: * @throws SQLException if the index is out of bounds or there is no
0242: * current row
0243: */
0244: protected Object getColumn(int index) throws SQLException {
0245: checkOpen();
0246:
0247: if (index < 1 || index > columnCount) {
0248: throw new SQLException(Messages
0249: .get("error.resultset.colindex", Integer
0250: .toString(index)), "07009");
0251: }
0252:
0253: Object[] currentRow;
0254: if (onInsertRow || (currentRow = getCurrentRow()) == null) {
0255: throw new SQLException(Messages
0256: .get("error.resultset.norow"), "24000");
0257: }
0258:
0259: if (SQL_ROW_DIRTY.equals(currentRow[columns.length - 1])) {
0260: cursorFetch(FETCH_REPEAT, 0);
0261: currentRow = getCurrentRow();
0262: }
0263:
0264: Object data = currentRow[index - 1];
0265: wasNull = data == null;
0266:
0267: return data;
0268: }
0269:
0270: /**
0271: * Translates a JDBC result set type into SQL Server native @scrollOpt value
0272: * for use with stored procedures such as sp_cursoropen, sp_cursorprepare
0273: * or sp_cursorprepexec.
0274: *
0275: * @param resultSetType JDBC result set type (one of the
0276: * <code>ResultSet.TYPE_<i>XXX</i></code>
0277: * values)
0278: * @param resultSetConcurrency JDBC result set concurrency (one of the
0279: * <code>ResultSet.CONCUR_<i>XXX</i></code>
0280: * values)
0281: * @return a value for the @scrollOpt parameter
0282: */
0283: static int getCursorScrollOpt(int resultSetType,
0284: int resultSetConcurrency, boolean parameterized) {
0285: int scrollOpt;
0286:
0287: switch (resultSetType) {
0288: case TYPE_SCROLL_INSENSITIVE:
0289: scrollOpt = CURSOR_TYPE_STATIC;
0290: break;
0291:
0292: case TYPE_SCROLL_SENSITIVE:
0293: scrollOpt = CURSOR_TYPE_KEYSET;
0294: break;
0295:
0296: case TYPE_SCROLL_SENSITIVE + 1:
0297: scrollOpt = CURSOR_TYPE_DYNAMIC;
0298: break;
0299:
0300: case TYPE_FORWARD_ONLY:
0301: default:
0302: scrollOpt = (resultSetConcurrency == CONCUR_READ_ONLY) ? (CURSOR_TYPE_FASTFORWARDONLY | CURSOR_TYPE_AUTO_FETCH)
0303: : CURSOR_TYPE_FORWARD;
0304: break;
0305: }
0306:
0307: // If using sp_cursoropen need to set a flag on scrollOpt.
0308: // The 0x1000 tells the server that there is a parameter
0309: // definition and user parameters present. If this flag is
0310: // not set the driver will ignore the additional parameters.
0311: if (parameterized) {
0312: scrollOpt |= CURSOR_TYPE_PARAMETERIZED;
0313: }
0314:
0315: return scrollOpt;
0316: }
0317:
0318: /**
0319: * Translates a JDBC result set concurrency into SQL Server native @ccOpt
0320: * value for use with stored procedures such as sp_cursoropen,
0321: * sp_cursorprepare or sp_cursorprepexec.
0322: *
0323: * @param resultSetConcurrency JDBC result set concurrency (one of the
0324: * <code>ResultSet.CONCUR_<i>XXX</i></code>
0325: * values)
0326: * @return a value for the @scrollOpt parameter
0327: */
0328: static int getCursorConcurrencyOpt(int resultSetConcurrency) {
0329: switch (resultSetConcurrency) {
0330: case CONCUR_UPDATABLE:
0331: return CURSOR_CONCUR_OPTIMISTIC;
0332:
0333: case CONCUR_UPDATABLE + 1:
0334: return CURSOR_CONCUR_SCROLL_LOCKS;
0335:
0336: case CONCUR_UPDATABLE + 2:
0337: return CURSOR_CONCUR_OPTIMISTIC_VALUES;
0338:
0339: case CONCUR_READ_ONLY:
0340: default:
0341: return CURSOR_CONCUR_READ_ONLY;
0342: }
0343: }
0344:
0345: /**
0346: * Create a new Cursor result set using the internal sp_cursoropen procedure.
0347: *
0348: * @param sql The SQL SELECT statement.
0349: * @param procName Optional procedure name for cursors based on a stored procedure.
0350: * @param parameters Optional stored procedure parameters.
0351: * @throws SQLException
0352: */
0353: private void cursorCreate(String sql, String procName,
0354: ParamInfo[] parameters) throws SQLException {
0355: TdsCore tds = statement.getTds();
0356: int prepareSql = statement.connection.getPrepareSql();
0357: Integer prepStmtHandle = null;
0358:
0359: //
0360: // If this cursor is going to be a named forward only cursor
0361: // force the concurrency to be updateable.
0362: // TODO: Cursor is updateable unless user appends FOR READ to the select
0363: // but we would need to parse the SQL to discover this.
0364: //
0365: if (cursorName != null
0366: && resultSetType == ResultSet.TYPE_FORWARD_ONLY
0367: && concurrency == ResultSet.CONCUR_READ_ONLY) {
0368: concurrency = ResultSet.CONCUR_UPDATABLE;
0369: }
0370: //
0371: // Simplify future tests for parameters
0372: //
0373: if (parameters != null && parameters.length == 0) {
0374: parameters = null;
0375: }
0376: //
0377: // SQL 6.5 does not support stored procs (with params) in the sp_cursor call
0378: // will need to substitute any parameter values into the SQL.
0379: //
0380: if (tds.getTdsVersion() == Driver.TDS42) {
0381: prepareSql = TdsCore.UNPREPARED;
0382: if (parameters != null) {
0383: procName = null;
0384: }
0385: }
0386: //
0387: // If we are running in unprepare mode and there are parameters
0388: // substitute these into the SQL statement now.
0389: //
0390: if (parameters != null && prepareSql == TdsCore.UNPREPARED) {
0391: sql = Support.substituteParameters(sql, parameters,
0392: statement.connection);
0393: parameters = null;
0394: }
0395: //
0396: // For most prepare modes we need to substitute parameter
0397: // names for the ? markers.
0398: //
0399: if (parameters != null) {
0400: if (procName == null || !procName.startsWith("#jtds")) {
0401: sql = Support.substituteParamMarkers(sql, parameters);
0402: }
0403: }
0404: //
0405: // There are generally three situations in which procName is not null:
0406: // 1. Running in prepareSQL=1 and contains a temp proc name e.g. #jtds00001
0407: // in which case we need to generate an SQL statement exec #jtds...
0408: // 2. Running in prepareSQL=4 and contains an existing statement handle.
0409: // 3. CallableStatement in which case the SQL string has a valid exec
0410: // statement and we can ignore procName.
0411: //
0412: if (procName != null) {
0413: if (procName.startsWith("#jtds")) {
0414: StringBuffer buf = new StringBuffer(procName.length()
0415: + 16
0416: + (parameters != null ? parameters.length * 5
0417: : 0));
0418: buf.append("EXEC ").append(procName).append(' ');
0419: for (int i = 0; parameters != null
0420: && i < parameters.length; i++) {
0421: if (i != 0) {
0422: buf.append(',');
0423: }
0424: if (parameters[i].name != null) {
0425: buf.append(parameters[i].name);
0426: } else {
0427: buf.append("@P").append(i);
0428: }
0429: }
0430: sql = buf.toString();
0431: } else if (TdsCore.isPreparedProcedureName(procName)) {
0432: //
0433: // Prepared Statement Handle
0434: // At present procName is set to the value obtained by
0435: // the connection.prepareSQL() call in JtdsPreparedStatement.
0436: // This handle was obtained using sp_cursorprepare not sp_prepare
0437: // so it's ok to use here.
0438: //
0439: try {
0440: prepStmtHandle = new Integer(procName);
0441: } catch (NumberFormatException e) {
0442: throw new IllegalStateException(
0443: "Invalid prepared statement handle: "
0444: + procName);
0445: }
0446: }
0447: }
0448:
0449: //
0450: // Select the correct type of Server side cursor to
0451: // match the scroll and concurrency options.
0452: //
0453: int scrollOpt = getCursorScrollOpt(resultSetType, concurrency,
0454: parameters != null);
0455: int ccOpt = getCursorConcurrencyOpt(concurrency);
0456: //
0457: // Create parameter objects
0458: //
0459: // Setup scroll options parameter
0460: //
0461: ParamInfo pScrollOpt = new ParamInfo(Types.INTEGER,
0462: new Integer(scrollOpt), ParamInfo.OUTPUT);
0463: //
0464: // Setup concurrency options parameter
0465: //
0466: ParamInfo pConCurOpt = new ParamInfo(Types.INTEGER,
0467: new Integer(ccOpt), ParamInfo.OUTPUT);
0468: //
0469: // Setup number of rows parameter
0470: //
0471: ParamInfo pRowCount = new ParamInfo(Types.INTEGER, new Integer(
0472: fetchSize), ParamInfo.OUTPUT);
0473: //
0474: // Setup cursor handle parameter
0475: //
0476: ParamInfo pCursor = new ParamInfo(Types.INTEGER, null,
0477: ParamInfo.OUTPUT);
0478: //
0479: // Setup statement handle param
0480: //
0481: ParamInfo pStmtHand = null;
0482: if (prepareSql == TdsCore.PREPARE) {
0483: pStmtHand = new ParamInfo(Types.INTEGER, prepStmtHandle,
0484: ParamInfo.OUTPUT);
0485: }
0486: //
0487: // Setup parameter definitions parameter
0488: //
0489: ParamInfo pParamDef = null;
0490: if (parameters != null) {
0491: // Parameter declarations
0492: for (int i = 0; i < parameters.length; i++) {
0493: TdsData.getNativeType(statement.connection,
0494: parameters[i]);
0495: }
0496:
0497: pParamDef = new ParamInfo(Types.LONGVARCHAR, Support
0498: .getParameterDefinitions(parameters),
0499: ParamInfo.UNICODE);
0500: }
0501: //
0502: // Setup SQL statement parameter
0503: //
0504: ParamInfo pSQL = new ParamInfo(Types.LONGVARCHAR, sql,
0505: ParamInfo.UNICODE);
0506: //
0507: // OK now open the Cursor
0508: //
0509: if (prepareSql == TdsCore.PREPARE && prepStmtHandle != null) {
0510: // Use sp_cursorexecute approach
0511: procName = "sp_cursorexecute";
0512: if (parameters == null) {
0513: parameters = new ParamInfo[5];
0514: } else {
0515: ParamInfo[] params = new ParamInfo[5 + parameters.length];
0516: System.arraycopy(parameters, 0, params, 5,
0517: parameters.length);
0518: parameters = params;
0519: }
0520: // Setup statement handle param
0521: pStmtHand.isOutput = false;
0522: pStmtHand.value = prepStmtHandle;
0523: parameters[0] = pStmtHand;
0524: // Setup cursor handle param
0525: parameters[1] = pCursor;
0526: // Setup scroll options (mask off parameter flag)
0527: pScrollOpt.value = new Integer(scrollOpt
0528: & ~CURSOR_TYPE_PARAMETERIZED);
0529: } else {
0530: // Use sp_cursoropen approach
0531: procName = "sp_cursoropen";
0532: if (parameters == null) {
0533: parameters = new ParamInfo[5];
0534: } else {
0535: ParamInfo[] params = new ParamInfo[6 + parameters.length];
0536: System.arraycopy(parameters, 0, params, 6,
0537: parameters.length);
0538: parameters = params;
0539: parameters[5] = pParamDef;
0540: }
0541: // Setup cursor handle param
0542: parameters[0] = pCursor;
0543: // Setup statement param
0544: parameters[1] = pSQL;
0545: }
0546: // Setup scroll options
0547: parameters[2] = pScrollOpt;
0548: // Setup concurrency options
0549: parameters[3] = pConCurOpt;
0550: // Setup numRows parameter
0551: parameters[4] = pRowCount;
0552:
0553: tds.executeSQL(null, procName, parameters, false, statement
0554: .getQueryTimeout(), statement.getMaxRows(), statement
0555: .getMaxFieldSize(), true);
0556:
0557: // Load column meta data and any eventual rows (fast forward cursors)
0558: processOutput(tds, true);
0559: if ((scrollOpt & CURSOR_TYPE_AUTO_FETCH) != 0) {
0560: // If autofetching, the cursor position is on the first row
0561: cursorPos = 1;
0562: }
0563:
0564: // Check the return value
0565: Integer retVal = tds.getReturnStatus();
0566: if ((retVal == null)
0567: || (retVal.intValue() != 0 && retVal.intValue() != 2)) {
0568: throw new SQLException(Messages
0569: .get("error.resultset.openfail"), "24000");
0570: }
0571:
0572: // Cursor is being built asynchronously so rowsInResult is not set
0573: asyncCursor = (retVal.intValue() == 2);
0574:
0575: //
0576: // Retrieve values of output parameters
0577: //
0578: PARAM_CURSOR_HANDLE.value = pCursor.getOutValue();
0579: int actualScroll = ((Integer) pScrollOpt.getOutValue())
0580: .intValue();
0581: int actualCc = ((Integer) pConCurOpt.getOutValue()).intValue();
0582: rowsInResult = ((Integer) pRowCount.getOutValue()).intValue();
0583:
0584: //
0585: // Set the cursor name if required allowing positioned updates.
0586: // We need to do this here as any downgrade warnings will be wiped
0587: // out by the executeSQL call.
0588: //
0589: if (cursorName != null) {
0590: ParamInfo params[] = new ParamInfo[3];
0591: params[0] = PARAM_CURSOR_HANDLE;
0592: PARAM_OPTYPE.value = new Integer(2);
0593: params[1] = PARAM_OPTYPE;
0594: params[2] = new ParamInfo(Types.VARCHAR, cursorName,
0595: ParamInfo.UNICODE);
0596: tds.executeSQL(null, "sp_cursoroption", params, true, 0,
0597: -1, -1, true);
0598: tds.clearResponseQueue();
0599: if (tds.getReturnStatus().intValue() != 0) {
0600: statement.getMessages().addException(
0601: new SQLException(Messages
0602: .get("error.resultset.openfail"),
0603: "24000"));
0604: }
0605: statement.getMessages().checkErrors();
0606: }
0607: //
0608: // Check for downgrade of scroll or concurrency options
0609: //
0610: if ((actualScroll != (scrollOpt & 0xFFF))
0611: || (actualCc != ccOpt)) {
0612: boolean downgradeWarning = false;
0613:
0614: if (actualScroll != scrollOpt) {
0615: int resultSetType;
0616: switch (actualScroll) {
0617: case CURSOR_TYPE_FORWARD:
0618: case CURSOR_TYPE_FASTFORWARDONLY:
0619: resultSetType = TYPE_FORWARD_ONLY;
0620: break;
0621:
0622: case CURSOR_TYPE_STATIC:
0623: resultSetType = TYPE_SCROLL_INSENSITIVE;
0624: break;
0625:
0626: case CURSOR_TYPE_KEYSET:
0627: resultSetType = TYPE_SCROLL_SENSITIVE;
0628: break;
0629:
0630: case CURSOR_TYPE_DYNAMIC:
0631: resultSetType = TYPE_SCROLL_SENSITIVE + 1;
0632: break;
0633:
0634: default:
0635: resultSetType = this .resultSetType;
0636: statement.getMessages().addWarning(
0637: new SQLWarning(Messages.get(
0638: "warning.cursortype", Integer
0639: .toString(actualScroll)),
0640: "01000"));
0641: }
0642: downgradeWarning = resultSetType < this .resultSetType;
0643: this .resultSetType = resultSetType;
0644: }
0645:
0646: if (actualCc != ccOpt) {
0647: int concurrency;
0648: switch (actualCc) {
0649: case CURSOR_CONCUR_READ_ONLY:
0650: concurrency = CONCUR_READ_ONLY;
0651: break;
0652:
0653: case CURSOR_CONCUR_OPTIMISTIC:
0654: concurrency = CONCUR_UPDATABLE;
0655: break;
0656:
0657: case CURSOR_CONCUR_SCROLL_LOCKS:
0658: concurrency = CONCUR_UPDATABLE + 1;
0659: break;
0660:
0661: case CURSOR_CONCUR_OPTIMISTIC_VALUES:
0662: concurrency = CONCUR_UPDATABLE + 2;
0663: break;
0664:
0665: default:
0666: concurrency = this .concurrency;
0667: statement.getMessages().addWarning(
0668: new SQLWarning(Messages.get(
0669: "warning.concurrtype", Integer
0670: .toString(actualCc)),
0671: "01000"));
0672: }
0673: downgradeWarning = concurrency < this .concurrency;
0674: this .concurrency = concurrency;
0675: }
0676:
0677: if (downgradeWarning) {
0678: // SAfe This warning goes to the Statement, not the ResultSet
0679: statement.addWarning(new SQLWarning(Messages.get(
0680: "warning.cursordowngraded", resultSetType + "/"
0681: + concurrency), "01000"));
0682: }
0683: }
0684: }
0685:
0686: /**
0687: * Fetch the next result row from a cursor using the internal sp_cursorfetch procedure.
0688: *
0689: * @param fetchType The type of fetch eg FETCH_ABSOLUTE.
0690: * @param rowNum The row number to fetch.
0691: * @return <code>boolean</code> true if a result set row is returned.
0692: * @throws SQLException
0693: */
0694: private boolean cursorFetch(Integer fetchType, int rowNum)
0695: throws SQLException {
0696: TdsCore tds = statement.getTds();
0697:
0698: statement.clearWarnings();
0699:
0700: if (fetchType != FETCH_ABSOLUTE && fetchType != FETCH_RELATIVE) {
0701: rowNum = 1;
0702: }
0703:
0704: ParamInfo[] param = new ParamInfo[4];
0705: // Setup cursor handle param
0706: param[0] = PARAM_CURSOR_HANDLE;
0707:
0708: // Setup fetchtype param
0709: PARAM_FETCHTYPE.value = fetchType;
0710: param[1] = PARAM_FETCHTYPE;
0711:
0712: // Setup rownum
0713: PARAM_ROWNUM_IN.value = new Integer(rowNum);
0714: param[2] = PARAM_ROWNUM_IN;
0715: // Setup numRows parameter
0716: if (((Integer) PARAM_NUMROWS_IN.value).intValue() != fetchSize) {
0717: // If the fetch size changed, update the parameter and cache size
0718: PARAM_NUMROWS_IN.value = new Integer(fetchSize);
0719: rowCache = new Object[fetchSize][];
0720: }
0721: param[3] = PARAM_NUMROWS_IN;
0722:
0723: synchronized (tds) {
0724: // No meta data, no timeout (we're not sending it yet), no row
0725: // limit, don't send yet
0726: tds.executeSQL(null, "sp_cursorfetch", param, true, 0, 0,
0727: statement.getMaxFieldSize(), false);
0728:
0729: // Setup fetchtype param
0730: PARAM_FETCHTYPE.value = FETCH_INFO;
0731: param[1] = PARAM_FETCHTYPE;
0732:
0733: // Setup rownum
0734: PARAM_ROWNUM_OUT.clearOutValue();
0735: param[2] = PARAM_ROWNUM_OUT;
0736: // Setup numRows parameter
0737: PARAM_NUMROWS_OUT.clearOutValue();
0738: param[3] = PARAM_NUMROWS_OUT;
0739:
0740: // No meta data, use the statement timeout, leave max rows as it is
0741: // (no limit), leave max field size as it is, send now
0742: tds.executeSQL(null, "sp_cursorfetch", param, true,
0743: statement.getQueryTimeout(), -1, -1, true);
0744: }
0745:
0746: // Load rows
0747: processOutput(tds, false);
0748:
0749: cursorPos = ((Integer) PARAM_ROWNUM_OUT.getOutValue())
0750: .intValue();
0751: if (fetchType != FETCH_REPEAT) {
0752: // Do not change ResultSet position when refreshing
0753: pos = cursorPos;
0754: }
0755: rowsInResult = ((Integer) PARAM_NUMROWS_OUT.getOutValue())
0756: .intValue();
0757: if (rowsInResult < 0) {
0758: // -1 = Dynamic cursor number of rows cannot be known.
0759: // -n = Async cursor = rows loaded so far
0760: rowsInResult = 0 - rowsInResult;
0761: }
0762:
0763: return getCurrentRow() != null;
0764: }
0765:
0766: /**
0767: * Support general cursor operations such as delete, update etc.
0768: *
0769: * @param opType the type of operation to perform
0770: * @param row the row number to update
0771: * @throws SQLException
0772: */
0773: private void cursor(Integer opType, ParamInfo[] row)
0774: throws SQLException {
0775: TdsCore tds = statement.getTds();
0776:
0777: statement.clearWarnings();
0778: ParamInfo param[];
0779:
0780: if (opType == CURSOR_OP_DELETE) {
0781: // 3 parameters for delete
0782: param = new ParamInfo[3];
0783: } else {
0784: if (row == null) {
0785: throw new SQLException(Messages
0786: .get("error.resultset.update"), "24000");
0787: }
0788: // 4 parameters plus one for each column for insert/update
0789: param = new ParamInfo[4 + columnCount];
0790: }
0791:
0792: // Setup cursor handle param
0793: param[0] = PARAM_CURSOR_HANDLE;
0794:
0795: // Setup optype param
0796: PARAM_OPTYPE.value = opType;
0797: param[1] = PARAM_OPTYPE;
0798:
0799: // Setup rownum
0800: PARAM_ROWNUM.value = new Integer(pos - cursorPos + 1);
0801: param[2] = PARAM_ROWNUM;
0802:
0803: // If row is not null, we're dealing with an insert/update
0804: if (row != null) {
0805: // Setup table
0806: param[3] = PARAM_TABLE;
0807:
0808: int colCnt = columnCount;
0809: // Current column; we will only update/insert columns for which
0810: // values were specified
0811: int crtCol = 4;
0812: // Name of the table to insert default values into (if necessary)
0813: String tableName = null;
0814:
0815: for (int i = 0; i < colCnt; i++) {
0816: ParamInfo pi = row[i];
0817: ColInfo col = columns[i];
0818:
0819: if (pi != null && pi.isSet) {
0820: if (!col.isWriteable) {
0821: // Column is read-only but was updated
0822: throw new SQLException(
0823: Messages.get("error.resultset.insert",
0824: Integer.toString(i + 1),
0825: col.realName), "24000");
0826: }
0827:
0828: param[crtCol++] = pi;
0829: }
0830: if (tableName == null && col.tableName != null) {
0831: if (col.catalog != null || col.schema != null) {
0832: tableName = (col.catalog != null ? col.catalog
0833: : "")
0834: + '.'
0835: + (col.schema != null ? col.schema : "")
0836: + '.' + col.tableName;
0837: } else {
0838: tableName = col.tableName;
0839: }
0840: }
0841: }
0842:
0843: if (crtCol == 4) {
0844: if (opType == CURSOR_OP_INSERT) {
0845: // Insert default values for all columns.
0846: // There seem to be two forms of sp_cursor: one with
0847: // parameter names and values and one w/o names and with
0848: // expressions (this is where 'default' comes in).
0849: param[crtCol] = new ParamInfo(Types.VARCHAR,
0850: "insert " + tableName + " default values",
0851: ParamInfo.UNICODE);
0852: crtCol++;
0853: } else {
0854: // No column to update so bail out!
0855: return;
0856: }
0857: }
0858:
0859: // If the count is different (i.e. there were read-only
0860: // columns) reallocate the parameters into a shorter array
0861: if (crtCol != colCnt + 4) {
0862: ParamInfo[] newParam = new ParamInfo[crtCol];
0863:
0864: System.arraycopy(param, 0, newParam, 0, crtCol);
0865: param = newParam;
0866: }
0867: }
0868:
0869: synchronized (tds) {
0870: // With meta data (we're not expecting any ResultSets), no timeout
0871: // (because we're not sending the request yet), don't alter max
0872: // rows, don't alter max field size, don't send yet
0873: tds.executeSQL(null, "sp_cursor", param, false, 0, -1, -1,
0874: false);
0875:
0876: if (param.length != 4) {
0877: param = new ParamInfo[4];
0878: param[0] = PARAM_CURSOR_HANDLE;
0879: }
0880:
0881: // Setup fetchtype param
0882: PARAM_FETCHTYPE.value = FETCH_INFO;
0883: param[1] = PARAM_FETCHTYPE;
0884:
0885: // Setup rownum
0886: PARAM_ROWNUM_OUT.clearOutValue();
0887: param[2] = PARAM_ROWNUM_OUT;
0888: // Setup numRows parameter
0889: PARAM_NUMROWS_OUT.clearOutValue();
0890: param[3] = PARAM_NUMROWS_OUT;
0891:
0892: // No meta data (no ResultSets expected), use statement timeout,
0893: // don't alter max rows, don't alter max field size, send now
0894: tds.executeSQL(null, "sp_cursorfetch", param, true,
0895: statement.getQueryTimeout(), -1, -1, true);
0896: }
0897:
0898: // Consume the sp_cursor response
0899: tds.consumeOneResponse();
0900: statement.getMessages().checkErrors();
0901: Integer retVal = tds.getReturnStatus();
0902: if (retVal.intValue() != 0) {
0903: throw new SQLException(Messages
0904: .get("error.resultset.cursorfail"), "24000");
0905: }
0906:
0907: //
0908: // Allow row values to be garbage collected
0909: //
0910: if (row != null) {
0911: for (int i = 0; i < row.length; i++) {
0912: if (row[i] != null) {
0913: row[i].clearInValue();
0914: }
0915: }
0916: }
0917:
0918: // Consume the sp_cursorfetch response
0919: tds.clearResponseQueue();
0920: statement.getMessages().checkErrors();
0921: cursorPos = ((Integer) PARAM_ROWNUM_OUT.getOutValue())
0922: .intValue();
0923: rowsInResult = ((Integer) PARAM_NUMROWS_OUT.getOutValue())
0924: .intValue();
0925:
0926: // Update row status
0927: if (opType == CURSOR_OP_DELETE || opType == CURSOR_OP_UPDATE) {
0928: Object[] currentRow = getCurrentRow();
0929: if (currentRow == null) {
0930: throw new SQLException(Messages
0931: .get("error.resultset.updatefail"), "24000");
0932: }
0933: // No need to re-fetch the row, just mark it as deleted or dirty
0934: currentRow[columns.length - 1] = (opType == CURSOR_OP_DELETE) ? SQL_ROW_DELETED
0935: : SQL_ROW_DIRTY;
0936: }
0937: }
0938:
0939: /**
0940: * Close a server side cursor.
0941: *
0942: * @throws SQLException
0943: */
0944: private void cursorClose() throws SQLException {
0945: TdsCore tds = statement.getTds();
0946:
0947: statement.clearWarnings();
0948:
0949: // Consume rest of output and remember any exceptions
0950: tds.clearResponseQueue();
0951: SQLException ex = statement.getMessages().exceptions;
0952:
0953: ParamInfo param[] = new ParamInfo[1];
0954:
0955: // Setup cursor handle param
0956: param[0] = PARAM_CURSOR_HANDLE;
0957:
0958: tds.executeSQL(null, "sp_cursorclose", param, false, statement
0959: .getQueryTimeout(), -1, -1, true);
0960: tds.clearResponseQueue();
0961:
0962: if (ex != null) {
0963: ex.setNextException(statement.getMessages().exceptions);
0964: throw ex;
0965: } else {
0966: statement.getMessages().checkErrors();
0967: }
0968: }
0969:
0970: /**
0971: * Processes the output of a cursor open or fetch operation. Fetches a
0972: * batch of rows from the <code>TdsCore</code>, loading them into the row
0973: * cache and optionally sets the column meta data (if called on cursor
0974: * open). Consumes all the response and checks for server returned errors.
0975: *
0976: * @param tds the <code>TdsCore</code> instance
0977: * @param setMeta whether column meta data needs to be loaded (cursor open)
0978: * @throws SQLException if an error occurs or an error message is returned
0979: * by the server
0980: */
0981: private void processOutput(TdsCore tds, boolean setMeta)
0982: throws SQLException {
0983: while (!tds.getMoreResults() && !tds.isEndOfResponse())
0984: ;
0985:
0986: int i = 0;
0987: if (tds.isResultSet()) {
0988: // Set column meta data if necessary
0989: if (setMeta) {
0990: this .columns = copyInfo(tds.getColumns());
0991: this .columnCount = getColumnCount(columns);
0992: }
0993: // With TDS 7 the data row (if any) is sent without any
0994: // preceding resultset header.
0995: // With TDS 8 there is a dummy result set header first
0996: // then the data. This case also used if meta data not supressed.
0997: if (tds.isRowData() || tds.getNextRow()) {
0998: do {
0999: rowCache[i++] = copyRow(tds.getRowData());
1000: } while (tds.getNextRow());
1001: }
1002: } else if (setMeta) {
1003: statement.getMessages().addException(
1004: new SQLException(Messages
1005: .get("error.statement.noresult"), "24000"));
1006: }
1007:
1008: // Set the rest of the rows to null
1009: for (; i < rowCache.length; ++i) {
1010: rowCache[i] = null;
1011: }
1012:
1013: tds.clearResponseQueue();
1014: statement.messages.checkErrors();
1015: }
1016:
1017: //
1018: // -------------------- java.sql.ResultSet methods -------------------
1019: //
1020:
1021: public void afterLast() throws SQLException {
1022: checkOpen();
1023: checkScrollable();
1024:
1025: if (pos != POS_AFTER_LAST) {
1026: // SAfe Just fetch a very large absolute value
1027: cursorFetch(FETCH_ABSOLUTE, Integer.MAX_VALUE);
1028: }
1029: }
1030:
1031: public void beforeFirst() throws SQLException {
1032: checkOpen();
1033: checkScrollable();
1034:
1035: if (pos != POS_BEFORE_FIRST) {
1036: cursorFetch(FETCH_ABSOLUTE, 0);
1037: }
1038: }
1039:
1040: public void cancelRowUpdates() throws SQLException {
1041: checkOpen();
1042: checkUpdateable();
1043:
1044: if (onInsertRow) {
1045: throw new SQLException(Messages
1046: .get("error.resultset.insrow"), "24000");
1047: }
1048:
1049: for (int i = 0; updateRow != null && i < updateRow.length; i++) {
1050: if (updateRow[i] != null) {
1051: updateRow[i].clearInValue();
1052: }
1053: }
1054: }
1055:
1056: public void close() throws SQLException {
1057: if (!closed) {
1058: try {
1059: if (!statement.getConnection().isClosed()) {
1060: cursorClose();
1061: }
1062: } finally {
1063: closed = true;
1064: statement = null;
1065: }
1066: }
1067: }
1068:
1069: public void deleteRow() throws SQLException {
1070: checkOpen();
1071: checkUpdateable();
1072:
1073: if (getCurrentRow() == null) {
1074: throw new SQLException(Messages
1075: .get("error.resultset.norow"), "24000");
1076: }
1077:
1078: if (onInsertRow) {
1079: throw new SQLException(Messages
1080: .get("error.resultset.insrow"), "24000");
1081: }
1082:
1083: cursor(CURSOR_OP_DELETE, null);
1084: }
1085:
1086: public void insertRow() throws SQLException {
1087: checkOpen();
1088: checkUpdateable();
1089:
1090: if (!onInsertRow) {
1091: throw new SQLException(Messages
1092: .get("error.resultset.notinsrow"), "24000");
1093: }
1094:
1095: cursor(CURSOR_OP_INSERT, insertRow);
1096: }
1097:
1098: public void moveToCurrentRow() throws SQLException {
1099: checkOpen();
1100: checkUpdateable();
1101:
1102: onInsertRow = false;
1103: }
1104:
1105: public void moveToInsertRow() throws SQLException {
1106: checkOpen();
1107: checkUpdateable();
1108: if (insertRow == null) {
1109: insertRow = new ParamInfo[columnCount];
1110: }
1111: onInsertRow = true;
1112: }
1113:
1114: public void refreshRow() throws SQLException {
1115: checkOpen();
1116:
1117: if (onInsertRow) {
1118: throw new SQLException(Messages
1119: .get("error.resultset.insrow"), "24000");
1120: }
1121:
1122: cursorFetch(FETCH_REPEAT, 0);
1123: }
1124:
1125: public void updateRow() throws SQLException {
1126: checkOpen();
1127: checkUpdateable();
1128:
1129: if (getCurrentRow() == null) {
1130: throw new SQLException(Messages
1131: .get("error.resultset.norow"), "24000");
1132: }
1133:
1134: if (onInsertRow) {
1135: throw new SQLException(Messages
1136: .get("error.resultset.insrow"), "24000");
1137: }
1138:
1139: if (updateRow != null) {
1140: cursor(CURSOR_OP_UPDATE, updateRow);
1141: }
1142: }
1143:
1144: public boolean first() throws SQLException {
1145: checkOpen();
1146: checkScrollable();
1147:
1148: pos = 1;
1149: if (getCurrentRow() == null) {
1150: return cursorFetch(FETCH_FIRST, 0);
1151: } else {
1152: return true;
1153: }
1154: }
1155:
1156: // FIXME Make the isXXX() methods work with forward-only cursors (rowsInResult == -1)
1157: public boolean isLast() throws SQLException {
1158: checkOpen();
1159:
1160: return (pos == rowsInResult) && (rowsInResult != 0);
1161: }
1162:
1163: public boolean last() throws SQLException {
1164: checkOpen();
1165: checkScrollable();
1166:
1167: pos = rowsInResult;
1168: if (asyncCursor || getCurrentRow() == null) {
1169: if (cursorFetch(FETCH_LAST, 0)) {
1170: // Set pos to the last row, as the number of rows can change
1171: pos = rowsInResult;
1172: return true;
1173: } else {
1174: return false;
1175: }
1176: } else {
1177: return true;
1178: }
1179: }
1180:
1181: public boolean next() throws SQLException {
1182: checkOpen();
1183:
1184: ++pos;
1185: if (getCurrentRow() == null) {
1186: return cursorFetch(FETCH_NEXT, 0);
1187: } else {
1188: return true;
1189: }
1190: }
1191:
1192: public boolean previous() throws SQLException {
1193: checkOpen();
1194: checkScrollable();
1195:
1196: // Don't bother if we're already before the first row
1197: if (pos == POS_BEFORE_FIRST) {
1198: return false;
1199: }
1200:
1201: // Save current ResultSet position
1202: int initPos = pos;
1203: // Decrement current position
1204: --pos;
1205: if (initPos == POS_AFTER_LAST || getCurrentRow() == null) {
1206: boolean res = cursorFetch(FETCH_PREVIOUS, 0);
1207: pos = (initPos == POS_AFTER_LAST) ? rowsInResult
1208: : (initPos - 1);
1209: return res;
1210: } else {
1211: return true;
1212: }
1213: }
1214:
1215: public boolean rowDeleted() throws SQLException {
1216: checkOpen();
1217:
1218: Object[] currentRow = getCurrentRow();
1219:
1220: // If there is no current row, return false (the row was not deleted)
1221: if (currentRow == null) {
1222: return false;
1223: }
1224:
1225: // Reload if dirty
1226: if (SQL_ROW_DIRTY.equals(currentRow[columns.length - 1])) {
1227: cursorFetch(FETCH_REPEAT, 0);
1228: currentRow = getCurrentRow();
1229: }
1230:
1231: return SQL_ROW_DELETED.equals(currentRow[columns.length - 1]);
1232: }
1233:
1234: public boolean rowInserted() throws SQLException {
1235: checkOpen();
1236: // No way to find out
1237: return false;
1238: }
1239:
1240: public boolean rowUpdated() throws SQLException {
1241: checkOpen();
1242: // No way to find out
1243: return false;
1244: }
1245:
1246: public boolean absolute(int row) throws SQLException {
1247: checkOpen();
1248: checkScrollable();
1249:
1250: pos = (row >= 0) ? row : (rowsInResult - row + 1);
1251: if (getCurrentRow() == null) {
1252: boolean result = cursorFetch(FETCH_ABSOLUTE, row);
1253: if (cursorPos == 1 && row + rowsInResult < 0) {
1254: pos = 0;
1255: result = false;
1256: }
1257: return result;
1258: } else {
1259: return true;
1260: }
1261: }
1262:
1263: public boolean relative(int row) throws SQLException {
1264: checkOpen();
1265: checkScrollable();
1266:
1267: pos = (pos == POS_AFTER_LAST) ? (rowsInResult + 1 + row)
1268: : (pos + row);
1269: if (getCurrentRow() == null) {
1270: if (pos < cursorPos) {
1271: // If fetching backwards fetch the row and the rows before it,
1272: // then restore pos
1273: int savePos = pos;
1274: boolean result = cursorFetch(FETCH_RELATIVE, pos
1275: - cursorPos - fetchSize + 1);
1276: if (result) {
1277: pos = savePos;
1278: } else {
1279: pos = POS_BEFORE_FIRST;
1280: }
1281: return result;
1282: } else {
1283: return cursorFetch(FETCH_RELATIVE, pos - cursorPos);
1284: }
1285: } else {
1286: return true;
1287: }
1288: }
1289:
1290: protected Object[] getCurrentRow() {
1291: if (pos < cursorPos || pos >= cursorPos + rowCache.length) {
1292: return null;
1293: }
1294:
1295: return rowCache[pos - cursorPos];
1296: }
1297: }
|