The database automatically clears out idle connections that cause the connection to disconnect in the middleware connection pool

All databases are automatically cleared of idle connections that are timed out, because the database itself is a socket server, and it must periodically clear dead connections to keep them running for long periods of time.

After the database clears the idle connection, the con.connected inside the middleware connection pool is still equal to true, that is, in the middleware is unable to determine whether the connection pool connections have been cleared by the database.

In fact, all connections in the middleware connection pool must remain connected for 24 hours. So how to solve this contradiction?

The answer is to set a timer in the connection pool, periodically check each connection in the pool, and automatically disconnect and reconnect the connection when the free connection in the pool has exceeded half an hour.

{*******************************************************
Data Chi Chen
Each database has its own data pool, which is identified by a set of bill numbers
Update history:
Add by Cxg 2016-8-1 Add timer
Timestamp = 0, indicating that the connection is assigned, the allocated connection does not check for idle
Connections with more than 30 minutes of idle time are automatically disconnected and re-connected
*******************************************************}

Unit Untdbpool;

Interface

Uses
Classes, Syncobjs, Sysutils,
Dateutils, Untdb, Windows, Untglobal
, Vcl.extctrls
;

Type
Tdbpool = Class
Private

Fcs:tcriticalsection;
Flist:tlist;
Fcount:integer;
Fdatabaseparams:tdbparams;
faccountno:string; Set number of the bill
Procedure OnTimer (Sender:tobject);
Public
Ftimer:ttimer;

Constructor Create; overload;
destructor Destroy; Override
Procedure Init;
function Lock:tfrmdb;
Procedure Unlock (Value:tfrmdb);
function Newobj:tfrmdb;
Property Count:integer read Fcount default 0;
Property Databaseparams:tdbparams Read Fdatabaseparams
Write Fdatabaseparams;
Property accountno:string read Faccountno write Faccountno; Set number of the bill
End

Var
Dbpoollist:array of Tdbpool; Database Pool List

function Getdbpool (const accountno:string): Tdbpool;

Implementation

Uses Untlog;

function Getdbpool (const accountno:string): Tdbpool;
Var
I:integer;
Begin
Result: = nil;
If Accountno = "Then
Exit;
For I: = Low (Dbpoollist) to High (dbpoollist) does
Begin
If Dbpoollist[i].accountno = Accountno Then
Begin
Result: = Dbpoollist[i];
Exit
End
End
End

Constructor Tdbpool.create;
Begin
Flist: = tlist.create;
FCS: = tcriticalsection.create;
Ftimer: = Ttimer.create (nil);
ftimer.enabled: = False;
Ftimer.interval: = 1000 * 5; 5 sec
Ftimer.ontimer: = Self.ontimer;
End

destructor Tdbpool.destroy;
Begin
While Flist.count > 0 do
Begin
Tfrmdb (Flist[0]). Free;
Flist.delete (0);
End

Freeandnil (flist);
Freeandnil (FCS);
Freeandnil (Ftimer);

Inherited Destroy;
End

Procedure Tdbpool.init;
Var
Ldb:tfrmdb;
Begin
While Flist.count < poolparams.poolsize do
Begin
LDB: = NEWOBJ;
If LDB <> Nil Then
Begin
LDB. Connectdb;
Flist.add (LDB);
LDB. TimeStamp: = GetTickCount; Pool connection, start calculating idle time
End
End
End

function TDBPool.Lock:TfrmDB;
Begin
Fcs. Enter;
Try
If Flist.count > 0 Then
Begin
Result: = Tfrmdb (flist[0]);
If not result.connected then
Result.connectdb;
Flist.delete (0);
Result.timestamp: = 0; =0, indicating that the connection has been allocated and does not calculate idle time
End
Else
Result: = nil;
If Result = Nil Then
Begin
Result: = NEWOBJ;
If Result <> Nil Then
Begin
Result.connectdb;
Result.tag: = 5;
End
End
Finally
Fcs. Leave;
End
End

function TDBPool.NewObj:TfrmDB;
Begin
Result: = nil;
If Poolparams.maxvalue = 0 Then
Begin
Result: = Tfrmdb.create (nil);
Result.databaseparams: = Self.databaseparams;
Inc (Fcount);
End
else if (poolparams.maxvalue <> 0) and (Fcount < Poolparams.maxvalue)
Then
Begin
Result: = Tfrmdb.create (nil);
Result.databaseparams: = Self.databaseparams;
Inc (Fcount);
End
End

Procedure Tdbpool.ontimer (Sender:tobject);
Var
I:integer;
Begin
The database automatically disconnects a long-time idle data connection
Fcs. Enter;
Try
For I: = 0 to Flist.count-1 do
Begin
If Tfrmdb (Flist[i]). TimeStamp = 0 Then
Continue//Allocated connection, no idle time calculated
Else
Connection in pool, idle time >30 minutes, disconnect and reconnect
if ((Gettickcount-tfrmdb (flist[i)). TimeStamp) > (+ +) Then
Tfrmdb (Flist[i]). Connectdb;
End
Finally
Fcs. Leave;
End
End

Procedure Tdbpool.unlock (Value:tfrmdb);
Procedure _free;
Begin
Value.disconnectdb;
Freeandnil (Value);
Dec (Fcount);
Value.timestamp: = GetTickCount; Return the pool and start calculating the idle time of the connection
End

Begin
If Value = Nil Then
Exit
Fcs. Enter;
Try
If Value.tag = 5 Then
Begin
_free;
End
Else
Begin
If Flist.count < poolparams.poolsize Then
Begin
Flist.add (Value);
End
Else
_free;
End
Finally
Fcs. Leave;
End
End

End.

The database automatically clears out idle connections that cause the connection to disconnect in the middleware connection pool