using prepare statements

[Peter Warde]

I have a prepared statement that has a sql query such as

SELECT *
FROM table1
WHERE name IN( ? )

and I have an array String [] names.

How do I pass the array names as a parameter to the prepared statement?

[Sathya Narayanan Natarajan]

Hi

From the String[] Array, Construct a Comma-Separated String Of Names in the following format ,

'Name1','Name2','Name3'

The following code will help you get a Comma-Separated-String of Names

String param = stringArray[0];
for (int i = 1; i<stringArray.length; i++) {
param += stringArray[i];
}


Then, just set the param in the statement as follows,

preparedStatement.setString(1,param);
Then run the query.

Hope this helps you,
Thanks.
Sathya

[James Carman]

Actually, I don't think you can do that, set the list of strings for an "in" condition. You might have to build your SQL by hand then. Just make sure you escape any single quote characters.

[Ilja Preuss]

Moving to JDBC...

[Lu Battist]

This will work better, but if you expect nulls in the stringArray
you may need to test for that and use the preparedStatement.setNull(...) method.

StringBuffer sbSql = new StringBuffer("SELECT * FROM table1 WHERE name IN (");
for (int i = 0; i<stringArray.length; i++) {
   if (i==stringArray.length) {
     sbSql.append("?");
   } else {
     sbSql.append("?, ");
   }
   preparedStatement.setString(i+1,stringArray[i]);
}
sbSql.append(")";

[ March 16, 2005: Message edited by: Lu Battist ]