db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Oyvind.Bakk...@Sun.COM
Subject Re: I need some advice to choose database for an upcomming job
Date Tue, 08 Nov 2005 15:18:39 GMT
Daniel John Debrunner wrote:
> Oyvind.Bakksjo@Sun.COM wrote:
> 
> 
>>Daniel John Debrunner wrote:
> 
> 
>>>In addition I guess you showRow() does a next() and then
>>>the rs.gerXXX()? I think if you called t2.executeQuery() between a
>>>next() and the rs.getXXX() calls on the other thread, I think you will
>>>see problems.
>>
>>
>>I tried that, but that did not change any behaviour. Besides, why would
>>it? It doesn't seem logical to me why you would get an exception exactly
>>there, if the resultset isn't closed and you don't get an exception on
>>the subsequent next() and rs.getXXX() calls after the second execute.
> 
> 
> My thinking is that the executeQuery() will cause a commit which will
> cause the open held ResultSet used by the other thread to move off the
> row, as required by the SQL standard. Thus before any getXXX() call can
> be made, the held cursor needs to be re-positioned using next(). I think
> you have said before that Derby doesn't act this way, though looking at
> the embedded code it should.

So I tried the following:

             conn.setAutoCommit(true);
             Statement st1 = conn.createStatement();
             try {
                 st1.execute("drop table t1");
             } catch (SQLException e) {
                 // Ignore
             }
             st1.execute("create table t1 (a int)");
             st1.execute("insert into t1 
values(0),(1),(2),(3),(4),(5),(6)");

             PreparedStatement ps1 = conn.prepareStatement("select * 
from SYS.systables",
 
ResultSet.TYPE_FORWARD_ONLY,
 
ResultSet.CONCUR_READ_ONLY,
 
ResultSet.HOLD_CURSORS_OVER_COMMIT);
             PreparedStatement ps2 = conn.prepareStatement("select * 
from t1",
 
ResultSet.TYPE_FORWARD_ONLY,
 
ResultSet.CONCUR_READ_ONLY,
 
ResultSet.HOLD_CURSORS_OVER_COMMIT);
             ResultSet rs1 = ps1.executeQuery();
             ResultSetMetaData md1 = rs1.getMetaData();
             rs1.next();
             show(rs1, md1);
             rs1.next();
             ResultSet rs2 = ps2.executeQuery();
             ResultSetMetaData md2 = rs2.getMetaData();
             show(rs1, md1);
             rs2.next();
             show(rs2, md2);
             rs2.next();
             show(rs2, md2);
             rs1.next();
             show(rs1, md1);
             rs2.next();
             show(rs2, md2);


(The show method calls ResultSet.getString on all columns.)

As you can see, the second executeQuery() call is between rs1.next() and 
show(rs1, md1). There's no exception thrown - no need to reposition, it 
seems. I changed the prepareStatement for ps1 to use 
CLOSE_CURSORS_AT_COMMIT, and then I got an exception about the result 
set being closed, so there's definitely a commit made at the second 
executeQuery.

-- 
Oyvind Bakksjo
Sun Microsystems, Database Technology Group
Trondheim, Norway
http://weblogs.java.net/blog/bakksjo/

Mime
View raw message