TSQLConnection.MaxStmtsPerConn and MySQL

I just built an automated time keeping system (fingerprint scanner)
and it should be UP 24 hours. Every morning, employees complain about
a "DBExpress Error" when they login. The problem is that my database connection gets dropped, even if I have a
timer (every 2.5 minutes) that syncs the time with the db server using "SELECT
CURRENT_TIMESTAMP". BTW, I'm using DBExpress/MySQL 4.0.21 (and Borland's dbexpmysql.dll).

After some tests, I encountered this read-only property
TSQLConnection.MaxStmtsPerConn which always equals to 1 (I tested FireBird, it's 0 [unlimited]). So every
dataset component connected to TSQLConnection gets its own copy of
TSQLConnection through TSQLConnection.CloneConnection. And the only
connection that never gets dropped is the one that gets the server's time.

How can I set this up? I wanted all my datasets to use only one connection.  
If it's on the TSQLConnection.Params, what's the Key/Value?
Does dbexpmysql.dll supports only one active statement per connection?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

For setting the time on a LAN, you should user some NTP (Network Time Protocol) implementation. Indy components offer this - TIdTimeServer and TidTime.

MaxStmtsPerConn is somehow tied with AutoClone property. The to play combination with this.
joncmoraAuthor Commented:
Never mind the time sync, its working anyway...

What I wanted to do is to keep my connections (and its clones) alive at all times. I already dropped a TTimer (code below) and set the interval to 2 minutes. But it just didn't work.

procedure TDataMod.TimerTimer(Sender: TObject);
  I: Integer;
  for I := 0 to ComponentCount - 1 do
    if Components[I] is TSQLDataSet then
      with TSQLDataSet(Components[I]) do
        if not SQLConnection.Connected then
          SQLConnection.Connected := True;

I have one "TSQLConnection" and several "TSQLDataset"s all set to CommandType := ctQuery. Most of these datasets are executed only when employees login/logout. And they become idle from around 7PM to 7AM so the associated (cloned) connection gets dropped.

Or maybe there is a way to make TSQLConnection/MySQL accomodate more connections. That would be very great!!!
Well, in this case you can use OnBeforeOpen event of the datasets...
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

joncmoraAuthor Commented:
OnBeforeOpen/OnAfterOpen doesn't fire on non-SELECT queries.
Yes, because you execute the SELECT statements with Open and Non-SELECT with ExecSQL. But I think this is the way - suing the events. On the TSQLDataSet you have OnBeforeEdit, OnBeforeInsert ..  This is the place you have to check for active connection.
joncmoraAuthor Commented:
I use a separate dataset instance for every table action (insert/select/update/delete) so OnBeforeX/OnAfterX will work only on SELECTs. And TSQLDataset has no editing support.

I don't use TClientDataset/TSimpleDataset either. I use an automatically generated decendant of TList to store the retreived records. So it's very lightweight.

I think it's not a good idea to check for the connection status for each execution. Isn't there a way to keep the connection alive like FTPs NOOP?
if you say that TSQLConnection.KeepConnection doen't work as designed ... I have no other ideas.
joncmoraAuthor Commented:
Sorry, I don't know what to do. Nobody provided a working solution. I've come to accept that Borland's driver only support 1 active statement per connection. Anyway, I switched to CoreLabs' driver. I would like a points refund.
PAQed with points refunded (500)

Community Support Moderator

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.