5

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!

Jerry Dodge
  • 26,858
  • 31
  • 155
  • 327
t1f
  • 3,021
  • 3
  • 31
  • 61
  • 1
    "ping" the database every 10 sec or so. – kobik Nov 15 '16 at 17:35
  • @kobik Could you provide a code example for this? I did come across mysql_ping in googling this and I thought about adding that to a TTimer but haven't yet found a code example on how to do this, make it an answer if you want so I can accept, I'd appreciate it, thanks! – t1f Nov 15 '16 at 18:20
  • 1
    Maybe @Kobik is thinking of doing a "SELECT 1" (which involves little server activity or network traffic). – MartynA Nov 15 '16 at 18:29
  • @MartynA - wouldn't that interfere with the select statement i already have set up in my dataset? – t1f Nov 15 '16 at 18:34
  • It needn't - you could use a separate query component to do it. – MartynA Nov 15 '16 at 18:35
  • 1
    You can have numerous queries sharing the same connection. Just create another query for the sole purpose of periodic activity. – Jerry Dodge Nov 15 '16 at 18:36
  • Right, thanks guys :) – t1f Nov 15 '16 at 18:42
  • @MartynA, yes was basically what I had in mind. in ADO/SQL Server you could simply do: `TADOConnection.Execute(' ')` (space as command). maybe MySQL has similar. with `TSQLConnection` you could also do `TSQLConnection.Exceute` with the query you suggested. – kobik Nov 15 '16 at 19:04
  • 1
    I think a `TSQLConnection.Exceute('DO 0', nil)` in a timer will do the job. – kobik Nov 15 '16 at 19:20
  • @kobik Indeed, just found the property description - it keeps a connection active when no dataset is connected/active, that's about it. Thanks for the answers :) – t1f Nov 15 '16 at 19:22

1 Answers1

8

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;
t1f
  • 3,021
  • 3
  • 31
  • 61
kobik
  • 21,001
  • 4
  • 61
  • 121
  • Thanks, it works - just tested it. Is there any difference between DO 1 and DO 0 ? I found a nice discussion that uses DO 1 as an example and argues it's better (marginally faster) than a select 1 or a mysql_ping - any thoughts? – t1f Nov 15 '16 at 21:12
  • 2
    no difference between `DO 0` and `DO 1` which evaluates an expression without returning a value as opposed to `SELECT 0` or `SELECT 1` which return a result set. the differences in network traffic however are neglectable. see also: http://stackoverflow.com/q/2546868/937125 – kobik Nov 16 '16 at 07:36
  • I see, yup, that's what I was reading, ok .. thanks for the answer : ) – t1f Nov 16 '16 at 09:52
  • 1
    Typo in `Exceute` – Peter Chaula Jun 14 '19 at 10:37