Re: JDBC question...

    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


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