× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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


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



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.