Integrated reconnect to database

[invantive]

We connect to approximately 100 cloud platforms through LibreOffice using our own SQL-engine (Invantive UniversalSQL) over the TDS protocol and the standard ODBC SQL Server driver.

LibreOffice Base can loose a connection, such as when the network changes.

On programs such as pgAdmin or SQL Server Management Studio, the database connection is automatically re-established by going through the logon procedure automatically. On LibreOffice Base, this requires restart of the application logic.

Is there any way available or anything planned to automatically reconnect?

I have found some similar questions, but none provides an error (please note that UniversalSQL automatically reconnects to the backing platforms, it really concerns solely LibreOffice Base <–> UniversalSQL):

• reconnect base to db after failed connection
• LibreOffice Base - connection times out after 60 seconds, how do you keep a connection alive/reconnect without restarting ?

[invantive]

Other similar previous topics:

• Re-establishing connections to remote databases after timeouts
• How to make LibreOffice Base reconnect to a database

[RobertG]

I would try something like this:

GLOBAL boStop AS BOOLEAN

SUB Reconnect
 DIM oDatasource AS OBJECT
 DIM oConnection AS OBJECT
 DIM oSQL_Command AS OBJECT
 boStop = false
 DO
 WAIT 30000 'Time in in Milliseconds
 oDatasource = thisDatabaseDocument.CurrentController
 oConnection = oDatasource.ActiveConnection()
 oSQL_Command = oConnection.createStatement()
 oSQL_Command.executeQuery("SELECT NOW()")
 LOOP WHILE boStop = false
END SUB

SUB StopConnect
 boStop = true
END SUB

This will help reconnecting ever 30 seconds. Reconnecting could be stopped with StopReconnect. If procedure Reconnect will be started with opening of the database file you have to fill password dialog during the first 30 seconds.

[Villeroy]

This will loop forever. I would simply restart the office.

[invantive]

Thank you! It seems however a little laborous, especially when each user that create an app has to do this.

Is there no automatic way that makes sure that in all occassions the connection is re-established? Or would that require an enhancement/fix to Base?

[Villeroy]

@invantive

Thank you! It seems however a little laborous, especially when each user that create an app has to do this.

It is YOUR app, so YOU have to take the necessary precautions depending on YOUR database engine. I would simply tell “my users” to restart the thing.
This draft may be a bit more useful. Tested with an availlable connection to a HSQL2 server on a local network.

Sub reconnect()
ctrl = thisDatabaseDocument.CurrentController
conn = ctrl.ActiveConnection
on error goto xErr
do while isNull(conn)
	ctrl.connect()
	wait 3000
	conn = ctrl.ActiveConnection
loop
Msgbox "database is connected",MB_ICONINFORMATION,"macro reconnect"
exit sub
xErr:
e = err()
if e = 1 then
	x = Msgbox("Database not reachable. Retry?", MB_ICONQUESTION + MB_YESNO, "macro reconnect")
	if x = IDYES then 
		resume
	endif
else
    error err
endif
End Sub