How do I use ADO Parameters at run time in Delphi 2006?

Question

I have been banging my head against the desk with this. I have a simple table with 2 columns, like so:

CREATE TABLE [dbo].[MiscInitializers](
 [PKey] [int] IDENTITY(1,1) NOT NULL,
 [Value] [text] NULL,
 CONSTRAINT [PK_MiscInitializers] PRIMARY KEY CLUSTERED 
(
 [PKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

I am trying to update a row with a procedure like this:

function TdmSQL.SetInitializer(Value: string; var Key: string): boolean;
const
  UpdateCmd =
    'update MiscInitializers set Value = :theValue where PKey = :theKey';
  InsertCmd = 'insert into MiscInitializers (Value) values (:Param1)';
var
  tmp: integer;
  rsTmp: TADODataSet;
  foo: TParameter;
  sTmp: string;
begin
  Result := false;
  adoGenericCommand.CommandText := '';
  adoGenericCommand.Parameters.Clear;
  if Key <> '' then
  begin
    // attempt update
    if not TryStrToInt(Key, tmp) then
      exit;
    adoGenericCommand.CommandText := UpdateCmd;
    adoGenericCommand.Prepared := true;
    adoGenericCommand.Parameters.Refresh;
    // some debug stuff
    sTmp := Format('Num Params: %d', [adoGenericCommand.Parameters.Count]);
    ShowMessageBox(sTmp);
    for tmp := 0 to adoGenericCommand.Parameters.Count  - 1 do
    begin
      sTmp := Format('Param %d: Name %s',
        [tmp, adoGenericCommand.Parameters.Items[tmp].Name]);
      ShowMessageBox(sTmp);
    end;
    // end debug stuff
    foo := adoGenericCommand.Parameters.ParamByName('theValue');
    foo.Value.AsString := Value;
    foo := adoGenericCommand.Parameters.ParamByName('theKey');
    foo.Value := Key;
    rsTmp.Recordset := adoGenericCommand.Execute;
    Result := rsTmp.RecordCount = 1;
    exit;
    // etc

What I see happening (with those debug messagebox calls) is that the update command gets 2 parameters, but their names are Param1 and Param2, not theValue and theKey.

Is there a way to set up the parameters at runtime so the ParamByName calls will work with the names I actually want, rather than the Param*N* that I'm getting?

score 2 · Answer 1 · answered Feb 01 '11 at 07:42

2

You can use ParseSQL to generate the Parameters

const
    UpdateCmd = 'update MiscInitializers set Value = :theValue where PKey = :theKey';
var
    ds: TADODataSet;
    I: Integer;
begin
    ds := TADODataSet.Create(nil);
    try
        ds.CommandText := UpdateCmd;
        ds.Parameters.ParseSQL(ds.CommandText, True);
        for I := 0 to ds.Parameters.Count - 1 do
            ShowMessage(ds.Parameters.Items[I].name);
    finally
        ds.Free;
    end;
end;

answered Feb 01 '11 at 07:42

Mikael Eriksson

136,425
22
210
281

No, this suggestion does not work. Calling ParseSQL has the same result as using Refresh. i.e. ds.Parameters.Items[0].Name becomes Param1, etc. – wades Feb 01 '11 at 14:56
I tried that approach before I posted the original question, and observed that when I enumerated the parameters, the names theKey and theValue got changed to ?Param1 and ?Param2, respectively, and that calls to FindParam and ParamByName would only find Param1 and Param2. It turns out that Sertac Akyuz was correct, the call to Refresh was the problem. – wades Feb 01 '11 at 16:30

score 0 · Accepted Answer · answered Jan 31 '11 at 21:34

0

Don't call Refresh on the 'Parameters' after you assign the 'CommandText'. When you call 'Refresh', the VCL turns to the provider for parameter information, and if the returned information does not contain parameter names then the VCL makes up them on the fly.

answered Jan 31 '11 at 21:34

Sertac Akyuz

54,131
4
102
169

Your suggestion is incorrect. When I remove the call to 'Refresh' I don't get any parameters at all (i.e. adoGenericCommand.Parameters.Count = 0). – wades Feb 01 '11 at 14:54
@wades - Strange, ":Param" style parameters should be initialized when you assign the 'CommandText'. As I told, 'Refresh' retrieves parameter information from the provider.. Your 'ParamCheck' is not false per chance? – Sertac Akyuz Feb 01 '11 at 15:39
1

I have to withdraw that objection, it turns out that when I tested it I made a mistake. In fact, omitting the call to Refresh seems to do the trick. – wades Feb 01 '11 at 16:12

How do I use ADO Parameters at run time in Delphi 2006?

2 Answers2