Java PreparedStatement：com.microsoft.sqlserver.jdbc.SQLServerException，索引超出范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38655215/
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 PreparedStatement: com.microsoft.sqlserver.jdbc.SQLServerException, index out of range
提问by Platus
I'm trying to execute a SQLquery using a Java PreparedStatementin Java 7 using the code below:
我正在尝试使用以下代码在 Java 7 中使用SQLJava执行查询PreparedStatement：
PreparedStatement functionalCRsStatement = con.prepareStatement(
"select * from openquery(SERVER,\n" +
"\t'Select X , Y, Z, A from D r\n" +
"\tINNER JOIN E c\n" +
"\tON r.RNID = c.RNID\n" +
"\twhere c.Y = ?')\n");
functionalCRsStatement.setString(2, x);
I get the following error message: com.microsoft.sqlserver.jdbc.SQLServerException: The index 2 is out of range.
我收到以下错误消息： com.microsoft.sqlserver.jdbc.SQLServerException: The index 2 is out of range.
PS: I'm sure of the correctness of the SQL query because I successfully tested it without a PreparedStatement, I just replaced the real name of the columns in the query by fake ones (X, Y, Z) to hide potentially confidential information.
PS：我确定 SQL 查询的正确性，因为我在没有PreparedStatement.
EDIT: I get a similar error when using setString(1, x)=> index 1 is out of range
编辑：使用setString(1, x)=>时出现类似错误index 1 is out of range
回答by Frank Pavageau
As @JonK commented, you have apostrophes in your query, which means your parameter is actually inside a string where the SQL engine won't bind a value (whether you use 1 or 2 as the index):
正如@JonK 评论的那样，您的查询中有撇号，这意味着您的参数实际上位于 SQL 引擎不会绑定值的字符串中（无论您使用 1 还是 2 作为索引）：
PreparedStatement functionalCRsStatement = con.prepareStatement(
"select * from openquery(APRPRD,\n" +
"\t'Select X , Y, Z, A from D r\n" +
"\tINNER JOIN E c\n" +
"\tON r.RNID = c.RNID\n" +
"\twhere c.Y = ?')\n");
contains this query (with SQL syntax highlighting, which shows the whole string)
包含此查询（带有 SQL 语法突出显示，显示整个字符串）
select * from openquery(APRPRD,
'Select X , Y, Z, A from D r
INNER JOIN E c
ON r.RNID = c.RNID
where c.Y = ?')
A SQL engine never inspects the inside of a string. How would you insert a string containing a question mark otherwise?
SQL 引擎从不检查字符串的内部。否则如何插入包含问号的字符串？
回答by gybandi
It seems you only have one ?in your statement, so you can't make a reference to the second index (2) in the functionalCRsStatement.setString(2, x);, because as it says, it's out of range.
?您的语句中似乎只有一个，因此您无法引用 中的第二个索引 (2) functionalCRsStatement.setString(2, x);，因为正如它所说，它超出了范围。
you should use
你应该使用
functionalCRsStatement.setString(1, x);
回答by Mureinik
You have only one bind variable placeholder (?) in your query - so you should bind it with an index of 1, not 2:
您的查询中只有一个绑定变量占位符 ( ?) - 因此您应该将其与索引绑定1，而不是2：
functionalCRsStatement.setString(1, x); // Was 2 in the OP
回答by Hemant Metalia
You have only one parameter to set in your prepared statement. the set method to set parameter in prepared statement checks index of the ?in the prepared statement and sets the value to prepared statement accordingly.
您只需在准备好的语句中设置一个参数。在准备好的语句中设置参数的 set 方法检查准备好的语句中的索引?并相应地设置值到准备好的语句。
So in your case there is only 1 ?so in an array of values to be passed for prepared statement is 1. and you are trying to pass the value at the index 2 hence it says The
因此，在您的情况下，只有 1，?因此在要为准备好的语句传递的值数组中为 1。并且您试图在索引 2 处传递值，因此它说
index 2 is out of range.
索引 2 超出范围。
Try the same with index 1. as you have only 1 parameter to be set.
对索引 1 尝试相同的方法，因为您只有 1 个参数需要设置。
e.g. functionalCRsStatement.setString(1, x);
例如功能性CRsStatement.setString(1, x);
remember the value x will be stored to the ?at 1st index in the prepared statement.
请记住，值 x 将存储?在准备好的语句中的第 1 个索引中。
EDIT: Also remember the type to the value to be passed. if you are setting value of X as int you need to call setInt(1,x). in this case it will not able to find first index os String and throw an error of index out of range.
编辑：还要记住要传递的值的类型。如果您将 X 的值设置为 int，则需要调用 setInt(1,x)。在这种情况下，它将无法找到第一个索引 os String 并抛出索引超出范围的错误。
回答by Elgayed
The prepared statement is not recognizing any param, for is this query contains 0 params because of mal-written string; try this :
准备好的语句无法识别任何参数，因为该查询是否由于写入错误的字符串而包含 0 个参数；试试这个 ：
PreparedStatement functionalCRsStatement = con.prepareStatement(
"select * from openquery(APRPRD," +
"'Select X , Y, Z, A from D r" +
"INNER JOIN E c" +
"ON r.RNID = c.RNID ')" +
"where c.Y = ?");
