java.sql.SQLException: 参数号 X 不是 OUT 参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3017934/
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
java.sql.SQLException: Parameter number X is not an OUT parameter
提问by Frederik
I'm struggling with getting the result OUT variable from a MySQL stored procedure. I get the following error:
我正在努力从 MySQL 存储过程中获取结果 OUT 变量。我收到以下错误：
java.sql.SQLException: Parameter number 3 is not an OUT parameter
The stored procedure looks like this:
存储过程如下所示：
CREATE DEFINER=`cv_admin`@`localhost` PROCEDURE `CheckGameEligibility`(
IN gID INT(10),
IN uID INT(10),
OUT result TINYINT(1)
)
BEGIN
# Do lots of stuff, then eventually:
SET result = 1;
END
My java function takes an array of strings* and creates the CallableStatement object dynamically:
我的 java 函数接受一个字符串数组*并动态创建 CallableStatement 对象：
public static int callAndReturnResult( String sql , String[] values )
{
int out = 0 ;
try
{
// construct the SQL. Creates: CheckGameEligibility(?, ?, ?)
sql += "(" ;
for( int i = 0 ; i < values.length ; i++ )
{
sql += "?, " ;
}
sql += "?)" ;
System.out.println( "callAndReturnResult("+sql+"): constructed SQL: " + sql );
// Then the statement
CallableStatement cstmt = DB.prepareCall( sql );
for( int i = 0 ; i < values.length ; i++ )
{
System.out.println( " " + (i+1) + ": " + values[ i ] ) ;
cstmt.setString(i+1, values[ i ] );
}
System.out.println( " " + (values.length+1) + ": ? (OUT)" ) ;
cstmt.registerOutParameter( values.length + 1 , Types.TINYINT );
cstmt.execute();
out = cstmt.getInt( values.length );
cstmt.close();
}
catch( Exception e )
{
System.out.println( "*** db trouble: callAndReturnResult(" + sql + " failed: " + e );
e.printStackTrace() ;
}
return out ;
}
*) I suppose I should be using an int array instead of a string array, but it doesn't seem to be what the error message was about.
*) 我想我应该使用 int 数组而不是字符串数组，但这似乎不是错误消息的内容。
Anyway, here's the output it generates:
无论如何，这是它生成的输出：
callAndReturnResult(CheckGameEligibility(?, ?, ?)): constructed SQL: CheckGameEligibility(?, ?, ?)
1: 57
2: 29
3: ? (OUT)
*** db trouble: callAndReturnResult(CheckGameEligibility(?, ?, ?) failed: java.sql.SQLException: Parameter number 3 is not an OUT parameter
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929)
at com.mysql.jdbc.CallableStatement.checkIsOutputParam(CallableStatement.java:692)
at com.mysql.jdbc.CallableStatement.registerOutParameter(CallableStatement.java:1847)
at org.apache.commons.dbcp.DelegatingCallableStatement.registerOutParameter(DelegatingCallabl>eStatement.java:92)
at Tools.callAndReturnResult(Tools.java:156)
Any ideas what might be the problem? :)
任何想法可能是什么问题？:)
Thanks in advance!
提前致谢！
采纳答案by jalogar
My code is slightly different
我的代码略有不同
CallableStatement statement = connection.prepareCall("{? = call nextOperatorTransactionId()}");
statement.registerOutParameter(1, Types.BIGINT);
statement.execute();
id.set(statement.getLong(1));
Notice the brackets and in my case the ? at the begining of the sentence. Without using brackets I got the same exception than you
注意括号，在我的情况下是 ? 在句子的开头。不使用括号，我得到了和你一样的例外
回答by drvisor
Try call CheckGameEligibility(?, ?, ?)instead of CheckGameEligibility(?, ?, ?). It helped me in the same circumstance.
尝试call CheckGameEligibility(?, ?, ?)代替CheckGameEligibility(?, ?, ?). 它在同样的情况下帮助了我。
回答by Sahil Garg
My sql gives this unrelated exception when the procedure name you specified is different than actual procedure present in db.
当您指定的过程名称与 db 中存在的实际过程不同时，我的 sql 会给出此无关的异常。
回答by Nikita Rybak
Here's tutorial on how to call stored procedures from jdbc:
http://java.sun.com/docs/books/tutorial/jdbc/basics/sql.html
The sql format is different from yours.
下面是关于如何从 jdbc 调用存储过程的教程：
http://java.sun.com/docs/books/tutorial/jdbc/basics/sql.html
sql 格式与您的不同。
回答by Powerlord
I'm not sure why Java or the MySQL drivers for Java don't like this syntax, but have you considered creating the procedure as a FUNCTIONthat RETURNS TINYINTinstead?
我不知道为什么Java或Java的MySQL驱动不喜欢这种语法，但你有没有考虑创建过程作为FUNCTION那个RETURNS TINYINT呢？
回答by Anthony
I just got this exception as well, and I found out it was simply a dumb little syntax error I made when constructing my CALL string. Double-check that yours is the correct syntax.
我也刚收到这个异常，我发现这只是我在构造 CALL 字符串时犯的一个愚蠢的小语法错误。仔细检查您的语法是否正确。
回答by Patricio
Same problem for me but I fixed checking the syntax as Anthony suggested, what I discover was that I was calling the stored procedure using the camel case convention ex: addPaiment and in the database it was stored as ADDPAIMENT so I believe that mysql was not finding the stored procedure.
对我来说同样的问题，但我按照 Anthony 的建议修复了检查语法，我发现我正在使用骆驼案例约定调用存储过程，例如：addPaiment 并且在数据库中它被存储为 ADDPAIMENT 所以我相信 mysql 没有找到存储过程。
