4 Replies Latest reply on Jan 27, 2003 10:52 PM by AzUr

    MySQL - wrong (doubles rows) resultset via JDBC

    AzUr Newbie

      I use mysql with jboss-3.0.0_tomcat-4.0.3 on a Linux system.
      I can connect and use SQL via JDBC calls.
      My problem is when I requests "larger" datachunks. The example is

      select InstrumentId,TradeDate,Value from Quotes where InstrumentId=2789 and TradeDate>"1999-01-01" and TradeDate<="1999-05-03" order by InstrumentId,TradeDate

      In for example phpAdmin the answer is correct but when I do the same in the EJB code my ResultSet in the code (seen below) produces ONLY doubles as seen in the debug output.

      The debug output is produced with the
      System.out.print() as seen in the code. Also note that we only query the original ResultSet not any modified ResultSet in the debug output!

      What to do??? Despair?

      Many regards, AzUr

      **************Code start**************************

      //---HANDLE RESULTSET
      ps.executeQuery();
      rs = ps.getResultSet();
      boolean found = false;
      int actual_id = 0;
      int previous_id = 0;
      // Init result array with empty Quote arrays.
      for(int i = 0; i<aInstrumentId.length; i++){
      result = new Quote[0];
      }
      while( rs.next() ){
      actual_id = rs.getInt(1);
      System.out.print("Row: " + rs.getRow() + "InstrumentId: " + rs.getInt(1));
      if(found){
      if(previous_id != actual_id){
      // Arrange result according to indata. Find index to store result in.
      result[getIdIndex(aInstrumentId, previous_id)] = toArray(quotes);
      quotes = new Vector();
      previous_id = actual_id;
      }
      }else{
      // First time in loop, executed once.
      found = true;
      previous_id = actual_id;
      quotes = new Vector();
      }
      quotes.addElement( new Quote(rs.getDate(2), rs.getFloat(3)) );
      System.out.print("Date: "+ rs.getDate(2) + " Quote: " + rs.getFloat(3));
      }
      // Handle last iteration of while loop above
      if(found){
      // Arrange result according to indata. Find index to store result in.
      result[getIdIndex(aInstrumentId, actual_id)] = toArray(quotes);
      }
      }catch(SQLException e){
      throw new FlEJBException(e.getMessage());
      }finally{
      try{
      if(rs != null){
      rs.close();
      }
      if(ps != null){
      ps.close();
      }
      if(con != null){
      con.close();
      }
      }catch(SQLException e){
      throw new FLEJBException(e.getMessage());
      }
      }
      return result;
      ***************Code end********************

      **********Debug start**********
      19:59:59,926 INFO [STDOUT] Row: 1InstrumentId: 2789
      19:59:59,935 INFO [STDOUT] Date: 1999-01-04 Quote: 4.35
      19:59:59,941 INFO [STDOUT] Row: 2InstrumentId: 2789
      19:59:59,947 INFO [STDOUT] Date: 1999-01-04 Quote: 4.35
      19:59:59,953 INFO [STDOUT] Row: 3InstrumentId: 2789
      19:59:59,959 INFO [STDOUT] Date: 1999-01-05 Quote: 4.295
      19:59:59,965 INFO [STDOUT] Row: 4InstrumentId: 2789
      19:59:59,971 INFO [STDOUT] Date: 1999-01-05 Quote: 4.295
      19:59:59,972 INFO [STDOUT] Row: 5InstrumentId: 2789
      19:59:59,974 INFO [STDOUT] Date: 1999-01-07 Quote: 4.275
      19:59:59,975 INFO [STDOUT] Row: 6InstrumentId: 2789
      19:59:59,976 INFO [STDOUT] Date: 1999-01-07 Quote: 4.275
      19:59:59,977 INFO [STDOUT] Row: 7InstrumentId: 2789
      19:59:59,979 INFO [STDOUT] Date: 1999-01-08 Quote: 4.31
      19:59:59,980 INFO [STDOUT] Row: 8InstrumentId: 2789
      19:59:59,982 INFO [STDOUT] Date: 1999-01-08 Quote: 4.31
      19:59:59,983 INFO [STDOUT] Row: 9InstrumentId: 2789
      19:59:59,984 INFO [STDOUT] Date: 1999-01-11 Quote: 4.39
      19:59:59,985 INFO [STDOUT] Row: 10InstrumentId: 2789
      19:59:59,987 INFO [STDOUT] Date: 1999-01-11 Quote: 4.39
      19:59:59,988 INFO [STDOUT] Row: 11InstrumentId: 2789
      19:59:59,990 INFO [STDOUT] Date: 1999-01-12 Quote: 4.375
      19:59:59,991 INFO [STDOUT] Row: 12InstrumentId: 2789
      19:59:59,992 INFO [STDOUT] Date: 1999-01-12 Quote: 4.375
      19:59:59,993 INFO [STDOUT] Row: 13InstrumentId: 2789
      19:59:59,995 INFO [STDOUT] Date: 1999-01-13 Quote: 4.315
      19:59:59,996 INFO [STDOUT] Row: 14InstrumentId: 2789
      19:59:59,997 INFO [STDOUT] Date: 1999-01-13 Quote: 4.315
      19:59:59,998 INFO [STDOUT] Row: 15InstrumentId: 2789
      20:00:00,000 INFO [STDOUT] Date: 1999-01-14 Quote: 4.31
      20:00:00,001 INFO [STDOUT] Row: 16InstrumentId: 2789
      20:00:00,003 INFO [STDOUT] Date: 1999-01-14 Quote: 4.31
      20:00:00,004 INFO [STDOUT] Row: 17InstrumentId: 2789
      20:00:00,005 INFO [STDOUT] Date: 1999-01-15 Quote: 4.285
      20:00:00,007 INFO [STDOUT] Row: 18InstrumentId: 2789
      20:00:00,008 INFO [STDOUT] Date: 1999-01-15 Quote: 4.285
      20:00:00,009 INFO [STDOUT] Row: 19InstrumentId: 2789
      20:00:00,011 INFO [STDOUT] Date: 1999-01-18 Quote: 4.268
      20:00:00,012 INFO [STDOUT] Row: 20InstrumentId: 2789
      20:00:00,013 INFO [STDOUT] Date: 1999-01-18 Quote: 4.268
      20:00:00,015 INFO [STDOUT] Row: 21InstrumentId: 2789
      **********Debug end**********