tdeloggio
asked on
JDBC anomal: java.sql.ResultSet.getFetchSize() returns zero, but resultset works fine.
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(t ransRS.get FetchSize( ));
javax.swing.JOptionPane.sh owMessageD ialog(null , message, "blah", dtype);
//msg box for debugging
int startingPoint = 0;
while (startingPoint <= transRS.getFetchSize()) {
transRS.relative(startingP oint);
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.Connectio n 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.PL AIN_MESSAG E;
javax.swing.JOptionPane.sh owMessageD ialog(null , sqle, "SQL Exception in Method dbConnection.doQuery", dtype);
} catch (Exception ge) {
// a dialog box to report the exception
int dtype = javax.swing.JOptionPane.PL AIN_MESSAG E;
javax.swing.JOptionPane.sh owMessageD ialog(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. :)
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(t
javax.swing.JOptionPane.sh
//msg box for debugging
int startingPoint = 0;
while (startingPoint <= transRS.getFetchSize()) {
transRS.relative(startingP
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.Connectio
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.PL
javax.swing.JOptionPane.sh
} catch (Exception ge) {
// a dialog box to report the exception
int dtype = javax.swing.JOptionPane.PL
javax.swing.JOptionPane.sh
}
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. :)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER