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

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

Date: 22 Feb 2005 15:15:42 -0800

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