Re: SQL question with where in clause

Maybe this will work for you. Here is some code I had come across
somewhere and has worked well for me.

<code>
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class PreparedStatementFactory {

public PreparedStatementFactory() {
}

public static void buildStatement(PreparedStatement stmnt, Object
values[])
throws IllegalArgumentException,
IllegalStateException {
if (values == null || stmnt == null) {
System.out
.println("The build statement
function was passed a null value for the values array or the actual
statement. This is bad.");
throw new IllegalArgumentException(
"Cannot pass a null value array
into buildStatement().");
}
try {
Object value = null;
for (int i = 0; i < values.length; i++) {
value = values[i];
if (value == null)
break;
stmnt.setObject(i + 1, value);
}

} catch (SQLException e) {
String message = "The prepared statement could not
be formed with the given values";
System.out.println(message);
throw new IllegalStateException(message);
}
}
}
</code>

Then you would execute like so...

String sql = 'SELECT * FROM mytable WHERE line in (?';
for (int i = 0; i < values.length; i++) {
sql = sql.trim() + ',?';
}
sql = sql.trim() + ')';
ps = connection.prepareStatement(sql);
PreparedStatementFactory.buildStatement(ps, params);


You should be able to pass in any number of parms based on values passed
in.

Hope this helps. I have used the PreparedStatementFactory class before,
but the other code has not been tested. There is probably a more elegant
way to handle the build of the sql statement, but I'm more of an RPG
programmer than a Java programmer, but I'm learning.

James R. Perkins




Ashish Kulkarni <kulkarni_ash1312@xxxxxxxxx>
Sent by: java400-l-bounces@xxxxxxxxxxxx
13.09.2007 12:32
Please respond to
Java Programming on and around the iSeries / AS400
<java400-l@xxxxxxxxxxxx>


To
Java Programming on and around the iSeries / AS400
<java400-l@xxxxxxxxxxxx>
cc

Subject
Re: SQL question with where in clause







Hi
No the problem i have is, i dont know how many ? to be
added to my SQL statement
select * from mytable where line in (?,?,?)
will work if i know i will have only 3 values to
replace, but the number of lines selected are going to
come from the screen, which can be from 1 t more then
100, so i wanted to make this portion dynamic

Ashish
--- Michael_Schutte@xxxxxxxxxxxx wrote:

I went back at old code... In places where didn't
want to use the place
marker, I used...

cstmt.setNull( ... );

However, I wasn't doing this with a select statement
it was with a
procedure call that populated databases. But this
may be a place to start
looking at.


Michael Schutte
Admin Professional
Bob Evans Farms, Inc.
"The Secret's the Sauce! Enjoy our new Bob-B-Q
Pulled Pork Knife & Fork
Sandwich!"


java400-l-bounces@xxxxxxxxxxxx wrote on 09/13/2007
02:48:26 PM:

Without seeing your code... I believe you are

wanting to do this. Are
you

doing this?

value1 = 'ABC';
value2 = 'XYZ';
value3 = '';
value4 = '';
value5 = '';
value6 = '';
value7 = '';

sqlStatement = "{SELECT * FROM mytable WHERE line

in (?,?,?,?,?,?,?)});

cstmt = conn.prepareCall(sqlStatement);
cstmt.setString(1, value1);
cstmt.setString(2, value2);
cstmt.setString(3, value3);
cstmt.setString(4, value4);
cstmt.setString(5, value5);
cstmt.setString(6, value6);
cstmt.setString(7, value7);

I cannot test this right now, but this is how I

understand to use the "?"

place holders.



Michael Schutte
Admin Professional
Bob Evans Farms, Inc.
"The Secret's the Sauce! Enjoy our new Bob-B-Q

Pulled Pork Knife & Fork

Sandwich!"


java400-l-bounces@xxxxxxxxxxxx wrote on 09/13/2007

02:35:18 PM:

Hi
I have a sql statment like

select * from mytable where line in ('ABC',

'XYZ')

where this SQL statement is going to extact all

rows

where line is ABC and XYZ,

but now the problem is i do not know if there

are

going to be only one line or 2 or 10, so i

thought i

would define the sql as

select * from mytable where line in ?

and then i created a string like ('ABC', 'XYZ')

and

passed it as input parameter, but it does now

work,

Any ideas for a work around this solution


Ashish

A$HI$H