Silent Warrior Silent Warrior - 1 year ago 61
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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download