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

From: Rich S. (Arby_001_at_Yahoo.com)
Date: 02/23/05 

Date: 23 Feb 2005 06:08:37 -0800

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

Relevant Pages