Re: JDBC - Setting Values on PreparedStatement

Hi James,
 
<snip>
Have a simple SQL statement
SELECT * FROM ERORPF WHERE ERORLANG IN ('E', 'N', 'D')
 
The contents of the IN predicate can vary from 1 to 10 items, hence my
question.
 
I set this in a PreparedStatement
PreparedStatement pstmt = con.prepareStatement("SELECT * FROM ERORPF WHERE
ERORLANG IN (?)");
But when it came to replacing the ? with the required values, I hit my
problem. I tried (perhaps optimistically) just to see if it would accept
parameters in the following format, but no joy.
 
String langs = "'" + lang1 + "' , " + "'" + lang2 + "'"; setString(1,
langs);
 
How can you pass a variable amount of values to the IN predicate?
 
</snip>
 
If you know you have a maximum of 10 parameter markers then I can't see any
reason why you can't do the following:
 
PreparedStatement pstmt = con.prepareStatement("SELECT * FROM ERORPF WHERE
ERORLANG IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
 
You populate the parameter markers with the values you have - once you run
out you fill the rest with the last value you have.
 
So, with values 'E', 'N', 'D' you would have:
 
PreparedStatement pstmt = con.prepareStatement("SELECT * FROM ERORPF WHERE
ERORLANG IN ('E', 'N', 'D', 'D', 'D', 'D', 'D', 'D', 'D', 'D')");
 
OK, you have some redundancy with the 'D' parms - but the RDBMS should
handle that and factor it out - there shouldn't be any constraint on
uniqueness of parameter marker values should there?
 
Try it - see if it works.
 
Cheers
 
Larry Ducie