Search code examples
mysqlsqldelphidbexpress

Keeping connection alive - mysql

I have set up my delphi application that I created with a button named Connect. Once I click it it executes the following code:

begin
    someConnection.Connected:=true;
    somecomenziDataSet.Active:=true;
end;

Similarly I have a button for Disconnect purposes which does the same thing but with =false;

My problem is the database I'm connecting to is hosted on a shared hosting account and the mysql server has wait_timeout variable set to 60 seconds, interactive_timeout is set to 30 seconds. Naturally, this disconnects me if I don't use my app for 60 seconds.

Is there any way to keep that connection alive?

Hosting company won't change the setting so i'm stuck with it.

I'm using RAD Studio 10 Seattle, dbexpress components, TSQLConnection and my database is mysql

Please do let me know in a comment if I left any necessary info out, thanks!

Solution

  • You can use a TTimer to periodically "ping" the database (e.g. interval of 10-20 seconds given a 60 sec timeout):

    procedure TMyDataModule.ConnectionPingTimer(Sender: TObject);
begin
  if not MySQLConnection.InTransaction then
    MySQLConnection.Execute('DO 0', nil); // or "SELECT 1" or whatever is cheapest
end;