• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 797
  • Last Modified:

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?

Thanks...
0
joncmora
Asked:
joncmora
  • 4
  • 4
1 Solution
 
Ivanov_GCommented:
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.
0
 
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);
var
  I: Integer;
begin
  for I := 0 to ComponentCount - 1 do
    if Components[I] is TSQLDataSet then
    begin
      with TSQLDataSet(Components[I]) do
        if not SQLConnection.Connected then
          SQLConnection.Connected := True;
    end;
end;

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!!!
0
 
Ivanov_GCommented:
Well, in this case you can use OnBeforeOpen event of the datasets...
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
joncmoraAuthor Commented:
OnBeforeOpen/OnAfterOpen doesn't fire on non-SELECT queries.
0
 
Ivanov_GCommented:
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.
0
 
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?
0
 
Ivanov_GCommented:
if you say that TSQLConnection.KeepConnection doen't work as designed ... I have no other ideas.
0
 
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.
0
 
PAQ_ManCommented:
PAQed with points refunded (500)

PAQ_Man
Community Support Moderator
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now