MySQL - wrong (doubles rows) resultset via JDBC
azur Jan 16, 2003 1:53 PMI 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**********