com.microsoft.sqlserver.jdbc.SQLServerException:索引8超出范围

Question

我正在尝试以下代码：

public class StoredProcedureParam 
{
    private String m_sType; 
    private String m_sValue;
    private String m_sParamName;

    public StoredProcedureParam(String a_sParamName, String a_sType, String a_sValue)
    {
        m_sType = a_sType;
        m_sValue = a_sValue;
        m_sParamName = a_sParamName;
    }
}

ArrayList<StoredProcedureParam> spmArr = new ArrayList<StoredProcedureParam>();

spmArr.add(new StoredProcedureParam("sBridgePhone",             "NString",  "value1"));
spmArr.add(new StoredProcedureParam("sCallerPaidTelNumber",             "NString",  "value2"));
spmArr.add(new StoredProcedureParam("sTollFreeTelNumber",       "NString",  "default"));
spmArr.add(new StoredProcedureParam("sParticipantAccessCode",   "NString",  "value3"));
spmArr.add(new StoredProcedureParam("sHostPassword",            "NString",  "value4"));
spmArr.add(new StoredProcedureParam("tNowUtc",                  "output",   "timestamp"));
spmArr.add(new StoredProcedureParam("nStatusCode",              "output",   "Int"));

if (!m_jdbcWrapper.callStoredProcedure("{call spAddConference(?,?,?,?,?,?,?)}", spmArr)) {
    System.out.println("callAddConferenceSp - Failed to execute");
    return "";
}


public boolean callStoredProcedure(String a_sStoredProcedure, ArrayList<StoredProcedureParam> a_ParamList)
{                   
    try 
    {
        connect();          

        m_cStatement = m_Connection.prepareCall(a_sStoredProcedure,
                                            ResultSet.TYPE_SCROLL_INSENSITIVE, 
                                            ResultSet.CONCUR_READ_ONLY);

        registerSpParams(a_ParamList);

        m_cStatement.execute();         
    } 
    catch (SQLException ex) 
    {
        ex.printStackTrace();
        return false;
    }
    finally 
    {
        try 
        {
            m_cStatement.close();
            System.out.println("JDBCWrapper - Statement Closed!");
            m_Connection.close();
            System.out.println("JDBCWrapper - Connection Closed!");             
        } 
        catch (SQLException e) 
        {
            e.printStackTrace();
        }
    }

    return true;
}


private void registerSpParams(ArrayList<StoredProcedureParam> a_ParamList) throws SQLException {

        if (a_ParamList.size() > 0)
        {
            for (int i = 1; i <= a_ParamList.size(); i++)
            {
                StoredProcedureParam spp = a_ParamList.get(i - 1);
                switch (TypesEnum.valueOf(spp.getType()))
                {
                    case Integer:
                        m_cStatement.setInt(spp.getParamName(), Integer.parseInt(spp.getValue()));
                        break;
                    case String: 
                        m_cStatement.setString(spp.getParamName(), spp.getValue());
                        break;
                    case NString:
                        m_cStatement.setNString(spp.getParamName(), spp.getValue());
                        break;
                    case Timestamp:
                        m_cStatement.setTimestamp(spp.getParamName(), Timestamp.valueOf(spp.getValue()));
                        break;
                    case output:
                        switch (TypesEnum.valueOf(spp.getValue()))
                        {
                            case Integer:
                                m_cStatement.registerOutParameter(spp.getParamName(), java.sql.Types.INTEGER);
                                break;
                            case Timestamp:
                                m_cStatement.registerOutParameter(spp.getParamName(), java.sql.Types.TIMESTAMP);
                                break;
                            default:
                                break;
                        }
                        break;
                    case bit:
                        m_cStatement.setByte(spp.getParamName(), Byte.parseByte(spp.getValue()));
                        break;
                    case Bigint:
                        m_cStatement.setBigDecimal(spp.getParamName(), BigDecimal.valueOf(Long.parseLong(spp.getValue())));
                        break;
                }
            }
        }
    }

由于某种原因，我在subject中遇到了异常：

com.microsoft.sqlserver.jdbc.SQLServerException: The index 8 is out of range.
      at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
      at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.registerOutParameter(SQLServerCallableStatement.java:75)
      at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.registerOutParameter(SQLServerCallableStatement.java:1735)
      at com.att.ecm.jdbcwrapper.JDBCWrapper.registerSpParams(JDBCWrapper.java:216)
      at com.att.ecm.jdbcwrapper.JDBCWrapper.callStoredProcedure(JDBCWrapper.java:138)
      at com.client.main.ConferenceStoredProcedures.callAddConferenceSp(ConferenceStoredProcedures.java:74)
      at com.main.Main.startFlow(Main.java:51)
      at com.main.Main.executeFlow(Main.java:34)
      at com.main.Main.main(Main.java:26)

却不能理解为什么？据我所知，我设置了7个问号和7个参数。那么，为什么它会在第8个索引上抛出异常呢？

你能给我一些建议吗？

致以最好的问候，塔尔

Answer 1

问题是存储过程有比7更多的参数。它有9个。当我添加2个参数并在调用字符串中添加2个问号时- excption没有发生。

Answer 2

您需要的是一个可以prepareCall的CallableStatement对象。

创建一个CallableStatement对象来调用数据库存储过程。CallableStatement对象提供用于设置其IN和OUT参数的方法，以及用于执行对存储过程的调用的方法。

要更改上面的代码，需要在set方法中使用index

像m_cStatement.setInt(i,...一样

Answer 3

当我映射了一个参数，但实际上并没有在查询中使用该参数时，就会发生这种情况。令人惊讶的是，参数没有被简单地忽略。