mscursorresultset.java

第三方的SQL Server and Sybase的jdbc dirver,速度更快

                }
                sql = buf.toString();
            } else if (TdsCore.isPreparedProcedureName(procName)) {
                //
                // Prepared Statement Handle
                // At present procName is set to the value obtained by
                // the connection.prepareSQL() call in JtdsPreparedStatement.
                // This handle was obtained using sp_cursorprepare not sp_prepare
                // so it's ok to use here.
                //
                try {
                    prepStmtHandle = new Integer(procName);
                } catch (NumberFormatException e) {
                    throw new IllegalStateException(
                               "Invalid prepared statement handle: " +
                                      procName);
                }
            }
        }

        //
        // Select the correct type of Server side cursor to
        // match the scroll and concurrency options.
        //
        int scrollOpt = getCursorScrollOpt(resultSetType, concurrency,
                parameters != null);
        int ccOpt = getCursorConcurrencyOpt(concurrency);
        //
        // Create parameter objects
        //
        // Setup scroll options parameter
        //
        ParamInfo pScrollOpt  = new ParamInfo(Types.INTEGER, new Integer(scrollOpt), ParamInfo.OUTPUT);
        //
        // Setup concurrency options parameter
        //
        ParamInfo pConCurOpt  = new ParamInfo(Types.INTEGER, new Integer(ccOpt), ParamInfo.OUTPUT);
        //
        // Setup number of rows parameter
        //
        ParamInfo pRowCount   = new ParamInfo(Types.INTEGER, new Integer(fetchSize), ParamInfo.OUTPUT);
        //
        // Setup cursor handle parameter
        //
        ParamInfo pCursor = new ParamInfo(Types.INTEGER, null, ParamInfo.OUTPUT);
        //
        // Setup statement handle param
        //
        ParamInfo pStmtHand = null;
        if (prepareSql == TdsCore.PREPARE) {
            pStmtHand = new ParamInfo(Types.INTEGER, prepStmtHandle, ParamInfo.OUTPUT);
        }
        //
        // Setup parameter definitions parameter
        //
        ParamInfo pParamDef = null;
        if (parameters != null ) {
            // Parameter declarations
            for (int i = 0; i < parameters.length; i++) {
                TdsData.getNativeType(statement.connection, parameters[i]);
            }

            pParamDef  = new ParamInfo(Types.LONGVARCHAR,
                    Support.getParameterDefinitions(parameters),
                    ParamInfo.UNICODE);
        }
        //
        // Setup SQL statement parameter
        //
        ParamInfo pSQL = new ParamInfo(Types.LONGVARCHAR, sql, ParamInfo.UNICODE);
        //
        // OK now open the Cursor
        //
        if (prepareSql == TdsCore.PREPARE && prepStmtHandle != null) {
            // Use sp_cursorexecute approach
            procName = "sp_cursorexecute";
            if (parameters == null) {
                parameters = new ParamInfo[5];
            } else {
                ParamInfo[] params = new ParamInfo[5 + parameters.length];
                System.arraycopy(parameters, 0, params, 5, parameters.length);
                parameters = params;
            }
            // Setup statement handle param
            pStmtHand.isOutput = false;
            pStmtHand.value = prepStmtHandle;
            parameters[0] = pStmtHand;
            // Setup cursor handle param
            parameters[1] = pCursor;
            // Setup scroll options (mask off parameter flag)
            pScrollOpt.value = new Integer(scrollOpt & ~CURSOR_TYPE_PARAMETERIZED);
        } else {
            // Use sp_cursoropen approach
            procName = "sp_cursoropen";
            if (parameters == null) {
                parameters = new ParamInfo[5];
            } else {
                ParamInfo[] params = new ParamInfo[6 + parameters.length];
                System.arraycopy(parameters, 0, params, 6, parameters.length);
                parameters = params;
                parameters[5] = pParamDef;
            }
            // Setup cursor handle param
            parameters[0] = pCursor;
            // Setup statement param
            parameters[1] = pSQL;
        }
        // Setup scroll options
        parameters[2] = pScrollOpt;
        // Setup concurrency options
        parameters[3] = pConCurOpt;
        // Setup numRows parameter
        parameters[4] = pRowCount;

        tds.executeSQL(null, procName, parameters, false,
                statement.getQueryTimeout(), statement.getMaxRows(),
                statement.getMaxFieldSize(), true);

        // Load column meta data and any eventual rows (fast forward cursors)
        processOutput(tds, true);
        if ((scrollOpt & CURSOR_TYPE_AUTO_FETCH) != 0) {
            // If autofetching, the cursor position is on the first row
            cursorPos = 1;
        }

        // Check the return value
        Integer retVal = tds.getReturnStatus();
        if ((retVal == null) || (retVal.intValue() != 0)) {
            throw new SQLException(Messages.get("error.resultset.openfail"), "24000");
        }

        //
        // Retrieve values of output parameters
        //
        PARAM_CURSOR_HANDLE.value = pCursor.getOutValue();
        int actualScroll = ((Integer) pScrollOpt.getOutValue()).intValue();
        int actualCc     = ((Integer) pConCurOpt.getOutValue()).intValue();
        rowsInResult = ((Integer) pRowCount.getOutValue()).intValue();

        //
        // Set the cursor name if required allowing positioned updates.
        // We need to do this here as any downgrade warnings will be wiped
        // out by the executeSQL call.
        //
        if (cursorName != null) {
            ParamInfo params[] = new ParamInfo[3];
            params[0] = PARAM_CURSOR_HANDLE;
            PARAM_OPTYPE.value = new Integer(2);
            params[1] = PARAM_OPTYPE;
            params[2] = new ParamInfo(Types.VARCHAR, cursorName, ParamInfo.UNICODE);
            tds.executeSQL(null, "sp_cursoroption", params, true, 0, -1, -1, true);
            tds.clearResponseQueue();
            if (tds.getReturnStatus().intValue() != 0) {
                statement.getMessages().addException(
                        new SQLException(Messages.get("error.resultset.openfail"), "24000"));
            }
            statement.getMessages().checkErrors();
        }
        //
        // Check for downgrade of scroll or concurrency options
        //
        if ((actualScroll != (scrollOpt & 0xFFF)) || (actualCc != ccOpt)) {
            boolean downgradeWarning = false;

            if (actualScroll != scrollOpt) {
                int resultSetType;
                switch (actualScroll) {
                    case CURSOR_TYPE_FORWARD:
                    case CURSOR_TYPE_FASTFORWARDONLY:
                        resultSetType = TYPE_FORWARD_ONLY;
                        break;

                    case CURSOR_TYPE_STATIC:
                        resultSetType = TYPE_SCROLL_INSENSITIVE;
                        break;

                    case CURSOR_TYPE_KEYSET:
                        resultSetType = TYPE_SCROLL_SENSITIVE;
                        break;

                    case CURSOR_TYPE_DYNAMIC:
                        resultSetType = TYPE_SCROLL_SENSITIVE + 1;
                        break;

                    default:
                        resultSetType = this.resultSetType;
                        statement.getMessages().addWarning(new SQLWarning(
                                Messages.get("warning.cursortype", Integer.toString(actualScroll)),
                                "01000"));
                }
                downgradeWarning = resultSetType < this.resultSetType;
                this.resultSetType = resultSetType;
            }

            if (actualCc != ccOpt) {
                int concurrency;
                switch (actualCc) {
                    case CURSOR_CONCUR_READ_ONLY:
                        concurrency = CONCUR_READ_ONLY;
                        break;

                    case CURSOR_CONCUR_OPTIMISTIC:
                        concurrency = CONCUR_UPDATABLE;
                        break;

                    case CURSOR_CONCUR_SCROLL_LOCKS:
                        concurrency = CONCUR_UPDATABLE + 1;
                        break;

                    case CURSOR_CONCUR_OPTIMISTIC_VALUES:
                        concurrency = CONCUR_UPDATABLE + 2;
                        break;

                    default:
                        concurrency = this.concurrency;
                        statement.getMessages().addWarning(new SQLWarning(
                                Messages.get("warning.concurrtype", Integer.toString(actualCc)),
                                "01000"));
                }
                downgradeWarning = concurrency < this.concurrency;
                this.concurrency = concurrency;
            }

            if (downgradeWarning) {
                // SAfe This warning goes to the Statement, not the ResultSet
                statement.addWarning(new SQLWarning(
                        Messages.get( "warning.cursordowngraded",
                                resultSetType + "/" + concurrency),
                        "01000"));
            }
        }
    }

    /**
     * Fetch the next result row from a cursor using the internal sp_cursorfetch procedure.
     *
     * @param fetchType The type of fetch eg FETCH_ABSOLUTE.
     * @param rowNum The row number to fetch.
     * @return <code>boolean</code> true if a result set row is returned.
     * @throws SQLException
     */
    private boolean cursorFetch(Integer fetchType, int rowNum)
            throws SQLException {
        TdsCore tds = statement.getTds();

        statement.clearWarnings();

        if (fetchType != FETCH_ABSOLUTE && fetchType != FETCH_RELATIVE) {
            rowNum = 1;
        }

        ParamInfo[] param = new ParamInfo[4];
        // Setup cursor handle param
        param[0] = PARAM_CURSOR_HANDLE;

        // Setup fetchtype param
        PARAM_FETCHTYPE.value = fetchType;
        param[1] = PARAM_FETCHTYPE;

        // Setup rownum
        PARAM_ROWNUM_IN.value = new Integer(rowNum);
        param[2] = PARAM_ROWNUM_IN;
        // Setup numRows parameter
        if (((Integer) PARAM_NUMROWS_IN.value).intValue() != fetchSize) {
            // If the fetch size changed, update the parameter and cache size
            PARAM_NUMROWS_IN.value = new Integer(fetchSize);
            rowCache = new Object[fetchSize][];
        }
        param[3] = PARAM_NUMROWS_IN;

        synchronized (tds) {
            // No meta data, no timeout (we're not sending it yet), no row
            // limit, don't send yet
            tds.executeSQL(null, "sp_cursorfetch", param, true, 0, 0,
                    statement.getMaxFieldSize(), false);

            // Setup fetchtype param
            PARAM_FETCHTYPE.value = FETCH_INFO;
            param[1] = PARAM_FETCHTYPE;

            // Setup rownum
            PARAM_ROWNUM_OUT.clearOutValue();
            param[2] = PARAM_ROWNUM_OUT;
            // Setup numRows parameter
            PARAM_NUMROWS_OUT.clearOutValue();
            param[3] = PARAM_NUMROWS_OUT;

            // No meta data, use the statement timeout, leave max rows as it is
            // (no limit), leave max field size as it is, send now
            tds.executeSQL(null, "sp_cursorfetch", param, true,
                    statement.getQueryTimeout(), -1, -1, true);
        }

        // Load rows
        processOutput(tds, false);

        cursorPos = ((Integer) PARAM_ROWNUM_OUT.getOutValue()).intValue();
        if (fetchType != FETCH_REPEAT) {
            // Do not change ResultSet position when refreshing
            pos = cursorPos;
        }
        rowsInResult = ((Integer) PARAM_NUMROWS_OUT.getOutValue()).intValue();

        return getCurrentRow() != null;
    }

    /**
     * Support general cursor operations such as delete, update etc.
     *
     * @param opType the type of operation to perform
     * @param row    the row number to update
     * @throws SQLException
     */
    private void cursor(Integer opType , ParamInfo[] row) throws SQLException {
        TdsCore tds = statement.getTds();

        statement.clearWarnings();
        ParamInfo param[];

        if (opType == CURSOR_OP_DELETE) {
            // 3 parameters for delete
            param = new ParamInfo[3];
        } else {
            if (row == null) {
                throw new SQLException(Messages.get("error.resultset.update"), "24000");
            }
            // 4 parameters plus one for each column for insert/update
            param = new ParamInfo[4 + columnCount];
        }

        // Setup cursor handle param
        param[0] = PARAM_CURSOR_HANDLE;

        // Setup optype param
        PARAM_OPTYPE.value = opType;
        param[1] = PARAM_OPTYPE;

        // Setup rownum
        PARAM_ROWNUM.value = new Integer(pos - cursorPos + 1);
        param[2] = PARAM_ROWNUM;

        // If row is not null, we're dealing with an insert/update
        if (row != null) {
            // Setup table
            param[3] = PARAM_TABLE;

            int colCnt = columnCount;
            // Current column; we will only update/insert columns for which
            // values were specified
            int crtCol = 4;
            // Name of the table to insert default values into (if necessary)
            String tableName = null;

            for (int i = 0; i < colCnt; i++) {
                ParamInfo pi = row[i];
                ColInfo col = columns[i];

                if (pi != null && pi.isSet) {
                    if (!col.isWriteable) {
                        // Column is read-only but was updated
                        throw new SQLException(Messages.get("error.resultset.insert",
                                Integer.toString(i + 1), col.realName), "24000");
                    }

                    param[crtCol++] = pi;
                }
                if (tableName == null && col.tableName != null) {
                    if (col.catalog != null || col.schema != null) {
                        tableName = (col.catalog != null ? col.catalog : "")
                                + '.' + (col.schema != null ? col.schema : "")
                                + '.' + col.tableName;
                    } else {
                        tableName = col.tableName;
                    }
                }
            }

            if (crtCol == 4) {
                if (opType == CURSOR_OP_INSERT) {
                    // Insert default values for all columns.
                    // There seem to be two forms of sp_cursor: one with
                    // parameter names and values and one w/o names and with
                    // expressions (this is where 'default' comes in).
                    param[crtCol] = new ParamInfo(Types.VARCHAR,
                            "insert " + tableName + " default values",
                            ParamInfo.UNICODE);
                    crtCol++;
                } else {
                    // No column to update so bail out!
                    return;
                }
            }

            // If the count is different (i.e. there were read-only
            // columns) reallocate the parameters into a shorter array
            if (crtCol != colCnt + 4) {
                ParamInfo[] newParam = new ParamInfo[crtCol];

                System.arraycopy(param, 0, newParam, 0, crtCol);
                param = newParam;
            }
        }

        synchronized (tds) {