TAdoConnection vs KeepConnection

[rcloutie]

Hi there,

Since few months, we have some problems with our advantage database server (need to be rebooted every 2 weeks or so), which is accessed from a linked server of SQL Server 2005. We think that maybe there's a property which cause connections to duplicate. Actually, there's only one connection (defined in a datamodule) and here's some code to launch a query:

var qry:     TAdoQuery;
(dtm.cntSQL: TAdoConnection)

qry.Connection := dtm.cntSQL;
     if not dtm.cntSQL.Connected then
        dtm.cntSQL.Open(sLogin,sPwd);
     qry.Close;
     qry.Sql.Clear;
     qry.Sql.Add(sSQL);
     qry.Open;
     qry.First;
qry.Connection := nil;
dtm.cnSQL.Close

Is code above makes sense? Any missing / extra code?
Is last line (connection close) necessary considering the fact that property KeepConnection of TAdoConnection is set to True ?

Thanks for help,

Rej Cloutier

 

[DjangMan]

Once you call qry.First; why are you dropping the connection property? Are you creating a disconnected ADO recordset?

 

[rcloutie]

Hi DjangMan,

Since I do not have any databound controls, disconnected ADO recordset is possible. But in fact, last two lines:

qry.Connection := nil;
dtm.cnSQL.Close

are not implemented yet because I considered that using KeepConnection active kept closing connection ignored.
Am I right?
1. Using KeepConnection=True ignores all connection.close statement?
2. Using KeepConnection=False disconnects almost every time a query is initialized (that is, setting the connection property, flag IsConnected=True; but when setting SQL string, flag IsConnected=False?);

I prefer to use default settings (KeepConnection=True) since it makes faster apps and less network traffic. But the trailing connections lost is a bad side effect that I need to solve. Is it possible that KeepConnection works well on a primary database server but for a linked server, there's always new (duplicated) connection each time a query is launched?

Thanks in advance,

Rej Cloutier

 

[DjangMan]

I can't speak for the mechanics of our the KeepConnection works. My understanding is that KeepConnection will keep the connection open when there are no open requests (open queries, tables, etc.). Once the last request is closed KeepConnection now comes into play. When set to true, if there are no open requests the connection stays open and if false, the connection is closed.

If you explicitly call .close I would expect the connection to heed that direct request.