TFDStoredProc param not found

Hi,

In my Delphi application I have a TFDStoredProc (name: aspCopyMyRecord) to access a MS SQL Server database.
Sometimes when the procedure below is executed, I get an error:
aspCopyMyRecord: Parameter '@ID' not found

procedure TDM3.CopyMyRecord(vID, vID2 : Integer; CONST vCode : String);
begin
  try
    with aspCopyMyRecord do
    begin
      ResourceOptions.UnifyParam := False;
      Close;
      StoredProcName := 'dba_CopyMyRecord';
      FetchOptions.Items := aspCopyMyRecord.FetchOptions.Items - [fiMeta];
      Command.FillParams(aspCopyMyRecord.Params);
      Prepare;
      Params.ParamByName('@ID').Value     := vID;
      Params.ParamByName('@ID2').Value  := vID2;
      Params.ParamByName('@Code').Value    := vCode;
      ExecProc;
    end;
  finally
    CheckReturn(aspCopyMyRecord);
  end;
end;

Open in new window


The parameters in SQL Server Stored Proc are named like:
CREATE PROCEDURE dba_CopyMyRecord( 
     @ID INT 
   , @ID2 INT
   , @Code nVarChar(50)
   , @ReturnValue INT OUTPUT)

Open in new window


Can anybode see what might be wrong with my code?
Thanks.
Stef MerlijnDeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Geert GOracle dbaCommented:
Don't the params get evaluated after the prepare ?

i always create params myself on sql
with stp.createParam ...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Stef MerlijnDeveloperAuthor Commented:
For as far as I investgated it, FireDac has a mechanism that removes the @ for some databases (o.a. SQL Server). That's why I use:
  ResourceOptions.UnifyParams := False;

Open in new window

But for some reason this doesn't always work.
Depending on the DBMS, the parameter name may be prefixed with '@' or ResourceOptions.UnifyParams set to True.
Info on: ResourceOptions.UnifyParams

In my development enviroment it works fine as it is setup now, but some customers still generate the error now and than.
Bruno BuesserCommented:
How did you create the stored procedure in your program?
If I create the stored procedure in code (with MySQL) I do the following:

  FScCreateUserProc:= TFDStoredProc.Create(Self);
  with FScCreateUserProc do
  begin
    Connection:= FConnection;
    StoredProcName:= 'ScCreateUser';
    Params.Clear;
    Params.Add('AUserName', ftString,45,ptInput);
    Params.Add('AFirstName', ftString,45,ptInput);
    Params.Add('ALastName', ftString,45,ptInput);
    Params.Add('ARoleName', ftString,45,ptInput);
    Params.Add('AId', ftString,4,ptOutput);
  end;

Open in new window

After that you can set the values and execute the procedure:

     
with FScCreateUserProc do
      begin
        Params.ParamByName('AUserName').AsString:= LoginName;
        Params.ParamByName('AFirstName').AsString:= FirstName;
        Params.ParamByName('ALastName').AsString:= LastName;
        Params.ParamByName('ARoleName').AsString:= RoleName;
        ExecProc;
        Id:= Params.ParamByName('AId').AsInteger;

Open in new window

Stef MerlijnDeveloperAuthor Commented:
Thanks for the help.
Both solution should probably work fine, but I've decided to use TADOStroredProc in stead of TFDStoredProc, as I kept getting errors from customers.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.