Re: Using parameters with the low level ADO API, trying to avoid the memory leaks.

• Previous message: lotemlotad_at_yahoo.com: "Re: how to run ado application on a client to connect MsSQL (it gives me en error) ?"
• In reply to: Rich S.: "Re: Using parameters with the low level ADO API, trying to avoid the memory leaks."
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Date: 27 Feb 2005 08:16:52 -0800

I’ve been struggling with an application that continued to
consume memory. I had isolated it as far as some issue with
ADO. This has been an ongoing issue and progress has been
slow, but useful. After this introduction I will list the
items that have helped me.

Using Delphi 7 Enterprise, I’m talking to a local Paradox
database to store statistics and production information. The
stats database is updated every thirty seconds and currently
has three records; it may have a max of twenty-one. These
lines run 24x7 and there is no room for leisurely downtime.

The production updates happen every thirty to fifty minutes per
line. Currently we are recording three lines, and will end up
with a maximum of twenty-one with fourteen being the actual
short term goal.

After the items are recorded locally, a second application
polls the Paradox database and copies the statistics to an on-
site MSSQL database, and then to the corporate MSSQL server.

Production information is moved in the same manner, but is
deleted from the source once the target write has been verified.

These intermediate steps allow us to reliably recover from
network, both LAN and WAN, failures.

We have a successful system working with BDE, so I was
surprised when my ADO implementation became a memory pig. I
monitored the results using Performance Monitor. The key items
were process handles, private memory and working memory.

===Here is a run down of items that I’ve done to correct the
===issues.

Using MemCheck (http://v.mahon.free.fr/pro/freeware/memcheck
and MemProof
(http://www.automatedqa.com/products/memproof/index.asp), I
made sure that my base code was free from leaks. AKA fix my
bugs first.

Make sure that the MDAC components and the Jet database
components are up to date.

Make Sure Delphi is up to date.

Freeing TADOConnection and _Connection both leave handles and
memory behind when freed. This seems to happen using either
Jet or MSSQL. Keep the same connection through out the
application.

In my thread the MSSQL TADOCommands seem to leak handles,
private memory and working memory when reused. I did not see
this in the limited testing of a simpler test run in the main
VCL thread. I started freeing each TADOCommand or TADOQuery as
soon as I was done with it and my working memory became
reasonable again.

TADOCommand and TADOQuerys that were connected to MSJet were a
different matter. Freeing them caused the exact opposite
problem with the handles and private memory increasing
quickly. I assume that this would apply to any Jet DB (ie.
Access) not just Paradox.

I modified my code so that the Jet calls reuse the TADOCommand
and MSSQL frees them when done.

Limit the amount of memory MS Jet is allowed to use. Otherwise
it will eventually use it all. Here is a sample of how I
limited each connection 128K.
  SampleADOConn.Properties.Item['Jet OLEDB:Max Buffer Size'].Value := 128
 
I fixed the Borland source that had the “inherited Destroy” at
the wrong place. For TADOConnection.Destroy and
TADOQuery.Destory in ADODB.PAS. This was seen even after the
service pack was applied.

Rich S.

"Rich S." <Arby_001@Yahoo.com> wrote:
>
>Thank you Dave and Viatcheslav.
>
>I'm using the MS Jet driver to talk to the Paradox table.
>
>Using Delphi's EmptyParam as defined in Variants.PAS did the
>trick. I wasn't aware of it before. It works, but it
>raised a question in my mind.
>
>What prevents the value of EmptyParam from being modified
>when used with a COM object that writes something to it?
>General use global variables would seem to be a dangerous
>thing.
>
>Here is the definition (comments edited for line wrap):
>{ Global constants }
>var
> EmptyParam: OleVariant; // "Empty parameter" standard
> {$EXTERNALSYM EmptyParam} // constant which can be
> // passed as an optional parameter
> // on a dual interface.
>
>Reading the help about $EXTERNALSYM indicated that the
>directive prevents the global showing up in a C++ headers,
>nothing there to make the variable read/only. So that isn't it.
>
>Looking further I see that the value of EmptyParam is assigned
>in the initialization section as VAR_PARAMNOTFOUND (defined as
>DISP_E_PARAMNOTFOUND).
>
>initialization
> SetClearVarToEmptyParam(TVarData(EmptyParam));
>
>I assume the COM object ignores the parameter because it is
>disguised as an exception. It will not attempt a write because
>as far as it is concerned it didn't received one.
>
>Clever trick. The only apparent danger of this global would be
>if somebody would overwrite the variable directly with another
>value.
>
>Rich
>
>"Viatcheslav V. Vassiliev" <support@oledbdirect.com> wrote:
>>> Satisfied, I changed "WHERE LineID = '1'"
>>> to "WHERE LineID = :LineID".
>>
>>MS ADO (without ADOExpress or dbGo) passes SQL directly to OLEDB provider.
>>You do not say which provider you use, but most of them will not understand
>>parameters in for :ParamName. For most providers ? should be used:
>>
>>WHERE LineID = ?
>>
>>And try to use EmptyParam instead of Null.
>>
>>//------------------------------------------
>>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)
>>
>>"Rich" <Arby_001@Yahoo.com> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅÅ:
>>news:421bbd1e$1@newsgroups.borland.com...
>>>
>>> This tale involves using the ADO API. We find a problem, we
>>> have a work around, but there is still has a challange to left
>>> to be met.
>>>
>>> We are using Delphi 7 SP1 & MDAC 2.7 SP2 and working with a
>>> Paradox table for this experiment.
>>>
>>> Several others here and I have had problems with memory leaks
>>> using TADOConnection and TADOCommand. My app runs 24x7 and
>>> I’m getting desperate.
>>>
>>> I’m now working directly with the Microsoft ADO components as
>>> exposed with ADOInt. Types such as CoConnection, CoCommand,
>>> and CoRecordSet to bypass some of Delphi's VCL wrapper overhead.
>>>
>>> I've made progress over the last couple of days getting my feet
>>> Wet, but I ran into a problem. I believe it has to do with the
>>> way that Visual Basic can call a ComObject vs. Delphi.
>>>
>>> I started with a simple SQL Update with no parameters. Each
>>> time I run the command, the update is reflected properly in
>>> the table.
>>>
>>> Satisfied, I changed "WHERE LineID = '1'"
>>> to "WHERE LineID = :LineID".
>>>
>>> I can populate the parameter as required, either using the
>>> Command.Parameters.Refresh() to create and then populate them,
>>> or create them individually and use
>>> Command.Parameters.Append(objParam) to assign them.
>>>
>>> When I run the CoCommand’s execute method, the update does not
>>> occur. No amount of playing with the parameter, or the data type
>>> makes a difference. There isn't any exception generated, just a
>>> big fat 0 records affected. The update just does not happen.
>>>
>>> So I tried the third alternate method of passing a variant array
>>> at execute time. That works.
>>>
>>> Here is the first call:
>>>
>>> RecordsAffected is an OleVariant.
>>> adCmdText is a constant that equals 1. It tells ADO
>>> that our CommandText is the literal SQL statement.
>>> ParamCol is a Variant, not an OleVariant.
>>> objParam is a CoParameter.
>>>
>>> Fails:
>>> ObjCmd.Execute(RecordsAffected,null,adCmdText);
>>>
>>>
>>>
>>> Works:
>>> ParamCol := VarArrayCreate([0, 0], VarVariant);
>>> ParamCol[0] := objParam; {Created earlier}
>>>
>>> ObjCmd.Execute(RecordsAffected,ParamCol,adCmdText);
>>>
>>> The VB Call that has worked for me in the past:
>>>
>>> ObjCmd.Execute RecordsAffected
>>>
>>> Note the absent values for parameters and option.
>>>
>>> It seems to me that when I make the original call, it accepts
>>> the null as my new parameter list, doing who knows what to my existing
>>> parameters.
>>>
>>> So how does Delphi call an object that has optional parameters
>>> without passing data?
>>>
>>> I would think that this had been covered years ago, but I
>>> failed in my search for knowledge.
>>>
>>> Rich
>>>
>>
>>
>

• Previous message: lotemlotad_at_yahoo.com: "Re: how to run ado application on a client to connect MsSQL (it gives me en error) ?"
• In reply to: Rich S.: "Re: Using parameters with the low level ADO API, trying to avoid the memory leaks."
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]