Re: JDBC - Setting Values on PreparedStatement

Thanks Larry,
      I had actually done that to get past the problem, but wondered if
there was a performance issue and was curious if there was a more effective
solution.
Good to know there are other people out there on the same wave-length.

Thanks to everyone who responded.

regards


James O'Sullivan
Senior Technical Consultant
email: josulli4@xxxxxxx
Office: +44 1252 536681
Fax: +44 1252 534022
www: www.csc.com
Based at: Tower 2, Floor 2, Royal Pavilion, Wellesley Road, Aldershot,
Hampshire, GU11 1PZ

CSC Corporation Limited: No. 1812179.
All registered office addresses: Royal Pavilion, Wellesley Road, Aldershot,
Hampshire, GU11 1PZ. Registered in England.


--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

This is a PRIVATE message. If you are not the intended recipient, please
delete without copying and kindly advise us by e-mail of the mistake in
delivery. NOTE: Regardless of content, this e-mail shall not operate to
bind CSC to any order or other contract unless pursuant to explicit written
agreement or government initiative expressly permitting the use of e-mail
for such purpose.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------




                                                                           
             "Larry Ducie"                                                 
             <Larry_Ducie@hotm                                             
             ail.com>                                                   To 
             Sent by:                  <java400-l@xxxxxxxxxxxx>            
             java400-l-bounces                                          cc 
             @midrange.com                                                 
                                                                   Subject 
                                       Re: JDBC - Setting Values on        
             26/01/2006 21:36          PreparedStatement                   
                                                                           
                                                                           
             Please respond to                                             
             Java Programming                                              
             on and around the                                             
              iSeries / AS400                                              
             <java400-l@midran                                             
                  ge.com>                                                  
                                                                           
                                                                           




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

--
This is the Java Programming on and around the iSeries / AS400 (JAVA400-L)
mailing list
To post a message email: JAVA400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/java400-l
or email: JAVA400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/java400-l.