TFDStoredProc param not found

Stef Merlijn
Stef Merlijn used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Oracle dba
Top Expert 2009
Commented:
Don't the params get evaluated after the prepare ?

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

Author

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

Author

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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial