Resultset returns more then what I expect.

[Mailman]

Hello everybody,

First time to post and I am a beginner with JAVA.

With the query I only want 20 rows as I stated in de SQL query.

If I do this

while(rs.next(){
       //Do something
}

it comes with the following error message:

java.sql.SQLException: [Microsoft][ODBC-stuurprogrammabeheer] Ongeldige cursorstatus

//-> This is dutch it means: [ODBC-Drivermanager] No legal cursorstate. //think that has to do with:

stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6958)

at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115)

at sun.jdbc.odbc.JdbcOdbc.SQLGetDataString(JdbcOdbc.java:3908)

at sun.jdbc.odbc.JdbcOdbcResultSet.getDataString(JdbcOdbcResultSet.java:5702)

at sun.jdbc.odbc.JdbcOdbcResultSet.getString(JdbcOdbcResultSet.java:356)

at sun.jdbc.odbc.JdbcOdbcResultSet.getString(JdbcOdbcResultSet.java:413)

at alarmcentrale.akties.getRecentList(akties.java:63)

at alarmcentrale.frmRecent.windowOpened(frmRecent.java:87)

at java.awt.Window.processWindowEvent(Window.java:1118)

at javax.swing.JFrame.processWindowEvent(JFrame.java:266)

at java.awt.Window.processEvent(Window.java:1079)

at java.awt.Component.dispatchEventImpl(Component.java:3615)

at java.awt.Container.dispatchEventImpl(Container.java:1627)

at java.awt.Window.dispatchEventImpl(Window.java:1606)

at java.awt.Component.dispatchEvent(Component.java:3477)

at java.awt.EventQueue.dispatchEvent(EventQueue.java:456)

at java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchThread.java:201)

at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:151)

at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:145)

at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:137)

at java.awt.EventDispatchThread.run(EventDispatchThread.java:100)


It crashes because it only has 20 records in it's resultset and rs.next() is still true but there's no data. If I do the following code I get a Resultset of 24 rows. That is strange in my opinion.

            ResultSet rs = db.openConnection("SELECT TOP 20 * FROM Melding ORDER BY Datum, Tijd");
            //System.out.println(rs.isBeforeFirst());
            //rs.first();
            //rs.relative(9);
            rs.last();
            recordCount = rs.getRow();
            rs.beforeFirst();
            rs.next();
            for(int j=0;j<recordCount;j++){
                zone = rs.getString("Zone");
                datum = rs.getDate("Datum");
                tijd = rs.getTime("Tijd");
                plaats = rs.getString("Plaats");
                adres = rs.getString("Adres");
            
                rij = zone + " " + datum + " " + tijd + " " + plaats + " " +
                      adres;
                System.out.println(rij + " " + i);
                lijst.add(rij);
                rij = "";
                rs.next();
            }

What am I doing wrong?
Greetings.

[aniseed]

"for" loop???

You already call a rs.next() before the for loop and twenty times inside the loop [at the end]. In the last iteration, it will try to move to the 21st record and read it, which will result in the exception.

The first modification you need to make is:

            ResultSet rs = db.openConnection("SELECT TOP 20 * FROM Melding ORDER BY Datum, Tijd");
            rs.last();
            recordCount = rs.getRow();
            rs.beforeFirst();

            for(int j=0;j<recordCount;j++){
                rs.next();		// Move cursor to next record.
                zone = rs.getString("Zone");
                datum = rs.getDate("Datum");
                tijd = rs.getTime("Tijd");
                plaats = rs.getString("Plaats");
                adres = rs.getString("Adres");
            
                rij = zone + " " + datum + " " + tijd + " " + plaats + " " +
                      adres;
                System.out.println(rij + " " + i);
                lijst.add(rij);
                rij = "";
            }

[aniseed]

Another suggestion to you is that you should avoid using the count of records whenever possible. In your case, it is absolutely not necessary to do that way. You could always use a while loop with rs.next() and fetch all the records.

[Mailman]

The reason for me to count the records was just a test for me. So i'd see how many records the SQL-query came up with. However It isn't resolved. But I think the problem is with the query itself! You ask for a top twenty, but I think here is the catch -> What happens if you have there are more results with the same criteria I mean:

position Name Time
1 Peter 0.30
2 Mark 0.45
2 Jim 0.45
3 Pim 0.48

If I make a SELECT TOP 2 * FROM table ORDER BY time what happens with the resultset? I know Access returns two, but what does the resultset do? Does it return three because positions 2 are the same? This could be it?

Or am I thinking wrong here?

thank

[aniseed]

The TOP will select the topmost two, in your case.
1 Peter 0.30
2 Mark 0.45

Databases usually have their own way of internal ordering if the criterion for the ORDER BY clause generates matching columns. Even when the Time is same, Access will have it's own method of arranging the records which will be purely implementation specific. I guess it has something to do with the way records are stored internally.

When you specify a limiting condition to your query, the resultset will be similar to selecting the number of specified rows and ignoring the rest indiscriminately.