Exhausted ResultSet

[Babu]

Hi,
I'm getting wierd error as shown below when I try to access Oracle 9i server
using Oracle Thin driver in Weblogic 7.0 SP4. I'm closing resultset, preparedstatement
and connection in the order as above. But this is not happening either in Weblogic
6.1 or Weblogic 8.1.
Any help is greatly appreciated.

Start server side stack trace:
java.sql.SQLException: Exhausted Resultset
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)
at oracle.jdbc.driver.OracleStatement.prepare_for_new_get(OracleStatemen
t.java:3365)
at oracle.jdbc.driver.OracleStatement.getIntValue(OracleStatement.java:4
440)
at oracle.jdbc.driver.OracleResultSetImpl.getInt(OracleResultSetImpl.jav
a:517)
at oracle.jdbc.driver.OracleResultSet.getInt(OracleResultSet.java:1528)
at weblogic.jdbc.pool.ResultSet.getInt(ResultSet.java:325)

[Joe Weinstein]

Babu wrote:

> Hi,
> I'm getting wierd error as shown below when I try to access Oracle 9i server
> using Oracle Thin driver in Weblogic 7.0 SP4. I'm closing resultset, preparedstatement
> and connection in the order as above. But this is not happening either in Weblogic
> 6.1 or Weblogic 8.1.
> Any help is greatly appreciated.

As I posted for your other question, I suspect the thin driver version. You should
get the latest from Oracle and get it in front of the classpath the server is getting
from the startup script.
Joe

[Slava Imeshev]

Hi,

That may be a result of a race condition or it may happen because
you are not checking result returned by rs.next() ant try to access
empty result set.

Could you show us fragment of the code where you open/access/close the RS?

Slava

"Babu" <babu.ja...@qwest.com> wrote in message news:3fb00834$1...@newsgroups.bea.com...

[Babu]

Slava,
here is the snippet of the code:

try {
ps = con.prepareStatement(stmt);
ps.executeQuery();
rs = ps.getResultSet();
System.out.println(" Retrieved the results for component types");
while (rs.next()) {
System.out.println(" getting the result ");
Integer id = new Integer(rs.getInt(2));
try {
componentClass = Class.forName(rs.getString(1));
} catch (ClassNotFoundException cfe) {
componentClass = null;
}
System.out.println(" Component Class loaded ");
compTypeIds.put(componentClass,id);
compTypeIdsToClasses.put(id, componentClass);
//DebugLog.put("Called compTypeIds.put(" + componentClass + ",
" + rs.getInt(2)+ ").");
System.out.println(" Iteration is over ");
}
System.out.println(" Result Set iteration is completed ");
} catch (SQLException sqe) {
//DebugLog.put(" ");
//DebugLog.put("Caught an SQLException while populating the component_type_ids
in PricingConstants.");
//DebugLog.put(sqe.getMessage());
//DebugLog.put("The Error Code is " + String.valueOf(sqe.getErrorCode()));
//DebugLog.put("The SQL State Code is " + String.valueOf(sqe.getSQLState()));
sqe.printStackTrace();
throw new PricingException("Caught an SQLException while populating
the component_type_ids in PricingConstants.");
} finally {
DBUtil.cleanup(null, ps, rs);
}

I don't see "Result Set iteration is completed " in the log. So it looks like
its creating issue before that itself.

Thanks anyway,
-Babu

[Slava Imeshev]

One more question: where do you define rs and ps? In the method or in the class?

Slava

"Babu" <babu.j...@qwest.com> wrote in message news:3fb015e2$3...@newsgroups.bea.com...

[Joe Weinstein]

Babu wrote:

> Slava,
> here is the snippet of the code:
>
> try {
> ps = con.prepareStatement(stmt);
> ps.executeQuery();
> rs = ps.getResultSet();

Hi. I see a problem with this, and I think it is relevant. You should
change the above code to:
ps = con.prepareStatement(stmt);
rs = ps.executeQuery();

The issue is that the executeQuery() call *does* return a result set,
which your code is currently ignoring. If GC() collects the never-retained
result set, it is probably causing the result set to be closed, which
closes the underlying DBMS result set.
Joe

[Babu]

Outside try block.

Thanks,

[Deyan D. Bektchiev]

Hi Joe,
This seems to be another gray place in the spec since the code should be
legal and the reference to the result set should only be lost during the
small fraction of time between the ps.executeQuery() and
ps.getResultSet() but the Weblogic driver probably returns a different
wrapper ResultSet for each call and therefore the first one is GCed.

Can't the Weblogic driver return the same instance and then it would not
be picked up by the GC?

Then of course until the prepared statement itself goes out of scope the
ResultSet wrapper would not be GCed... so it's a choice that somebody
had to make I guess.

--dejan

[Babu]

Hello Joe, Slava and Deyan,
It looks like Joe is correct. I changed the code to take the result
set when i execute the query itself, that problem got solved . Thank you, very
much for all the prompt help this forum brings in.

Thanks,
-Babu

[Joe Weinstein]

Babu wrote:

> Hello Joe, Slava and Deyan,
> It looks like Joe is correct. I changed the code to take the result
> set when i execute the query itself, that problem got solved . Thank you, very
> much for all the prompt help this forum brings in.
>
> Thanks,
> -Babu

Glad to help.
Joe

[Joe Weinstein]

Deyan, you are correct though. getResultSet() *should* return the
exact same object that the immediately prior executeQuery() had
returned. This can be fixed by us.
Joe

[Babu]

Thank you again, Joe.

-Babu

[Deyan D. Bektchiev]

Joe,

When I initially started writing the response to the message I was
almost certain that getResultSet() *should* return the exact same object
that a prior call to executeQuery() would return but I looked at the
spec and I did not find any strong requirement to do so so I decided it
was left up to the vendor to decide what to do.

I read the API doc just now and the only very vague wording that implies
this it that both methods (executeQuery() and getResultSet()) would
return *the* result as a ResultSet object but there is no requirement
that it be the same both times.

And as I mentioned before, if you return the same ResultSet instance
then GC would not close it so it could potentially live longer if the
application does not explicitly close it.

So I still think that it is a vendor choice to decide how to implement
the behavior.

--dejan