This thread does seem to be going off at a tangent to the original post.
What you need to decide is what are you trying to achieve in terms of recovery from database connection loss. Broadly speaking, there are three levels of response to this event:
1. Let the user work it out.
This is a minimal approach and ok for basic applications and when connection loss is rare. All you need to do is to report the exception and exit the program. The user is then responsible for restarting the program, working out what data was lost, if any, and restoring any important changes. The users task is simplified if updates are committed soon after they are made, even auto-committed as then they only have to look at the last thing they did. Caching too many updates before applying/committing them just makes the problem worse.
2. Assisted Restart
On connection loss, your program closes down the connection (usually a forced disconnect) in order to restore itself to a known state. It then re-opens the connection, reopens the datasets that were open when the connection was lost and tells the user that some data may have been lost. Again, it is the user’s responsibility to work out what was lost, etc.
3. Journaling
This is where you pre-empt the problem by recording, in some journal file, every SQL Update, Insert and Delete statement, the transaction they were executed under and when each transaction starts and is either committed or rolled back. The journal file can be no more than a text file with one statement per line,
When a connection is lost, your program does a forced disconnect and re-opens the connection, It then looks in the journal for any uncommitted transactions and, for each one, starts a new transaction with the same parameters, and executes the recorded SQL statements in the same order as they were journaled and under that transaction. The datasets are then reopened and the user should see exactly the same state as when the connection was lost. Note that the recovery transactions are not committed. That is because the view is restored to exactly the same state and it is up to the user to decide whether to commit or rollback.
The problem with the above comes if the connection is lost when committing a transaction, You then do not know if it was committed or is still in limbo. You need to save some information server side with each transactions that allows a simple way of determining whether the commit succeeded. This could be as simple as a last transaction identifier recorded by an update statement that is executed under the transaction.
IBX does not include an actual journaling capability – although this is an idea for a future release. However, it does include TISQLMonitor. This allows you to snoop on every transaction and sql statement and use this to record to a journal file.