Solved

JDBC anomal: java.sql.ResultSet.getFetchSize() returns zero, but resultset works fine.

Posted on 2003-11-04
3
1,367 Views
Last Modified: 2012-05-04
This question is concerning my senior project.  (Widener University - Computer Science)  I'm using j2sdk version 1.4.2 and mysql connector J version 3.0.8 to connect a java application to a mysql database for the purpose of doing some data mining.  The project is nearly 2/3 complete, but I have run into  a snag.  For the routine that discovers sub-itemsets from my transaction table, i need to know the size of the resultset after a query.  I'm using ResultSet.getFetchSize() to accomplish this, but the method returns zero even though the ResultSet is fully populated with the correct and anticipated result.  I fear the problem may be that I'm passing the result set between classes, but I'm not really sure.  Here's the code in context:

try {
                con = db.getConnection();
                db.doUpdate(con, "DELETE FROM itemsets;");
                result = db.doQuery(con, "SELECT DISTINCT tid FROM transactions;");
                int isid = 1;
                while (result.next()) {
                    int tid = result.getInt("tid");
                    String query = "SELECT iid FROM transactions WHERE tid = " + tid + " ORDER BY indx;";
                    transRS = db.doQuery(con, query);
                    transRS.next();
                    String message = java.lang.String.valueOf(transRS.getFetchSize());
                    javax.swing.JOptionPane.showMessageDialog(null, message, "blah", dtype);
                    //msg box for debugging
                   
                    int startingPoint = 0;
                    while (startingPoint <= transRS.getFetchSize()) {
                        transRS.relative(startingPoint);
                        for (int i = startingPoint + 1; i == transRS.getFetchSize(); i++) {
                            for (int j = startingPoint + 1; j == i; j++) {
                                db.doUpdate(con, "INSERT INTO itemsets (isid, iid) VALUES (" + isid + ", " + transRS.getInt("iid") + ");");
                                transRS.next();
                            }
                            isid++;
                            transRS.first();
                        }
                        startingPoint++;
                    }
                    transRS.close();
                    current++;
                    Thread.sleep(1);
                }
                result.close();
                db.closeConnection(con);
            }

the "db" class you see here is a class i wrote to cut down on the size of the program, it just handles the connections and queries in a convenient way.  It's worked perfectly for many other database routines in this program, but here's the doQuery method just in case i'm doing something wrong.

/**
     *  Method to return the results of an SQL query
     */
    public java.sql.ResultSet doQuery(java.sql.Connection conn, String sql) throws java.sql.SQLException {
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
        } catch (java.sql.SQLException sqle) {
            // a dialog box to report the exception
            int dtype = javax.swing.JOptionPane.PLAIN_MESSAGE;
            javax.swing.JOptionPane.showMessageDialog(null, sqle, "SQL Exception in Method dbConnection.doQuery", dtype);
        } catch (Exception ge) {
            // a dialog box to report the exception
            int dtype = javax.swing.JOptionPane.PLAIN_MESSAGE;
            javax.swing.JOptionPane.showMessageDialog(null, ge, "General Exception in Method dbConnection.doQuery", dtype);
        }
       
        return rs;
    }

I Apologize for the super-sized post, but seeing the code is probably necessary.  As a last resort, i could always run a query that just counts the number of transactions, and read in the result... but this is already a query-intensive routine so I figured I'd try to get this method to work.  Thanks a lot for any help.  :)
0
Comment
Question by:tdeloggio
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 92

Accepted Solution

by:
objects earned 125 total points
ID: 9683458
>  I'm using ResultSet.getFetchSize() to accomplish this

getFetchSize() does not return the size of the result set.
It returns the fetch size, which is the number of rows at a time a fetched from the server.

There is no standard way to get the size of a result set in advance.
0
 

Author Comment

by:tdeloggio
ID: 9683537
Thank you objects, guess I'll just have to make another query to get the size of the result.  
0
 
LVL 92

Expert Comment

by:objects
ID: 9683552
Yes thats often what people do.

http://www.objects.com.au
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
V.simple question about Android packages and libraries 1 38
Java exception bubble up 2 24
Better way to make a string with template variables in java 3 34
Java array 10 62
After being asked a question last year, I went into one of my moods where I did some research and code just for the fun and learning of it all.  Subsequently, from this journey, I put together this article on "Range Searching Using Visual Basic.NET …
Introduction Java can be integrated with native programs using an interface called JNI(Java Native Interface). Native programs are programs which can directly run on the processor. JNI is simply a naming and calling convention so that the JVM (Java…
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question