?
Solved

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

Posted on 2003-11-04
3
Medium Priority
?
1,379 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 500 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Java had always been an easily readable and understandable language.  Some relatively recent changes in the language seem to be changing this pretty fast, and anyone that had not seen any Java code for the last 5 years will possibly have issues unde…
Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
Suggested Courses
Course of the Month8 days, 4 hours left to enroll

765 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