ansaurus

Question

Why do I keep on getting an exception-illegal operation on ResultSet?

Answer 1

A:

Remove the following line, it serves no purpose. Its trying to the the column value from the ResultSet but you haven't scrolled to the first row yet

                                        rs.getString(i);

objects 2010-04-07 22:21:15

I think the intent is `result += rs.getString(i) + "\n"`.

Kevin Brock 2010-04-08 02:25:51

Answer 2

+1 A:

if rs is null, then rs.equals(null) will throw a NullPointerException instead of returning false. Nevertheless if that is not the exception you are getting then the problem lies somewhere else.

Kathy Van Stone 2010-04-07 22:22:26

In this case `rs` would never be `null`.

Kevin Brock 2010-04-08 02:22:51

I suspected so, but given that the call is behind a custom class, I couldn't be certain.

Kathy Van Stone 2010-04-08 15:06:39

Answer 3

+1 A:

You always need to call next() on the ResultSet, before accessing it. If not, the pointer points to the row before the first. You do this with while-loops in your code, but some places you dont, which generates an error:

for (int i = 1; i < numberOfColumns; i++) {
  rs.getString(i);
  result += i + "/n";
}

Ohh, and as another poster mentions, you dont assign the result to a variable, so the rs.getString(i) call has no effect. But this is probably your source of error.

Frederik Wordenskjold 2010-04-07 22:24:07

Answer 4

+2 A:

I see a few things

First rs is never null. If there are no rows then rs.next() will return false
ResultSet starts position BEFORE the first row. You need to call rs.next() to move to the first row which, as above, will return false if there are no rows.

The typical pattern for ResultSet use is...

while (rs.next())
{
  rs.getXXX();
}

ResultSet should ALWAYS be close()d when you are done, otherwise you may leak database resources. This also applies to Connection, Statement, PreparedStatement so check your DB code in other classes.
When looping over the column metadata you need to do..

for (int i = 1; i <= numberOfColumns; i++) { }

Note the <= rather than <. As you have correctly found that JDBC column indexes start at 1.

It would be worth considering using a library that hides all the JDBC noise. Consider spring which has a basic JDBC abstraction while retaining all the power or all the way up to hibernate which is very powerful but has quite a learning curve. In your case I'd recommend just use the spring stuff.

See: Spring docs

Mike Q 2010-04-07 22:29:54

Also it would be good to note that when checking an object reference for `null` to use normal `==` instead of `equals()` (i.e. `rs == null` instead of `rs.equals(null)`). In fact, using the equals form here should also be `false` because of the contract on equals or throw an exception if `rs` really was `null`.

Kevin Brock 2010-04-08 02:21:23

Answer 5

+1 A:

Also, take a look into the javadoc/code for the DB class' execSQL(query) method. Typically, a ResultSet is never null, e.g. like when you use PreparedStatement.executeQuery. However, check into the that execSQL method to verify whether or not it will ever return a null ResultSet.

elduff 2010-04-07 22:34:39

THANKS EVERYONE

2010-04-18 21:21:49

Answer 6

A:

Several have pointed out some of your problems in the code. The reason for your exception is because you have not advanced to the first row using rs.next(). If you expect that your SQL statement will only retrieve a single row then you probably should change your rs.equals(null) statement to:

if (!rs.next()) {
    ...code here to set not found...
} else {
    ...code here to retrieve the columns...
}

Here are some other tips. In the code to retrieve the columns, your use of rs.getString(i) doesn't do anything, the result variable is just accumulating the column numbers with "/n" (not new-line; probably that should be "\n"). So the loop inside that section should probably become:

for (int i = 1; i <= numberOfColumns; i++) {
    result += rs.getString(i) + "\n";
}

But concatenating to a immutable String is not good and can result in slow execution for large numbers of concatenations. Use a StringBuilder instead and initialize it to a reasonable size, something like this:

StringBuilder sb = new StringBuilder(256);
for (int i = 1; i <= numberOfColumns; i++) {
    sb.append(rs.getString(i)).append("\n");
}
result = sb.toString();

Later you loop through the results set, so in the loop through the columns are you trying to get column names from the meta data? In that case:

StringBuilder sb = new StringBuilder(256);
for (int i = 1; i <= numberOfColumns; i++) {
    sb.append(rsmd.getColumnName(i)).append("\n");
}
result = sb.toString();

But since you need to check for the first row the next loop will not count the number of rows correctly so you will need to adjust for that, perhaps (and there is no need to check result at that point since it is guaranteed to no longer be an empty string since there must be at least one column in the table):

do {
    RowCount++; // recommend using rowCount.
} while (rs.next());

In the end, what are you using result for? It seems that this is just used to determine if there were some columns in the result. If that is all then you can eliminate most of this code. For what you actually have (I don't know if this is your intent), this could be reduced to (in the else if where you do the DB interaction):

DB db = new DB();
try {
    db.connect();
    String query = "select count(1) from Stock where Part_name =" + ...;
    ResultSet rs = db.execSQL(query);
    if (!rs.next()) {
        PartNotFound nf = ...;
        nf.setVisible(true);
    } else {
        Receptionist_FranchiseePartFound part = new Receptionist_FranchiseePartFound();
        part.setVisible(true);
        rowCount = rs.getInt(1);
        part.getTable().addRowSelectionInterval(0, rowCount);
    }
} catch (Exception e) {
    ...error handling with stack trace/JOptionPane...
}

Note that if you are just after the count of rows then it is best to let the database engine do that for you - looping through the result rows just to count them also means that all that data must be sent to your application. Instead, if you just use the aggregate function then the database only needs to send a single row and column to your application.

Kevin Brock 2010-04-08 02:54:40

ansaurus

tags:

views:

answers:

Why do I keep on getting an exception-illegal operation on ResultSet?

related questions