There were some questions in Skype about handling disconnections from SQL back ends. While Clarion expects a database connection to stick around forever, this is not typical - and it certainly isnt how SQL back ends achieve their best performance.
Anyhow, disconnects happen, so how do you catch and cure them in your app without having the app close / halt?
While this isnt perfect, it’s something pratik and I tossed back and forth some years ago and had reasonable success with this code. Note: the template part of this is shown to illustrate which embed point this code should be used in (global file manager Throw, for each table).
#AT(%FileManagerCodeSection, ,'Throw','(USHORT ErrorNumber),BYTE'),PRIORITY(4500),DESCRIPTION('')
#CASE(%FILEDRIVER)
#OF('ODBC')
#OROF('SQLAnywhere')
#OROF('Oracle')
#OROF('MSSQL')
IF ERRORNUMBER = 9
SETCURSOR(Cursor:wait)
CLOSE(%FILE)
SHARE(%FILE)
SETCURSOR()
RETURN Level:Benign
ELSIF ERRORCODE() > 0 and ERRORCODE() <> 33
! use ODS to log errors here if you wish. ud.debug('detected error # ' & ERRORCODE() & ' FEC=' & FileErrorCode() & ' for file')
END
#END
#ENDAT
We also tinkered with prop:disconnect to try and clean up a timed out connection. We used that to formally close a table so that we could get a successful reopen after that. We had varying amounts of success with this - which was maybe 10 years ago.
Did anyone ever try using MAV Direct ODBC Driver from Ingasoft Plus? I believe one of the claims with the product/template was that it could handle SQL disconnects/reconnects in a graceful way. Ingasoft Plus discontinued the product in 2014 (either their relationship with the developer dissolved or the developer disappeared). If so, is there any newer alternative out there that exists? Handling SQL disconnects in a graceful way and removing the “everything as a cursor” pattern in Clarion are two subjects that keep me up at night.
Appreciate the code and having the subject brought up for discussion. This is a good example of an incremental improvement that SV could easily add but often seems to forget as they pursue the latest fashions.
I never tried MAV, mostly due to concerns that it was short lived. The only alternatives im aware of are creating a file manager mod that uses ODBC directly, or that uses NetTalk WebServer’s file management API.
Apologies for resurrecting this almost a year-old topic…
I’m developing a WebServer app using Capesoft’s NetTalk whose database is connected to MySQL using ODBC. Every now and then, my WebServer app encounters a sudden disconnect from the database that renders it unusable unless it’s restarted.
Can someone elaborate on how {PROP:Disconnect} works and where’s the best place to put it? I’ve been trying to solve this nasty ODBC problem for days.
Prop:Disconnect is used to try to close the file and kill the connection so that you can try to reopen the file and bring things back to life. I have seen inconsistent success with it.
From the help: PROP:Disconnect CLOSEs any open files in the target file’s database, then disconnects the application from the database.
PROP:Disconnect is neither thread nor program wide. Rather it is connection wide. So every file on every thread that uses the same connection (same OWNER attribute) is closed.
Another note from the MARS help (not related to ODBC driver): If all tables are closed and a PROP:Disconnect is issued successfully, you will need to SEND the /MULTIPLEACTIVERESULTSETS = TRUE again before the first table is opened.
I’ve found that it’s easier and more dependable to write a small monitor app which runs as a service. It’s only function is to test the webserver’s ability to access SQL and if the test fails, simply kill and restart the NTWS service.
Did you use the {PROP:Disconnect} that was suggested by @mriffey? If so, can you give us some hints on where to place it? Right now, the only problem we have with the application we’re developing are the random disconnects.
However, it’s been less than dependable because the RTL doesnt necessarily clean everything up well enough to restart file connections from scratch. Sometimes it works, sometimes it doesnt.
Sorry, my answer was unclear.
I meant that I use the method to have a separate program monitor the service and make sure it is responsive.
If it becomes unresponsive then kill and restart the service.
In my case, the service monitor is the program that is running as the actual Windows Service. It then uses OddJob to spawn the Nettalk service program as a child process.
It can then detect if the Nettalk service crashes and restarts it.
The monitor program also tests an end point for the Nettalk service every few seconds and it if doesn’t get a response after a certain threshold it kills the child process and starts a new one.
We don’t use PROP:Disconnect to restore MySQL connections, instead we have a template which puts following code into each Hide:Access:FileName.FunctionDone method:
PUSHERRORS()
CASE ERRORCODE()
OF FileSystemErr
#IF (%iaTracer)
%iaTracer(SELF.GetName() &'::FunctionDone('& opCode &') FILEERROR '& FILEERROR() &' ['& FILEERRORCODE() &']')
#ENDIF
IF opCode <> DriverOp:OPEN ! protect against recursion
!-- close
LOOP WHILE SELF.Opened
SELF.Close()
END
!-- reopen
IF SELF.UseFile() = Level:Benign
POPERRORS()
RETURN TRUE
END
ELSE
#IF (%iaTracer)
%iaTracer(SELF.GetName() &'::FunctionDone(DriverOp:OPEN) cannot be processed')
#ENDIF
END
END
POPERRORS()
That’s basically what I do. When a hang or other failure is detected, the monitor app issues a service stop. If that fails, it does a taskkill. In the next cycle (a few seconds), it will see that the service is stopped, so it’ll restart it.
Not really the kind of solution an interactive desktop app can use:)
#AT(%FileManagerCodeSection, ,'Throw','(USHORT ErrorNumber),BYTE'),PRIORITY(4500),DESCRIPTION('')
#CASE(%FILEDRIVER)
#OF('ODBC')
#OROF('SQLAnywhere')
#OROF('Oracle')
#OROF('MSSQL')
IF ERRORNUMBER = 9
SETCURSOR(Cursor:wait)
CLOSE(%FILE)
SHARE(%FILE)
SETCURSOR()
RETURN Level:Benign
ELSIF ERRORCODE() > 0 and ERRORCODE() <> 33
! use ODS to log errors here if you wish. ud.debug('detected error # ' & ERRORCODE() & ' FEC=' & FileErrorCode() & ' for file')
END
#END
#ENDAT
In conjunction with the code by @mriffey, we have found that using the AUTO_RECONNECT property for the MySQL connection string solves the disconnection problems we’re having with our application.
The only other place I had seen this setting was from a 2011 post by Markus Steinwender on the SV newsgroups. I sent him an email and asked about it. His reply:
I use a persistent SSH Tunnel from a Server with TSPLUS to connect to mysql server and try to keep the tunnel open and also the application tries to ping the server every 30 seconds to keep the connection open. This works most of the time.
