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

Solution 1

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?

Solution 2

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

Solution 3

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

Comments

[Hrabosch]

There is already EDIT in question about that he have a same error with setString(1, x)

[Hemant Metalia]

what is the value of x ?

[Hrabosch]

It is not my question ;)

[Platus]

So you're saying there is no solution?

[Frank Pavageau]

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.