"John T. Dow" <[email protected]> writes: > On Mon, 02 Feb 2009 17:10:52 +0100, Knut Anders Hatlen wrote: > >>"John T. Dow" <[email protected]> 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
