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 Tue, 03 Feb 2009 04:03:55 GMT
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.

By the way, PostgreSQL displays the correct row numbers for both result sets. I write my code
to run with either database engine, depending on whether the application is to be distributed
with a small scale (single user) embedded database or with a large scale client/server database.

John


Mime
View raw message