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;


qryNew: TADOQuery;

dspNew: TDatasetProvider;


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;




Result := dspNew.Name;



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;


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





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


Query: TADOQuery;

Provider: TDataSetProvider;



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


TServerMethods2 = class(TDSServerModule)

cnt1: TADOConnection;

procedure DSServerModuleCreate(Sender: TObject);

procedure DSServerModuleDestroy(Sender: TObject);


{ 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 declarations }

function GetData(SQL: string): Integer;



procedure TServerMethods2.DSServerModuleCreate(Sender: TObject);


I: Integer;

DP: TDataPair;


FLastObjIndex := -1;

FPoolSize := 20;

FDataPool := TStringList.Create;

for I := 0 to FPoolSize – 1 do


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);





procedure TServerMethods2.DSServerModuleDestroy(Sender: TObject);


I: Integer;

DP: TDataPair;


for I := 0 to FPoolSize – 1 do


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









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


DP: TDataPair;


if FLastObjIndex = 19 then

FLastObjIndex := 0




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


DP.Query.SQL.Text := SQL;



Result := FLastObjIndex;




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


procedure TForm7.btn1Click(Sender: TObject);



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




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.



Happy coding!

Lessons from successes (not just failures!)

Everyone seems to think that one only learns a lesson when they fail. Though that is mostly true, I think lessons can be learnt from successes too!
In 2009, I was “forced” (why forced? I will come to that some other time…).. forced to start a tech product company. I had very little time to launch the product so I had to make some hard decision very very quickly.

I remember sitting in one of our brain storming sessions on the features the products need to have on the day of the launch. Lots of ideas were flying around, people saying, “we must have this in it” and other guy saying…”customers wont buy if it doesn’t have that”. The cautious (less innovative) guys said that the product must at least do what the existing products in the market did. Well, you know how it goes from here, don’t you? Before we even wrote first line of code, we had 50 lines of features to include. Now, I don’t know about you, but such long features list have baffled me even as a consumer, let alone being a vendor!

It was clear to me at that instant that we were heading for trouble. Conventional wisdom will not help here. The only way the product would capture market share, I thought to myself, was that it did that one thing that the customer needed and it did it better than anyone else. Suddenly the importance of the list of features disappeared and what emerged was importance of quality of those 2 – 3 features that were dead important to the customer. This had number of benefits. Primararily, we as a team, could focus sharply because the scope was narrowed. Second, we could test immediately since we had fewer features to develop, we could develop them faster, make it easier to use, vaghera… vaghera (etc, etc). Third, it didn’t cost us much money. You all know how good it feels when something doesn’t cost THAT much money, right? 🙂

There was still a problem… question of why would customer want to shift to our product if it did what existing products already did in a better way?… kept nagging me. The answer to that came to me in a two pronged way.

Number one: If the product does something better than any of it’s competitor, it will get some traction. I thought how Google managed to take away email share from Hotmail despite Hotmail being the prime email provider for millions at that time. Gmail simply provided better email experience and people didn’t mind changing their email for that. That’s brought me solace for some reason.

Number two: If there was one feature… just one feature… that no other product had (even if it was a gimmick), there would be a way to attract customer’s attention. And that’s exactly what we did. I could go to the customer and say… “No other product in the market can give you this.” Suddenly, we were perceived better than competition!

Conclusion (this is the part you were waiting for) is that we managed to capture some of the largest customers in Mumbai and company was profitable from year one!


1. If you are launching something new, keep the focus narrow. Choose your feature wisely. Invest more time in testing rather than adding new features.

2. Keep something that makes your product stand out. I am not suggesting that you add something ridiculous. All I am saying is that it must stand out enough to charm the customer.

Hope you could take something away from this. I am open to your feedback!