JDBC and SQLServer2000 - parsing a comma-separated string to search on int
I am using sqlserver2000 and JDBC on Windows XP platform.
From a webpage i get a user input string in the form "'1', '2', '3'". This string may contain only one value for ex. " '1' ".
Database column is of type int.
I build the query string based on user input and then use prepareStatement call to store this in a PreparedStatement object.
I built the where clause:
and convert( char(50), table1.column1 ) in ( ? )
Then I use setString method of PreparedStatement object to set the parameter value as user input string.
This query does not return any rows when run thru JDBC.
If I run the same query in MS SQLAnalyzer, I get back proper results.
Earlier I was not using convert function on the database column and would get JDBC sql exception saying something like trying to convert nvarchar to int.
Finally, I extracted integer values from the user-input string, built the query where clause and did not use setString method of PreparedStatement object. Now i get expected results back.
Is this the only way to do this kind of work?
# 1
> and convert( char(50), table1.column1 ) in ( ? )
> Then I use setString method of PreparedStatement
> object to set the parameter value as user input
> string.
Which of course won't work because all that does is insert a single literal where as the SQL that you want needs more than one.
>
> Finally, I extracted integer values from the
> user-input string, built the query where clause and
> did not use setString method of PreparedStatement
> object. Now i get expected results back.
>
> Is this the only way to do this kind of work?
There are any number of ways.
You can just create the sql by appending the string rather than using setString()
You could parse to determine the number of values, then create a statement with that many parameters and use setInt() on each.
You pass the entire thing to a stored proc and let it process it which would parse it.
You could create a fixed length statement with X parameters (?) where that is greater than the number that could occur. Then fill it it with what you have and fill in the extras with the first value in your list.
You might note that if you list gets large (which is not just a count but actually the physical length as text) that you could run into database/driver limits on the statement. Fixing that requires other solutions.
