Silent Warrior Silent Warrior - 12 days ago 5
SQL Question

Behaviour of ResultSet after connection got corrupted

Suppose I'm making a JDBC call and I had fetched data from a database to a

ResultSet
. But due to a network issue I lost my connection with the database.

(
Connection
,
Statement
and
ResultSet
is not closed in DB). Will I still be able to iterate over the
ResultSet
?

Answer

Even if you could, you shouldn't unless you are coding against a very specific jdbc driver. In some cases, the result set will not be constructed at all. In others (Oracle IIRC), you could configure it so that it only fetches a give number of rows out of the total.

However, in general, if you lose the connection, you have more things to worry about than wondering if you can iterate over a partially fetched resulst set object. In such cases, the rule of thumb is

  1. to assume the worst;
  2. attempt to close the result set, statement and connection; even if the physical connection is lost, there might be resources like memory and file handles on the calling side that need to be disposed off;
  3. if possible, attempt to get a new connection (either a new physical one or from a connection pool) and start over.

Also, as a rule of thumb, you should not worry about partial failures when executing statements within a transaction. Discard and retry fresh.

In some rare cases the DB can send you a vendor specific code (SQLException.getErrorCode()) that can tell you whether the operation can be retried. Oracle has some specific codes (don't remember them) for cases when you do an insert and a unique constrain has been violated. Sometimes such failed operations can be retried, but that's vendor and business specific.

In general, just dump the mauled result set and start over.