Hi !
We are using JDBC 2.0 to connect to Oracle 8.1.7.The web server is JRUN 3.1.
Both DB Server (Oracle) and Web server (JRUN) are hosted on physically different machines.
We are using JDBC type 4 driver (that is, classes12.zip).
We have queries which fetch CLOB data from Oracle. Some times the queries return more than
3000 rows. We are concerned about the response time for fetching this data.
The Question is, when the ResultSet object is fetched from the Database, does it go all
the way to the DB server every time for each rs.next() and fetch one row at a time, OR
it fetches all rows in one go and iterates only through ResultSet object in the middle tier
(that is Web Server) ?
If for each rs.next(), it goes all the way to DB server and brings back one row, then is there
any parameter we can set in JDBC or in JRUN so that it will fetch the entire result set in bulk
instead of making a return trip to DB server for each row ?
If you are interested enough, Our code is somewhat like this:
Connection con; // Connection Object
Compound objMatchComp; // Compound Entity object
String query = “
PreparedStatement ppdStmt = con.prepareStatement(query);
ppdStmt.setLong(1,lSearchId);
ppdStmt.setString(2,strUserId);
try{
ResultSet rsCompounds = ppdStmt.executeQuery(query);
ArrayList arraylstMatchComps = new ArrayList();
while (rsCompounds.next()) {
objMatchComp = new Compound();
objMatchComp.setCompoundId(rsCompounds.getLong(1));
Clob clob = rsCompounds.getClob(2);
long i=1;
int j=(int)clob.length();
String struc = clob.getSubString(i,j);
objMatchComp.setStructure(struc);
}
catch(Exception e){
}