The SQL drivers have the ability to automatically reconnect after a lost connection.
A lost connection situation happens if for some reason the connection between a workstation and the database server has been lost.
A lost connection can be caused for several reasons:
Network hick-ups. This is more likely on WAN connections and Wifi networks.
Sometimes connections are lost after a certain period of inactivity.
The database server can be temporary unavailable because of maintenance or backups.
The drivers will try to re-connect to the database server when a connection lost error has been received.
If the reconnect succeeds (the connection is re-established), the operation that generated the lost connection error (for example a Find operation), will be retried.
If the reconnect does not succeed (unable to connect to the database server) a ‘connection lost’ error will be generated.
After a successful reconnection inside a Find operation, it will appear to the program as if nothing has happened. The program has executed a successful Find operation and is unaware a re-connect has taken place. Note that this will only occur if the first re-connect attempt is successful.
If the first reconnect attempt fails, the Find operation will generate a ‘connection lost’ error. The next Find operation from the program will again try to reconnect.
After receiving a connection lost error, the driver will not try to reconnect when it is inside a transaction. Instead it will generate ’lost connection’ error. This will abort and roll back the transaction. The first operation after the transaction will try to reconnect, and, if successful, the program can continue.
Re-executing the operation after a successful reconnect will only be done on Find and Open operations.
Save and Delete operations always take place inside a transaction and will not try to reconnected, but generate an error that will cause a transaction abort.
Auto Reconnect can be configured in the driver configuration file (MSSQLDRV.INT, DB2_DRV.INT or ODBC_DRV.INT) with the following keyword:
AUTO_RECONNECT 1
The default value is 1 (Auto-Reconnect On)
The driver will generate the following error when the connection to the database was lost:
12343 DFODBCERR_DATABASECONNECTIONLOST
Connection to database lost
The driver will translate the database native connection lost errors to this DataFlex error code.
For the SQL Server and DB2 drivers, the native connection lost errors are pre-configured and don't have to be setup.
The ODBC driver can connect to different database back ends and the connection lost state(s) must be specified in the database specific configuration file like Oracle.int, Mysql.int, Access.int, etc.
The connection lost state can be specified with the CONNECTION_LOST_STATE keyword. If there are multiple connection lost states, the CONNECTION_LOST_STATE keyword can be specified multiple times.
Example (MySQL.INT)
; Connection_Lost_State
; The native error that indicates a lost connection.
; This error will translate to a DF lost connection error.
Connection_Lost_State 08S01