db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: Can an embedded Derby connection support multiple simultaneous result sets?
Date Tue, 03 Feb 2009 10:15:06 GMT
"John T. Dow" <john@johntdow.com> writes:

> On Mon, 02 Feb 2009 17:10:52 +0100, Knut Anders Hatlen wrote:
>
>>"John T. Dow" <john@johntdow.com> writes:
>>
>>> I think the answer is no, but I couldn't find anything in the documentation on
the subject. The best I found was this in one of the user group discussions...
>>>
>>>     The result set is closed as soon as one of the following occurs:
>>>     >   ...
>>>     >   another Statement object is executed on the same connection
>>>
>>> Is it necessary to open a new connection for each statement/resultset?
>>
>>You can have multiple open statements per connection (but only one open
>>result set per statement). So this (untested) code which has two active
>>result sets at the same time on the same connection should work:
>>
>>  Statement s1 = conn.createStatement();
>>  Statement s2 = conn.createStatement();
>>
>>  ResultSet rs1 = s1.executeQuery("select * from t1");
>>
>>  while (rs1.next()) {
>>    ResultSet rs2 = s2.executeQuery("select * from t2");
>>    while (rs2.next()) {
>>      //....
>>    }
>>  }
>>
>>-- 
>>Knut Anders
>
>
> I tried the code above and it works. That is, it displays data from
> both result sets.
>
> Then I made a small change. I added calls getRow() and displayed the
> results. (The createStatement call has to be changed, see below.)
>
> It seems that getRow() only returns a non-zero value if next() has
> just been called on that resultset.
>
> For example, after calling rs2.next(), rs1.getRow() returns 0. 
>
> However, the resultset is positioned properly because
> rs1.getString("colname") returns the correct value.
>
> try {
>   Statement s1 = jdbcConn.createStatement(
> 	ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
>   Statement s2 = jdbcConn.createStatement(
> 	ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
>
>   ResultSet rs1 = s1.executeQuery("select * from table1");
>
>   while (rs1.next()) {
>
>     rs1.getRow()     RETURNS CORRECT VALUES
>
>     ResultSet rs2 = s2.executeQuery("select * from table2");
>     while (rs2.next()) {
>
>       rs1.getRow()     RETURNS 0 FOR EVERY ROW
>
>       rs2.getRow()     RETURNS CORRECT VALUES
>
>     }
>   }
> } catch (SQLException ex) {
> }
>
> I also did the following, with the same results.
>
> Statement s1 = jdbcConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
> Statement s2 = jdbcConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
>
> Note that I've been testing getRow() < 1 to see if the result set is
> empty. I guess that at the time the result set is created, I can do
> next() to determine if it's empty and then set a flag. But what
> happens if later I delete a row or rows from the result set? That
> logic would also have to do a next and/or previous and then set the
> flag. Testing getRow() < 1 would sure be nice.

Hi John,

I'm assuming that you're running with auto-commit enabled. In that case
the transaction is committed when a ResultSet is closed. In the case
above, that happens each time s2.executeQuery() is called because
executeQuery() implicitly closes existing ResultSets on the same
statement. When the transaction is committed, all the open ResultSets in
that transaction (rs1 in this case) lose their positions and won't be
positioned on a row until next() or some other positioning method is
called. The JDBC specification says that ResultSet.getRow() is supposed
to return 0 if there's no current row, so that's why you always get 0
from rs1.

I think you'll see the results you expect if you disable auto-commit.

-- 
Knut Anders

Mime
View raw message