🚀 go-pugleaf

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.