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
– JonK, Commented Jul 29, 2016 at 9:28
index 2 is out of range explains it. You should use 1 instead. — Abubakkar
– Abubakkar, Commented Jul 29, 2016 at 9:28
I actually tried 1 first and I get the same erroer index 1 out of range — Platus
– Platus, Commented Jul 29, 2016 at 9: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
– JonK, Commented Jul 29, 2016 at 9:33

Frank Pavageau · Accepted Answer · 2016-08-09 21:34:05Z

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 9, 2016 at 21:34

answered Jul 29, 2016 at 11:02

Frank Pavageau

11.7k1 gold badge45 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Platus Over a year ago

So you're saying there is no solution?

Frank Pavageau Over a year ago

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.

gybandi · Accepted Answer · 2016-07-29 09:29:49Z

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, 2016 at 9:29

gybandi

1,8981 gold badge12 silver badges14 bronze badges

Comments

Mureinik · Accepted Answer · 2016-07-29 09:30:48Z

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, 2016 at 9:30

Mureinik

316k54 gold badges402 silver badges406 bronze badges

Comments

Elgayed · Accepted Answer · 2016-07-29 10:24:18Z

0

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

answered Jul 29, 2016 at 10:24

Elgayed

1,22911 silver badges18 bronze badges

Comments

Hemant Metalia · Accepted Answer · 2016-07-29 10:25:40Z

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, 2016 at 10:25

answered Jul 29, 2016 at 9:45

Hemant Metalia

30.8k18 gold badges77 silver badges93 bronze badges

3 Comments

Hrabosch Over a year ago

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

Hemant Metalia Over a year ago

what is the value of x ?

Hrabosch Over a year ago

It is not my question ;)

Collectives™ on Stack Overflow

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

5 Answers 5

2 Comments

Comments

Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related