Behaviour of ResultSet after connection got corrupted.

Question

Suppose I am making jdbc call and I had fetched data from db to resultset. But due to some network issue I lost my connection with db. (Connection, statement and resultset is not closed in DB). So can I still able to iterate resultset ?

Answer 1

It am pretty sure it depends entirely on your JDBC driver. It may have buffered all the results before the connection was lost. It may have only buffered the next 10 results before the connection was lost. Even if all results were buffered, the driver itself may start throwing exceptions before you can finish iterating over the buffered results.

Personally, I would assume that any behavior after a network interruption is considered undefined.

Answer 2

Typically any kind of "entire result set" object will not be fully constructed until the full row set has been received successfully. If for example you have a property like NumberRecordsAffected on the object then it must have received all rows.

However an enumerable object like GetFirstRow/GetNextRow typically will bring down a chunk of rows at a time, so you wouldn't know the connection died until the current buffer was exhausted (if it buffers any rows) and it tries to fetch the next row from the db.

In either case I would expect an exception to be thrown, but IANAJDBCD (I am not a jdbc developer).

Answer 3

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.