Delphi Datasnap – Memory Leak and it’s workaround.

Datasnap – Fetching data using SQL from the server…. WITHOUT crashing the server!

 

For the past few weeks, I had been noticing a snag in my application that kep coming up randomly. This was to do with calling the a particular function that was responsible for taking an Sql as a string and return a DatasetProvider name so that the data could be accessed on the client side’s client dataset.

 

The function was written something like this on the Datasnap server

 

function GetDataForClient(SQL: string): stirng;

var

qryNew: TADOQuery;

dspNew: TDatasetProvider;

begin

qryNew := TADOQuery.Create(nil);

qryNew.Connection := SomeADOConnectionObject;

qryNew.SQL.Text := SQL;

qryNew.Name := ‘qry’+IntToStr(UniqueID);

 

dspNew := TDatasetProvider.Create(nil);

dspNew.Name := ‘dsp’+IntToStr(UniqueID);

dspNew.Dataset := qryNew;

 

qryNew.Open;

 

Result := dspNew.Name;

end;

 

So, basically, as you may have already figured out, the function is meant to be called on the client side as follows (assuming all other properties were set correctly and DS server is running):

 

procedure ClientFetchData;

begin

ClientDataset.ProviderName := DatasnapServerMethods.GetDataForClient(‘select * from employees;’);

ClientDataset.Open;

end;

 

 

The above code worked quite well for… until sometime… as the application scaled and the function kept being calling more often. Randomly, the application would throw an exception of invalid memory.

 

I tried several things after that, such as releasing the object after the data was fetched at the client side, running the query in a thread and then freeing the thread’s objects and the thread itself. But for some reason, the error wouldn’t go away, though the occurrence of it had reduced to only once or twice a day compared to the previous occurrence of every other hour.

 

Anyhow, this wasn’t something I could ignore for a long time so I decided to rough it out and finally, I found a work around that worked beautifully…. Dataset Pool!

 

What I did was create a pool of 20 queries objects and it’s dataset providers when the DS server session was created and destroy them when the session was destroyed. I also maintained the last accessed query/provider pair so as to redirect the next call to the next object in the pool. The moment I reached the last object, I reset the count to access the first object of the pool.

 

The basic reason to do that was to ensure that I am not creating any objects dynamically on the call of the function (as mentioned in GetDataForClient).

 

Here’s the implementation of the functionaility.

 

TDataPair = class

public

Query: TADOQuery;

Provider: TDataSetProvider;

end;

 

The basic class that holds the pair of query/dataset.

 

TServerMethods2 = class(TDSServerModule)

cnt1: TADOConnection;

procedure DSServerModuleCreate(Sender: TObject);

procedure DSServerModuleDestroy(Sender: TObject);

private

{ Private declarations }

FPoolSize: Integer; // the pool size

FDataPool: TStringList; // the stirng list object that which acts as the pool

FLastObjIndex: Integer; // var that maintains the last accessed object from the pool

public

{ Public declarations }

function GetData(SQL: string): Integer;

end;

 

procedure TServerMethods2.DSServerModuleCreate(Sender: TObject);

var

I: Integer;

DP: TDataPair;

begin

FLastObjIndex := -1;

FPoolSize := 20;

FDataPool := TStringList.Create;

for I := 0 to FPoolSize – 1 do

begin

DP := TDataPair.Create;

 

DP.Query := TADOQuery.Create(Self);

DP.Query.Name := ‘qry’+IntToStr(I);

DP.Query.ConnectionString := ‘<<some valid connection string>>’;

 

DP.Provider := TDataSetProvider.Create(Self);

DP.Provider.Name := ‘dsp’+IntToStr(I);

DP.Provider.DataSet := DP.Query;

 

FDataPool.AddObject(IntToStr(I), DP);

end;

 

end;

 

procedure TServerMethods2.DSServerModuleDestroy(Sender: TObject);

var

I: Integer;

DP: TDataPair;

begin

for I := 0 to FPoolSize – 1 do

begin

DP := (FDataPool.Objects[I] as TDataPair);

DP.Query.Close;

DP.Query.Free;

DP.Provider.Free;

DP.Free;

end;

FDataPool.Free;

end;

 

function TServerMethods2.GetData(SQL: string): Integer;

var

DP: TDataPair;

begin

if FLastObjIndex = 19 then

FLastObjIndex := 0

else

Inc(FLastObjIndex);

 

DP := (FDataPool.Objects[FLastObjIndex] as TDataPair);

DP.Query.Close;

DP.Query.SQL.Text := SQL;

DP.Query.Open;

 

Result := FLastObjIndex;

end;

 

 

On the client side, this would what the call would look like:

 

procedure TForm7.btn1Click(Sender: TObject);

begin

cds1.Close;

cds1.ProviderName := ‘dsp’ + IntToStr(Server.GetData(‘select * from employees;’));

cds1.Open;

end;

 

The above implementation does exactly what the GetDataForClient function did earlier except that it doesn’t create any new objects on the server. Plus, it resolved the access violation exception.

 

HTH.

Happy coding!