Resultset returns more then what I expect.


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: Resultset returns more then what I expect.

Hybrid View

  1. #1
    Join Date
    Feb 2006
    Posts
    2

    Resultset returns more then what I expect.

    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

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

    Code:
    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.
    Code:
                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.

  2. #2
    Join Date
    Oct 2005
    Location
    New Jersey
    Posts
    255
    "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:
    Code:
                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 = "";
                }
    Happiness is good health and a bad memory.

  3. #3
    Join Date
    Oct 2005
    Location
    New Jersey
    Posts
    255
    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.
    Happiness is good health and a bad memory.

  4. #4
    Join Date
    Feb 2006
    Posts
    2
    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

  5. #5
    Join Date
    Oct 2005
    Location
    New Jersey
    Posts
    255
    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.
    Happiness is good health and a bad memory.

Similar Threads

  1. Replies: 0
    Last Post: 03-27-2002, 11:53 AM
  2. Resultset implementation
    By KNR in forum Java
    Replies: 2
    Last Post: 06-23-2000, 08:13 PM
  3. Using SQL Against Resultset
    By Faina in forum VB Classic
    Replies: 4
    Last Post: 04-25-2000, 09:53 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center