Geek asks and answers

What is the correct way to dynamically create and call a stored procedure in Delphi using FireDac?

I am relatively new to FireDAC. I want to dynamically call a stored procedure on the fly. So far I have the following:

function TForm21.ExecuteStoredProc(aSPName: string; aParams: TADParams): Boolean;
var
  LSP: TADStoredProc;
  i: Integer;
begin
  LSP := TADStoredProc.Create(nil);
  try
    LSP.Connection := ADConnection1;
    LSP.StoredProcName := aSPName;
    LSP.Prepare;
    for i := 0 to aParams.Count - 1 do
    begin
      LSP.Params[i].Value := aParams[i].Value;
    end;
    LSP.ExecProc;
  finally
    LSP.Free;
  end;
  Result := True;
end;

I call it

procedure TForm21.Button1Click(Sender: TObject);
var
  LParams: TADParams;
begin
  LParams := TADParams.Create;
  LParams.Add.Value := 612;
  LParams.Add.Value := '2008';

  ExecuteStoredProc('HDMTEST.dbo.spARCHIVE_HISTORY_DATA', LParams);
end;

However, the stored procedure does not execute. That is, the code works fine, an error message is not displayed, but the stored procedure does not start.

Additional information - it works fine if I remove the component and adjust the parameters in the code.

Does anyone know what I am missing?

+3
source share
1 answer

, q - , , , , , , .

SP.

http://docwiki.embarcadero.com/RADStudio/XE5/en/TFDQuery,_TFDStoredProc_and_TFDUpdateSQL_Questions

"If you have difficulties with manual definition of parameters,
populate the Params collection automatically and check how the
parameters are defined. Then compare that to your code. "

"" Params. EMBA FireDac FD , , , FetchOptions fiMeta, FDStoredProc StoredProcName.

StoredProc pubs SqlServer :

create procedure test(@ANumber int, @AName varchar(20))
as
begin
  select
   @ANumber * 2 as "Number",
   @AName + @AName as "Name"
end

OP,

[...]
  LSP.Params.Clear;
  LSP.StoredProcName := '';
  LSP.FetchOptions.Items := LSP.FetchOptions.Items + [fiMeta];
  LSP.StoredProcName := aSPName;
  LSP.Prepare;
  Assert(LSP.ParamCount > 0);

  for i := 0 to aParams.Count - 1 do
  begin
    LSP.Params.ParamByName(aParams[i].Name).Value := aParams[i].Value;
  end;
[...]

procedure TForm21.Button1Click(Sender: TObject);
var
  LParams: TFDParams;
  Param : TFDParam;
begin
  LParams := TFDParams.Create;
  Param := LParams.Add;
  Param.Name := '@ANumber';
  Param.Value := 612;

  Param := LParams.Add;
  Param.Name := '@AName';
  Param.Value := '2008';

  ExecuteStoredProc('test', LParams);
end;

.

OP q, , SP , , "[] ", , , , , . , , , Uses , . , .

( XE6):

program ConsoleStoredProcProject3;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils, FireDac.DApt, FireDAC.Stan.Def, FireDAC.Stan.ASync,
  FireDAC.Stan.Param, FireDAC.Stan.Option, FireDAC.Comp.Client,
  FireDAC.Phys.MSSQL, VCL.ClipBrd;

procedure TestSP;
var
  Connection : TFDConnection;
  StoredProc : TFDStoredProc;
  Param : TFDParam;
begin
  Connection := TFDConnection.Create(Nil);
  Connection.DriverName := 'MSSQL';
  Connection.Params.Values['Server'] := // your server name'';
  Connection.Params.Values['Database'] := 'pubs';
  Connection.Params.Values['user_name'] := 'user';    // adjust to suit
  Connection.Params.Values['password'] := 'password'; // ditto
  Connection.LoginPrompt := False;
  Connection.Connected := True;

  StoredProc := TFDStoredProc.Create(Nil);
  StoredProc.Connection := Connection;
  StoredProc.FetchOptions.Items := StoredProc.FetchOptions.Items + [fiMeta];
  StoredProc.StoredProcName := 'test';
  StoredProc.Prepare;
  Param := StoredProc.Params.ParamByName('@ANumber');
  Param.Value := 333;
  Param := StoredProc.Params.ParamByName('@AName');
  Param.Value := 'A';

  StoredProc.Active := True;

  WriteLn(StoredProc.FieldByName('Number').AsInteger);
  WriteLn(StoredProc.FieldByName('Name').AsString);

  ReadLn;
end;

begin
  try
    TestSP;
  except
    on E: Exception do
      Clipboard.AsText := E.Message;
  end;
end.
+1

More articles:


All Articles