Re: JDBC question...

Hi,

ignore the "'", these are handled ba the JDBC driver:

queryCKWORKCD.setString(1, pc[x].trim())




Shane_Cessna@xxxxxxx wrote:

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