Problem on memory leak with TADOQuery

[Sara T.]

Does anybody find this?

I have something to create and destroy this object (TADOQuery) a lot of
times, I found TADOQuery does not release the memory and eats memory until
the machine becomes out of memory.

I thought the several people found this problem (I search in the google) but
nobody answer.

Again, it anybody finds the solutions, lindy post it.

Sara T.

[Viatcheslav V. Vassiliev]

How do you determine memory leaks? What DBMS you use and does leak occur in
application or in DBMS?

//------------------------------------------
Regards,
Vassiliev V. V.
http://www.managed-vcl.com - using .Net objects in Delphi for Win32 +
ADO.Net
http://www.oledbdirect.com - The fastest way to access MS SQL Server,
MS Jet (Access) and Interbase (through OLEDB)

"Sara T." <sar...@thaiquest.com> сообщил/сообщила в новостях следующее:
news:42e4d1b3$1...@newsgroups.borland.com...

[Pieter]

It would be easier to tell something if you post some code.
Pieter

[Sara T.]

I'm using an simple ADOQuery with:
AdoQuery1.Close;
AdoQuery1.Sql.Clear;
AdoQuery1.SQL.Add('select * from myTable');
AdoQuery1.Open;

Every call I lose memory.

I found the same question in Google but nobody answers.

Sara T.

"Pieter" <Pie...@nomail.com> wrote in message
news:42e62634$1...@newsgroups.borland.com...

[pieter]

Here is a way:

Create a Connection class (Connection.cpp) with a TADOQuery and a method like (not tested):

Connection :: Connection ()
{
//set your connection string here.
ADOQuery1->ConnectioString = ...
//Or you can set the connection string in de object inspector
}

TADOQuery *Connection :: open(String stmtSQL)
{
try {
ADOQuery1->Close();
ADOQuery1->SQL->Clear();
ADOQuery->SQL->Add(stmtSQL);
ADOQuery1->Open();
} catch (Exception &e) {
ShowMessage (e.Message);
}
return ADOQuery1;
}

you can call this class by:
//include header
#include "Connection.h"

TADOQuery *qry = new TADOQuery(Application);
Connection *con = new Connection();

String SQL = "SELECT * FROM myTable"
qry = con->open(SQL);
//do something with the query;

//delete adoquery en connection
delete qry;
delete con;

Good luck.

[Sara T.]

I will try and let you the response.
But I don't understand why it could be worked.

Sara T.

"pieter" <pie...@nomail.com> wrote in message
news:42e65079$1...@newsgroups.borland.com...

[AlexB]

Sara T. wrote:
> I have something to create and destroy this object (TADOQuery) a lot
> of times, I found TADOQuery does not release the
> memory and eats memory until the machine becomes out of memory.

> I'm using an simple ADOQuery with:
>
> AdoQuery1.Close;
> AdoQuery1.Sql.Clear;
> AdoQuery1.SQL.Add('select * from myTable');
> AdoQuery1.Open;
>
> Every call I lose memory. I found the same question in Google but
> nobody answers.

Some month ago:
=========================================
D7, ADODB unit.
we use Jedi or memcheck to track if memory is lost or not. This tools
say that there is a problem in adodb unit when the tadoquery is freed.
So we checked the source code of the vcl and made some modifications:

destructor TADOCommand.Destroy;
begin
// we modified like this ==>
Connection := nil;
FCommandObject := nil;
FreeAndNil(FParameters);
// we modified : end
inherited Destroy;

{ // this is the original source code
Connection := nil;
FCommandObject := nil;
FreeAndNil(FParameters);}
end;

destructor TADOQuery.Destroy;
begin
// we modified like this ==>
FreeAndNil(FSQL);
// we modified : end
inherited Destroy;

// FreeAndNil(FSQL); // original source code
end;

Now: no more memory problem.
Do you think we can used our modifications? Do you agree with that?
---
These fixes are already present in D7 Update 1.
---
I've just passed the Delphi 7 update 1.
The VCL has not changed, the problem still exists.
-------- End of quotation -----------------------
I.e. original VCL src calls "inherited Destroy;" before some other
statements. It's not so good practice but pascal allows it and in theory
no leaks present here... Try to patch ADOdb.pas as described above.
=================================================
QualityCentral #8837

I guess "memory leak" needs no further explanation ;) See steps how to
reproduce... I've traced the memory leak down to
WideStringField.GetAsVariant() in DB.pas. See workaround for possible fix.

(Tested with English D7 Build 8.1)
1. Create a MS-Access 2000 MDB containing a table "Table1" with a string
column named "Column1". Add a few records to that table (actual column
text doesn't matter)
2. Create a new Delphi application. Drop a TADOConnection and a
TADOTable on the form and connect them to "Table1" in the Access database
3. Drop a TButton and add the following lines to it's OnClick event
handler, which basically just accesses the first two rows of the table
alternatingly and reads them into a variable type Variant:

procedure TForm1.Button1Click(Sender: TObject);
var
v: Variant;
begin
try
ADOTable1.Open;
repeat // forever
v := ADOTable1.FieldByName('Column1').Value;
ADOTable1.Next;
v := ADOTable1.FieldByName('Column1').Value;
ADOTable1.Prior;
until False;
finally
ADOTable1.Close;
end;
end;

4. Compile the application and run it (outside Delphi). Watch memory
consumption in the Windows task manager.

memory consumption increased endlessly before the fix (I interrupted the
process at 100 MB) , and after the fix, it never goes over a certain
limit (10 MB in my test app). I've tested it with MemProof. For both
"Live Pointers" and "Virtual Memory", the peak size keeps increasing
permanently.

Workaround:

Instead of using
DataSet.FieldValues['myfield'] or
DataSet.FieldByName('myfield').Value,
use one of the following workarounds:

1) TWideStringField(DataSet.FieldValues['myfield']).Value, which
retrieves the field value as WideString and not as Variant.

2) Use the following function
GetWideString(DataSet.FieldByName('myfield') as TWideStringField) to
retrieve the value as Variant without leaking memory.

function GetWideString(field: TWideStringField): Variant;
var
S: WideString;
begin
if field.DataSet.GetFieldData(field, @S, False)
then Result := S;
else Result := Null;
end;

IMHO 2) is also a possible fix for Delphi's VCL.
TWideStringField.GetAsVariant() and my workaround function above differ
only in 2 points, both of which cause a memory leak:
- S is declared as PWideChar instead of WideString
- Instead of simply using "Result := S", the following code is used.
TVarData(Result).VOleStr := PWideChar(S);
TVarData(Result).VType := varOleStr;

I don't understand Delphi's memory management of WideStrings well enough
to tell where exactly the memory leaks come from, but this workaround
seems to fix them.
========================================
Does anyone know about memory leak issue in TADOQuery? I've search in
Google and Google Groups and I found only very little info about this,
although the problem is spot on. SysString is not released after closing
and freeing ADO Query. I found one workaround from QualityCentral #8837
but there's another that I couldn't fix. The code is very simple:

adoQuery := TADOQuery.Create(nil);
adoQuery.Connection := adoConnection;
adoQuery.Prepared := True;
adoQuery.SQL.Add('SELECT Title, Description ');
adoQuery.SQL.Add('FROM User WHERE Id = ' + IntToStr(Self.FUserId));
adoQuery.Open();
adoQuery.SQL.Clear(); // <-Clear before Close??? (AlexB)
adoQuery.Close();
adoQuery.Free();

On MemProof this will yield SysString not freed resources from deep
inside TCustomADODataSet. The last three entries on the stack is like this:

system.pas WStrFromPWCharLen
variants.pas VarFromWStr
adodb.pas TCustomADODataSet::InternalInitFieldDefs

If I put this snippet in an iteration the number of SysString unreleased
will go up. Does anyone know any workaround/resolution for this issue?
------------------
(without answer...)
==================================
Another tips:
==================================
SetProcessWorkingSetSize(GetCurrentProcess(), DWORD(-1), DWORD(-1));

after test. This will trim working memory set. If this helps, there is
no memory leak, if not - use MemCheck or other tool to find a leak.
==================================
Get TBetterADODataSet (free) and test again. It is said to fix some
problems in the original.
=================================

Good lack ...
Alex.

[Sara T.]

Somebody fixed somecode in ADODB.pas, why Borland does not provide any hot
fix to solve?

Sara T.

"AlexB" <b....@inbox.ru> wrote in message
news:42e705cf$1...@newsgroups.borland.com...

[AlexB]

Sara T. wrote:
> Somebody fixed somecode in ADODB.pas, why Borland does not provide any hot
> fix to solve?

Ask Borland not us :)
In Quality Central you can see a lot of bugs without fix since old
versions (5 - 4 - 3 ...)

Alex.