在使用 Java 调用 Spring 存储过程时出现错误的语法错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6774336/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting a bad grammar error in a call to a Spring Stored Procedure with Java
提问by Andrew Cooper
I'm having to debug some code written by someone else and I've run into a problem in one of the DAO implementations. We're using Java 1.6 and Spring (I think there's some hibernate in some portions of the application but don't think they come into play here.) The code when it runs is erroring on the "outMap = super.execute(inMap);" line of code. The error it throws is
我不得不调试其他人编写的一些代码，并且在 DAO 实现之一中遇到了问题。我们正在使用 Java 1.6 和 Spring（我认为在应用程序的某些部分有一些休眠，但不认为它们在这里起作用。）运行时的代码在“outMap = super.execute(inMap)”上出错;" 代码行。它抛出的错误是
SystemErr R callHistorySP() org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call DB2ADMIN/QUOTEACCESSORIALS(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}]; nested exception is java.sql.SQLException: The number of parameter values set or registered does not match the number of parameters.
SystemErr R callHistorySP() org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; 错误的 SQL 语法 [{调用 DB2ADMIN/QUOTEACCESSORIALS(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}]; 嵌套异常为 java.sql.SQLException: 设置或注册的参数值数量与参数数量不匹配。
Here's the object (at least the relevant parts).
这是对象（至少是相关部分）。
private class RateQuoteHistoryStoredProc extends StoredProcedure {
private final String SQL = getStoredProcName();
private final String QUOTE_NUM = "rateQuoteNumber",
ACCESSORIAL = "accessorial",
ACCESSORIAL_AMT = "accessorialAmt",
FAKRATEHISTORY = "fakRateHistory",
HANDLING_UNITS = "numHandlingUnits",
PIECES = "numPieces",
CUBIC_FEET = "cubicFeet",
BUNKER_FUEL_SURCHARGE = "puertoRicoBunkerFuel",
CANADIAN_BORDER_FEE = "canadaCrossBorderFee" ,
TX_ARBITRARY_CHARGE = "texasArbitraryChg" ,
WASHINGTON_DC_CHARGE = "washingtonDCArbitraryChg",
NYC_CHARGE = "nycArbitraryChg" ,
NORTHERN_VIRGINIA_CHARGE = "northVirginiaArbitraryChg" ,
FLORIDA_ARBITRARY_CHARGE = "flKeysArbitraryChg" ,
FERRY_CHARGE ="ferryCharge";
private Map outMap = new HashMap();
public RateQuoteHistoryStoredProc(DataSource ds){
try {
setDataSource(ds);
setSql(SQL);
declareParameter(new SqlParameter(QUOTE_NUM, Types.DECIMAL));
for (int i = 0; i < getMAX_ACCESSORIALS(); i++) {
declareParameter(new SqlOutParameter(ACCESSORIAL + i, Types.CHAR));
}
for (int i = 0; i < getMAX_ACCESSORIALS(); i++) {
declareParameter(new SqlOutParameter(ACCESSORIAL_AMT + i, Types.DECIMAL));
}
declareParameter(new SqlOutParameter(FAKRATEHISTORY, Types.CHAR));
declareParameter(new SqlOutParameter(HANDLING_UNITS, Types.DECIMAL));
declareParameter(new SqlOutParameter(PIECES, Types.DECIMAL));
declareParameter(new SqlOutParameter(CUBIC_FEET, Types.DECIMAL));
declareParameter(new SqlOutParameter(BUNKER_FUEL_SURCHARGE, Types.DECIMAL));
declareParameter(new SqlOutParameter(CANADIAN_BORDER_FEE, Types.DECIMAL));
declareParameter(new SqlOutParameter(TX_ARBITRARY_CHARGE, Types.DECIMAL));
declareParameter(new SqlOutParameter(WASHINGTON_DC_CHARGE, Types.DECIMAL));
declareParameter(new SqlOutParameter(NYC_CHARGE, Types.DECIMAL));
declareParameter(new SqlOutParameter(NORTHERN_VIRGINIA_CHARGE, Types.DECIMAL));
declareParameter(new SqlOutParameter(FLORIDA_ARBITRARY_CHARGE, Types.DECIMAL));
declareParameter(new SqlOutParameter(FERRY_CHARGE, Types.DECIMAL));
compile();
}
catch (Exception e) {
log.error("RateQuoteStoredProc()",e);
}
}
public void callHistorySP(LTLCustomRatesBean bean) throws Exception {
HashMap inMap = new HashMap();
inMap.put(QUOTE_NUM, bean.getRateQuoteNumber());
for(int i = 0; i < getMAX_ACCESSORIALS(); i++){
inMap.put(ACCESSORIAL+i, " ");
}
for(int i = 0; i < getMAX_ACCESSORIALS(); i++){
inMap.put(ACCESSORIAL_AMT+i, "0");
}
try
{
outMap = super.execute(inMap);
}
catch (NullPointerException npe){
if(bean.getCustomer() == null){
log.error("callHistorySP() Customer is null");
}else{
log.error("callHistorySP()",npe);
}
}
catch (Exception e) {
log.error("callSP()",e);
}
if (isDebugMode()) {
log.debug("callSP(): outMap: "+outMap);
}
saveResults(outMap, bean);
}
}
Here's the Stored Procedure that it is calling.
这是它正在调用的存储过程。
CREATE PROCEDURE DB2ADMIN.QUOTEACCESSORIALS (
IN QUOTENUM DECIMAL(7, 0) ,
INOUT ACCCD1 CHAR(6) ,
INOUT ACCCD2 CHAR(6) ,
INOUT ACCCD3 CHAR(6) ,
INOUT ACCCD4 CHAR(6) ,
INOUT ACCCD5 CHAR(6) ,
INOUT ACCCD6 CHAR(6) ,
INOUT ACCCD7 CHAR(6) ,
INOUT ACCCD8 CHAR(6) ,
INOUT ACCCD9 CHAR(6) ,
INOUT ACCCD10 CHAR(6) ,
INOUT ACCCD11 CHAR(6) ,
INOUT ACCCD12 CHAR(6) ,
INOUT ACCCD13 CHAR(6) ,
INOUT ACCCD14 CHAR(6) ,
INOUT ACCCD15 CHAR(6) ,
INOUT ACCCD16 CHAR(6) ,
INOUT ACCCD17 CHAR(6) ,
INOUT ACCCD18 CHAR(6) ,
INOUT ACCCD19 CHAR(6) ,
INOUT ACCCD20 CHAR(6) ,
INOUT ACCAM1 DECIMAL(9, 2) ,
INOUT ACCAM2 DECIMAL(9, 2) ,
INOUT ACCAM3 DECIMAL(9, 2) ,
INOUT ACCAM4 DECIMAL(9, 2) ,
INOUT ACCAM5 DECIMAL(9, 2) ,
INOUT ACCAM6 DECIMAL(9, 2) ,
INOUT ACCAM7 DECIMAL(9, 2) ,
INOUT ACCAM8 DECIMAL(9, 2) ,
INOUT ACCAM9 DECIMAL(9, 2) ,
INOUT ACCAM10 DECIMAL(9, 2) ,
INOUT ACCAM11 DECIMAL(9, 2) ,
INOUT ACCAM12 DECIMAL(9, 2) ,
INOUT ACCAM13 DECIMAL(9, 2) ,
INOUT ACCAM14 DECIMAL(9, 2) ,
INOUT ACCAM15 DECIMAL(9, 2) ,
INOUT ACCAM16 DECIMAL(9, 2) ,
INOUT ACCAM17 DECIMAL(9, 2) ,
INOUT ACCAM18 DECIMAL(9, 2) ,
INOUT ACCAM19 DECIMAL(9, 2) ,
INOUT ACCAM20 DECIMAL(9, 2) ,
OUT FAKRATEHISTORY CHAR(20) ,
OUT HANDLING_UNIT DECIMAL(5, 0) ,
OUT PIECES DECIMAL(5, 0) ,
OUT CUBIC_FEET DECIMAL(5, 0) ,
OUT BUNKER_FUEL_SURCHARGE DECIMAL(9, 2) ,
OUT CANADIAN_BORDER_FEE DECIMAL(9, 2) ,
OUT TX_ARBITRARY_CHARGE DECIMAL(9, 2) ,
OUT WASHINGTON_DC_CHARGE DECIMAL(9, 2) ,
OUT NYC_CHARGE DECIMAL(9, 2) ,
OUT NORTHERN_VIRGINIA_CHARGE DECIMAL(9, 2) ,
OUT FLORIDA_ARBITRARY_CHARGE DECIMAL(9, 2) ,
OUT FERRY_CHARGE DECIMAL(9, 2) )
LANGUAGE RPG
SPECIFIC DB2ADMIN.QUOTEACCESSORIALS
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
EXTERNAL NAME 'LTL400AVX2/WW7R010'
PARAMETER STYLE GENERAL ;
You'll note that the SP has 53 parameters. It has 1 input parameter, 40 in/out parameters, and 12 output parameters. The call that I'm making seems to have the correct number of parameters and I can't figure out what is causing the error. Any help would be greatly appreciated. I'm sure that the problem is probably something simple but I just don't know Spring well enough to see what is wrong with this code. I haven't been able to Google an answer up like normal either. Thanks!
您会注意到 SP 有 53 个参数。它有1个输入参数、40个输入/输出参数和12个输出参数。我正在进行的调用似乎具有正确数量的参数，但我无法弄清楚是什么导致了错误。任何帮助将不胜感激。我确信问题可能很简单，但我只是不太了解 Spring，无法查看这段代码有什么问题。我也无法像往常一样用谷歌搜索答案。谢谢！
Andrew
安德鲁
采纳答案by Luke Woodward
You are declaring your 40 INOUT parameters using SqlOutParameter. Does it help if you declare them using SqlInOutParameterinstead?
您正在使用SqlOutParameter声明您的 40 个 INOUT 参数。如果您使用SqlInOutParameter来声明它们是否有帮助？
