Re: JDBC - Setting Values on PreparedStatement

    Hi James,

    While there often are additional reasons to use prepared statements, the
primary idea is that a search plan can be created beforehand and reused in
later queries.  The benefit comes from not having to recalc the plan each
time, which is the case with standard Statements.  While the comparison
*values* of variables can vary using replacement markers, it's pretty much
an implicit requirement that the variables themselves don't change.  If they
do, clearly another plan has to be devised, which pretty much defeats the
advantage.

    If you ignore possible other considerations, then perforrmance-wise a
standard Statement will beat out a PreparedStatement in a one-time use.
That's becasue a PreparedStatement requires a separate trip to the database
so the database engine can get the information and prepare the plan.

    There's no substantive difference in Sarah's solution and building the
appropriate statement with replacement markers ( question marks - ? ) as you
need it.  Whether it makes sense performance-wise to do either one depends
on whether the statement will be executed multiple times.

    A relatively new impact is that some drivers/databases are capable of
caching prepared statements, so in a multi-user environment, like servlets,
you may still get a performance gain overall even though an individual
response/thread uses it only once.  HTH,


                                                         Joe Sam

Joe Sam Shirah -        http://www.conceptgo.com
conceptGO       -        Consulting/Development/Outsourcing
Java Filter Forum:       http://www.ibm.com/developerworks/java/
Just the JDBC FAQs: http://www.jguru.com/faq/JDBC
Going International?    http://www.jguru.com/faq/I18N
Que Java400?            http://www.jguru.com/faq/Java400


----- Original Message ----- 
From: "James O'Sullivan" <josulli4@xxxxxxx>
To: "Java Programming on and around the iSeries / AS400"
<java400-l@xxxxxxxxxxxx>
Sent: Thursday, January 26, 2006 10:57 AM
Subject: Re: JDBC - Setting Values on PreparedStatement


>
> Thanks Sarah,
>       I was hopeful that it could be done using the ?, but good to have
> this option.
>
> 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.
>
>
>
>
>              Sarah Poger
>              Gladstone
>              <listmember@glads                                          To
>              tone.info>                Java Programming on and around the
>              Sent by:                  iSeries / AS400
>              java400-l-bounces         <java400-l@xxxxxxxxxxxx>
>              @midrange.com                                              cc
>
>                                                                    Subject
>              26/01/2006 15:12          Re: JDBC - Setting Values on
>                                        PreparedStatement
>
>              Please respond to
>              Java Programming
>              on and around the
>               iSeries / AS400
>              <java400-l@midran
>                   ge.com>
>
>
> I had to do something similar where I didn't know in advance how many
> variables would be in the IN clause.
>
> How I solved my issue was to not use ? in my prepared statement.
>
> For example:
>
> String langs = "'" + lang1 + "' , "  + "'" + lang2 + "'";
> PreparedStatement pstmt = con.prepareStatement("SELECT * FROM ERORPF WHERE
> ERORLANG IN (" + langs + ")"  );
>
> HTH,
> Sarah
>
>
> On 1/26/06, James O'Sullivan <josulli4@xxxxxxx> wrote:
> >
> >
> >
> >
> > Hi All,
> >      I have had a tour around the Net and can't seem to find an
> > answer/example of this.
> >
> > 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?
> >
> >
> > regards
> >
> >
> > James O'Sullivan