Re: JDBC question... -- JAVA400-L

Joe Sam,

I completely understand...thanks so much for the explanation...I made the 
chages everyone has suggested and this thing is running beautifully...way 
faster than creating the prepared statements over and over again...

thanks again to all of you...you're helping another RPG programmer 
understand Java...

Shane Cessna  
iSeries Programmer
iSeries WAS Administrator
North American Lighting, Inc.
(618) 662-4483 x2776
shane_cessna@xxxxxxx 



"Joe Sam Shirah" <jshirah@xxxxxxxxxxxxx> 
Sent by: java400-l-bounces@xxxxxxxxxxxx
06/16/2005 02:36 PM
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: JDBC question...







    Hi Shane,

> statements...is this what you're talking about?

    Yes, but first see Franco's response re:

>    queryCKWORKCD.setString(1, "'" + pc[x].trim() + "'");

and change it as suggested.

> Should I declare my ResultSet at the top of my method also?

    I'll leave issues of where to declare variables to others for now.  In
general it doesn't matter much, other than scope and possibly clarity.

    BUT, I think I should clarify for you why it was important to move the

nal820.conn.prepareStatement(...

out of the loop.  It doesn't have to do with variables declared in or out 
of
the loop.  PreparedStatements work differently than regular Statements in
several ways, but the most important is that a PreparedStatement is sent 
to
the database on the conn.prepareStatement() to be precompiled.

    There's a lot of garbage out there about what "pre-compiled" means, 
but
in a production quality database and driver, what happens is that a query
plan or strategy is calculated and saved.  Then, every time the
PreparedStatement is run, the saved strategy is used; the only change is 
any
paramters sent.

    A regular Statement does the same, except the query is sent, the plan 
is
computed, the query executes - *every time*.  If you only run it once, OK
( ignoring the other considerations I mentioned earlier ).

    By putting

nal820.conn.prepareStatement(...

in the loop, you were causing this extra trip ( and calculation ) to the
database on every iteration.  While it's possible the DB might cache the
plan, you shouldn't count on it.  Incidentally, since you were creating a
new PreparedStatement every time, the close() outside the loop only closed
the last one.  gc would eventually handle it, but that's not a good way to
handle things.  Make sense?

    BTW, I think most people here know, but my friends call me


                                                         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: <Shane_Cessna@xxxxxxx>
To: "Java Programming on and around the iSeries / AS400"
<java400-l@xxxxxxxxxxxx>
Sent: Thursday, June 16, 2005 2:42 PM
Subject: Re: JDBC question...


> Joe,
>
> Thanks for the tips...I changed the strings I was using to be like 
String
> workCode, timeString, etc.;
>
> I also changed my Statements to PreparedStatements...
>
> I do have a question about the parameter passing on prepared
> statements...is this what you're talking about?
>
> PreparedStatement queryCKWORKCD;
> ...
> queryCKWORKCD = nal820.conn.prepareStatement("SELECT WCNONW FROM
> CLOCFILE01.CKWORKCD WHERE WCCODE = ?");
> for (int x = 0; x <= 9; x++) {
>    queryCKWORKCD.setString(1, "'" + pc[x].trim() + "'");
>    ResultSet rs2 = queryCKWORKCD.executeQuery();
>    if (rs2.next() == true) {
>       // continue processing...
>    }
> }
>
> Should I declare my ResultSet at the top of my method also?
>
> Shane Cessna
> iSeries Programmer
> iSeries WAS Administrator
> North American Lighting, Inc.
> (618) 662-4483 x2776
> shane_cessna@xxxxxxx
>
>
>     Hi Shane,
>
>     Paul's answer should have resolved your immediate problem.
>
>     Since you were in on a newbie/rookie thread earlier, I have some
> general
> comments on your code:
>
>         Generally, don't use the form
>
>                 String s = new String( "some literal" );
>
>     Literals *are* Strings and you're just creating a new object for
> nothing
> ( that isn't ==, BTW. )  There's another thread on the cost of
> instantiation.  A real concern is what happens while initializing in the
> constructor.  Since String uses a backing character array and does some
> calculations, duplicating strings like this can be costly.
>
>     I think you already know this, see the difference between:
>
>                             String timeQuery = new String("...
>
> and
>
>                             workCodeQuery = "select...
>
>         Another response said "No need to use prepared statements in 
this
> case, all the statements are fixed."  The rationale for 
PreparedStatements
> is normally "Is the statement executed multiple times?"  If so, the 
answer
> is almost always yes, regardless of whether parameters are needed or 
not.
> Sometimes other concerns, such as protecting from SQL injection attacks
> and
> auto-handling escaping suggest using PreparedStatements even when it 
will
> only be executed once.
>
>     BUT, in your code for queryCKWORKCD, you are causing a lot of
> unnecessary work.  In the loop:
>
> >    for (x = 0; x <= 9; x++) {
> >       workCodeQuery =
>
> you create and execute a new PreparedStatement on each iteration, when 
the
> only difference is pc[x].trim().  That should be a parameter, with the
> same PreparedStatement used throughout the loop.  For more info, see "
> JDBC
> Technology Guide: Getting Started" in the JDK documentation for JDBC. 
You
> may also find my JDBC 2.0 Short Course helpful.  It's at:
>
> <
>
http://developer.java.sun.com/developer/onlineTraining/Database/JDBC20Intro/

>
> >
>
> The examples were tested on DB2 Universal, DB2/400 and Cloudscape.
>
>     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: <Shane_Cessna@xxxxxxx>
> To: "Java Programming on and around the iSeries / AS400"
> <java400-l@xxxxxxxxxxxx>
> Sent: Thursday, June 16, 2005 10:41 AM
> Subject: JDBC question...
>
>
> > DB2400 nal820 = new DB2400();
> > String timeQuery = new String("select tiatim, tisec2, tisec3, tipc01,
> > tipc02, tipc03, tipc04, tipc05, tipc06, tipc07, tipc08, tipc09, 
tipc10,
> "
> > +
> >                   "tihr01, tihr02, tihr03, tihr04, tihr05, tihr06,
> tihr07,
> > tihr08, tihr09, tihr10 from timecustom.ktimld4 " +
> >                   "where tiatim between " + Long.toString(indt1) + " 
and
> "
> > + Long.toString(ondt) + " and tisec3 <> 'FH' and tisec3 <> ' ' and
> tisec2
> > not in " +
> >                   "('01300', '04301', '01306', '01310', '01311',
> '01319',
> > '01320', '01326', '01327', '01328', '01332', '01333', " +
> >                   "'01334', '01335', '01336', '01401', '01408', 
'01440',
> > '01456', '01706', '01710', '01712', '02302', '02303', " +
> >                   "'02305', '02307', '02308', '02309', '02313', 
'02314',
> > '02316', '02317', '05325', '02403', '02409', '02441', " +
> >                   "'02457', '02702', '02714', '06350', '06351', 
'06352',
> > '06353', '06354', '06355', '06356', '06357', '06358', " +
> >                   "'06359', '06360', '06416', '06422', '06708', 
'06718')
> > order by tisec3, tisec2");
> > PreparedStatement queryKTIMLD4 =
> nal820.conn.prepareStatement(timeQuery);
> > ResultSet rs = queryKTIMLD4.executeQuery();
> > while (rs.next() == true) {
> >    tiatim = rs.getLong(1);
> >    tisec2 = rs.getString(2);
> >    tisec3 = rs.getString(3);
> >    for (int j = 0; j <= 9; j++) {
> >       pc[j] = rs.getString("tipc" + (Integer.toString(j+1)).trim());
> >    }
> >    for (int j = 0; j <= 9; j++) {
> >       hrs[j] = rs.getBigDecimal("tihr" +
> (Integer.toString(j+1)).trim());
> >    }
> >    for (x = 0; x <= 9; x++) {
> >       workCodeQuery = "select wcnonw from clocfile01.ckworkcd where
> wccode
> > = '" + pc[x].trim() + "'";
> >       PreparedStatement queryCKWORKCD =
> > nal820.conn.prepareStatement(workCodeQuery);
> >       ResultSet rs2 = queryCKWORKCD.executeQuery();
> >       wcnonw = rs2.getString(1);
> >       // further processing...
> >    }
> >    rs2.close();
> >    queryCKWORKCD.close();
> > }
> > rs.close();
> > queryKTIMLD4.close();
> >
> > Here's the error I'm getting...it's happening at the following line:
> > wcnonw = rs2.getString(1);
> >
> > Cursor position not valid.
> > java.sql.SQLException: Cursor position not valid.
> >         at java.lang.Throwable.<init>(Throwable.java:96)
> >         at java.lang.Exception.<init>(Exception.java:44)
> >         at java.sql.SQLException.<init>(SQLException.java:45)
> >         at
> > com.ibm.as400.access.JDError.throwSQLException(JDError.java:395)
> >         at
> >
>
com.ibm.as400.access.AS400JDBCResultSet.getValue(AS400JDBCResultSet.java:351
> 9)
> >         at
> >
>
com.ibm.as400.access.AS400JDBCResultSet.getString(AS400JDBCResultSet.java:31
> 73)
> >         at DailyLaborHours.addDay(DailyLaborHours.java:395)
> >         at DailyLaborHours.getdata(DailyLaborHours.java:84)
> >         at DailyLaborHours.main(DailyLaborHours.java:53)
> >
> > Do I have to do something special to be able to use a ResultSet of one
> > query inside the ResultSet of another query?...Thanks in advance...
> >
> > Shane Cessna
> > iSeries Programmer
> > iSeries WAS Administrator
> > North American Lighting, Inc.
> > (618) 662-4483 x2776
> > shane_cessna@xxxxxxx
>
>
> 
--------------------------------------------------------------------------
--
> ----
>
>
> -- 
> 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.
>
>
>


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


> -- 
> 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.
>
>