asked on

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...

Ivanov_G

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.

joncmora

ASKER

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!!!

Ivanov_G

Well, in this case you can use OnBeforeOpen event of the datasets...

joncmora

ASKER

OnBeforeOpen/OnAfterOpen doesn't fire on non-SELECT queries.

Ivanov_G

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.

joncmora

ASKER

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?

Ivanov_G

if you say that TSQLConnection.KeepConnection doen't work as designed ... I have no other ideas.

joncmora

ASKER

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.

ASKER CERTIFIED SOLUTION

PAQ_Man

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial