db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "John T. Dow" <j...@johntdow.com>
Subject Re: Can an embedded Derby connection support multiple simultaneous result sets?
Date Wed, 04 Feb 2009 15:38:22 GMT
On Wed, 04 Feb 2009 14:02:17 +0100, Knut Anders Hatlen wrote:

>"John T. Dow" <john@JohnTDow.com> writes:
>
>> On Tue, 03 Feb 2009 11:48:58 +0100, Knut Anders Hatlen wrote:
>>
>>>>>>"John T. Dow" <john@johntdow.com> writes:
>>>>>
>>>>> For example, after calling rs2.next(), rs1.getRow() returns 0. 
>>>>>
>>>>> However, the resultset is positioned properly because
>>>>> rs1.getString("colname") returns the correct value.
>>>
>>>Looks like I missed that last sentence when I answered your mail the
>>>first time. I still think that rs1 is not positioned on a row, and
>>>that's why rs1.getRow() returns null. The difference between getRow()
>>>and getString() is that the ResultSet object has a copy of the current
>>>row from which getString() fetches the string value, and it's not
>>>affected by the fact that the underlying cursor has lost its
>>>position. The row number, on the other hand, is not cached in the
>>>ResultSet, so getRow() always fetches the row number from the cursor and
>>>therefore detects that there's no current row.
>>>
>>>-- 
>>>Knut Anders
>>
>>
>> You are correct, I was using autocommit and when it's turned off,
>> getRow() continues to report the row properly.
>>
>> PostgreSQL returns the correct row even with autocommit on.
>>
>> That's a bummer. I'm not about to rewrite lots of code so the standard
>> mode is autocommit off. Most of the time having it on is nice, it's
>> the exception that I want it off.
>>
>> I was relying on getting a 0 to indicate that the result set is
>> empty. (After opening, I move to the first row by default so getting a
>> 0 has been a reliable indicator of an empty result set.)
>
>Assuming that you use either next() or first() to move to the first row,
>you could just check if they return false. If they do, the ResultSet is
>empty.
>
>> I sure wish there was an isEmpty() method for result sets.
>>
>> I need a database-agnostic technique for determining if a result set
>> is empty. Perhaps do next() and then previous(), with appropriate
>> tests for being at the beginning or end initially. (I always use
>> scrollable result sets.)
>
>Alternatively, if you call isBeforeFirst() right after you have created
>the scrollable ResultSet, it should return true if the ResultSet is
>non-empty and false if it's empty per the JDBC specification.
>
><URL:http://java.sun.com/javase/6/docs/api/java/sql/ResultSet.html#isBeforeFirst()>
>
>-- 
>Knut Anders


If you have just created a resultset, then doing next() indeed tells one right away if it's
empty. So if I've created a resultset for an immediate need, there is no issue. Create it,
get the data, close it.

The resultsets of interest are those that are associated with frames that are used to display
and update information from tables, such as a customer table, an inventory table, etc. These
are opened and stay open. People can do things with them at random intervals (event driven
programming). They can move from row to row, export data from all the rows, update data, delete
a row or rows, etc.

It's in that situation that I wanted a nice, foolproof way of knowing if the result set is
open and has data. The result set could stay open for hours even.

By convention, for such resultsets, I always do a next() right after opening, and whenever
the user repositions (eg by clicking a button "<<", "<". ">", ">>" or deleting
a row), my logic always ensures that the resultset is left on a row (and not before or after
the rows). Thus getting the row number is a way that I can ensure that there is data I'm currently
positioned on a row.

I might just rewrite my logic a little so I can guarantee that I'll never have a resultset
that is empty. That is, if it's opened and finds no rows, set the resultset variable to null
and test that. If the last row is ever deleted, set it to null.

For the moment, I've been trying this routine that I added to my library. It has special logic
for Derby (because PostgreSQL returns the row number at all times). "dbengine" is a String
that contains the name of the database engine: eg "Derby" or "PostgreSQL".

public static int getRow(ResultSet rs) throws SQLException {
  if (rs == null) {
    return -1;
  }
  int row = rs.getRow();
  if (row > 0 || ! dbengine.equalsIgnoreCase("Derby")) {
    return row;
  }
  if (rs.isAfterLast()) {
    return 0;
  }
  rs.next();
  rs.previous();
  return rs.getRow();
}

Anyway, it sure would be convenient if there was an isEmpty that would work at all times.
But, that's a JDBC issue, not a Derby issue.

Thanks for your help, my program is now functional.

John


Mime
View raw message