Java PreparedStatement: com.microsoft.sqlserver.jdbc.SQLServerException, index out of range

Question

I'm trying to execute a SQL query using a Java PreparedStatement in Java 7 using the code below:

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.

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.

EDIT: I get a similar error when using setString(1, x) => index 1 is out of range

You're trying to set the value of the second parameter but you only have one parameter that needs to be set. Also, what's the purpose of `st`? You don't appear to do anything with it. You also don't need to 'prettify' the SQL inside a prepared statement - make it easy to read from the point of view of your code instead of littering the query string with superfluous tab and newline characters. — JonK, Jul 29 '16 at 09:28
`index 2 is out of range` explains it. You should use `1` instead. — Abubakkar, Jul 29 '16 at 09:28
I actually tried `1` first and I get the same erroer `index 1 out of range` — Platus, Jul 29 '16 at 09:31
With that update, it's quite probably down to the fact that the `?` is inside a quoted string, so the API doesn't see it as a placeholder. — JonK, Jul 29 '16 at 09:33
There it is because you are selecting `*` .... Do you need all? — Hrabosch, Jul 29 '16 at 09:35

Frank Pavageau · Answer 1 · 2016-08-09T21:34:05.613

4

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):

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)

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?

edited Aug 09 '16 at 21:34

answered Jul 29 '16 at 11:02

Frank Pavageau

11,477
1
43
53

So you're saying there is no solution? – Platus Aug 02 '16 at 13:28
If you provide the query as a string, it has to be complete: you have to concatenate the value, which means managing the quotes of the string parameter, etc. Tricky depending on where the string comes from. Also see [this SO question](https://stackoverflow.com/q/3378496/1350869). – Frank Pavageau Aug 09 '16 at 21:39

score 1 · Answer 2 · answered Jul 29 '16 at 09:29

1

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.

you should use

 functionalCRsStatement.setString(1, x);

answered Jul 29 '16 at 09:29

gybandi

1,880
1
11
14

score 1 · Answer 3 · answered Jul 29 '16 at 09:30

1

You have only one bind variable placeholder (?) in your query - so you should bind it with an index of 1, not 2:

functionalCRsStatement.setString(1, x); // Was 2 in the OP

answered Jul 29 '16 at 09:30

Mureinik

297,002
52
306
350

Hemant Metalia · Answer 4 · 2016-07-29T10:25:40.820

0

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.

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

index 2 is out of range.

Try the same with index 1. as you have only 1 parameter to be set.

e.g. functionalCRsStatement.setString(1, x);

remember the value x will be stored to the ? at 1st index in the prepared statement.

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.

edited Jul 29 '16 at 10:25

answered Jul 29 '16 at 09:45

Hemant Metalia

29,730
18
72
91

1

There is already EDIT in question about that he have a same error with `setString(1, x)` – Hrabosch Jul 29 '16 at 10:19
what is the value of x ? – Hemant Metalia Jul 29 '16 at 10:20
It is not my question ;) – Hrabosch Jul 29 '16 at 10:22

score 0 · Answer 5 · answered Jul 29 '16 at 10:24

The prepared statement is not recognizing any param, for is this query contains 0 params because of mal-written string; try this :

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 = ?");

Java PreparedStatement: com.microsoft.sqlserver.jdbc.SQLServerException, index out of range

5 Answers5

Linked