Solved

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

Posted on 2003-11-04
3
1,354 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
  • 2
3 Comments
 
LVL 92

Accepted Solution

by:
objects earned 125 total points
Comment Utility
>  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
Comment Utility
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
Comment Utility
Yes thats often what people do.

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

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
strCount chalenge 3 50
json format text only 4 64
create a gui in perl 3 42
recursion example 16 66
For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…

763 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now