Re-establishing connections to remote databases after timeouts

[CandidoBandido]

Dear All

I have a BASE application that addresses a remote PostgresSQL table where most of my users will be in Windows; I have set the timeout parameters to the maximum.

Unfortunately my users will still occasionally go and make a cup of tea and/or chat and leave the application running. What I wish to do is to be able to test the connection and if timed out reestablish a connection without having to close the application and reboot the device. I will test before saving data or moving from record to record.

I have looked at [forum entry 897] but see no resolution. Has anybody else found a solution to this problem?

The system reports as being connected even after timeout. How do I detect if the DB has been timed out? After timeout, do I have to close the current connection and re-establish a new connection?

Any help gratefully received!

Best wishes,

CB

[RobertG]

If AutoReconnect and something like SET SESSION wait_timeout=600; (MySQL/MariaDB) wont help:

GLOBAL boStop AS BOOLEAN

SUB Reconnect
   DIM oDatasource AS OBJECT, oConnection AS OBJECT, oSQL_Command AS OBJECT
   boStop = false
   DO
     WAIT 30000 'Time 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

Will automatically connect to send a query every 30 seconds.
“Reconnect” will start it, “StopConnect” will end the procedure.

[CandidoBandido]

Thank you, RobertG!

That appears to be working as expected. I entered the reconnect call inside the loop that checks to see if my form is open (and therefore needs the connection), as in:

Dim StartTime as long
Dim EndTime as long

… (hide application window, log in the user, open the form …) …

StartTime = Timer

do while IsOpenForm(“MyFormName”)
EndTime=Timer

 wait 20
 
 If EndTime - StartTime > 30  then '30 seconds has passed...
    StartTime=EndTime              'reset the times to be the same...
    Reconnect
 end if
 
 appWindow.setVisible(False)    'the application window can suddenly reappear ....

loop
…
Have a great Xmas!

Best wishes,

CB