BorlandTalk.com

Rich · Guest

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

Dave Blake · Guest

Viatcheslav V. Vassiliev · Guest

Rich S. · Guest

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 (AT) oledbdirect (DOT) com> wrote:

Viatcheslav V. Vassiliev · Guest

Overwrite could happen, in cases when parameter is

SomeFunction(var SomeParameter: OleVariant);

and passing EmptyParam to such function.

DISP_E_PARAMNOTFOUND is standard way to pass default value in COM - VB,
VBScript, JScript use this value if value is not specified explicitly.

//------------------------------------------
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 S." <Arby_001 (AT) Yahoo (DOT) com> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅÅ:
news:421c8e65$1 (AT) newsgroups (DOT) borland.com...

Rich S, · Guest

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
([url]http://www.automatedqa.com/products/memproof/index.asp)[/url], 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 (AT) Yahoo (DOT) com> wrote: