how to avoid problems with the loss of connection to the database

Databases
1

Hello folks,
I need help about the following problem :
I’ve a mac desktop application which open a postgresql database on a server machine ( The database.connect is in app.open event ).
Then the application do some database operations and wait to do other database operations every 15 minutes.
If during this time the client loss the database connection all database operations fail (obviously).
My question is,
can I wrap all code like this? it’s enough ? :

if db.connect then
Dim ps3 As PostgreSQLPreparedStatement
ps3 = app.db.Prepare(“UPDATE ““tblViaggi”” SET data_invio_email = $1 WHERE riferimento_spedizione = $2 ;”)
ps3.Bind(0, 99999999) 'data_invio_email
ps3.Bind(1, riferimento) 'riferimento_spedizione
ps3.SQLExecute
end if

or I’ve to check after SQLExecute instruction the error :
“If app.db.error Then”
and then try to reconnect it ?

What is the best ? or other suggestions …

Thanks in advance

luciano

2

A (Re)Connect happens fast (in most cases). I’d recommend to first (Re)Connect and then perform the querrie.

3

Hi Sascha,
you mean that the code below should works ?

if db.connect then
Dim ps3 As PostgreSQLPreparedStatement
ps3 = app.db.Prepare(“UPDATE ““tblViaggi”” SET data_invio_email = $1 WHERE riferimento_spedizione = $2 ;”)
ps3.Bind(0, 99999999) 'data_invio_email
ps3.Bind(1, riferimento) 'riferimento_spedizione
ps3.SQLExecute
end if

Thanks

4

Yes.

So i do the following now:

  1. Connect
  2. If connected, perform querries
  3. If not connected x times in a row, report back an error
  4. If not connected, increase a connection fail counter and reconnect (Goto 1.) :wink:
5

I’ve done this with exceptions because

a) you check the connection and 1 millisec later you loose the connection.
b) if you get a permanent error you may have to do something different compared to simply connecting back.

For Valentina:

exception exc if exc isA VException then Globals.theErrorLog.DialogErrorProceed(app.kErrorValentina + " " + exc.Message) #if App.kMaxVersion = Globals.Version.Server then NotificationManager.post("LostConnection") globals.StopArchiving = true Return Nil end if theException = new ErrorException(exc, currentMethodName)

6

Thanks both.