使用FireDac在Delphi中动态创建和调用存储过程的正确方法是什么？ [英] What is the proper way to dynamically create and call a stored procedure in Delphi using FireDac?
问题描述
函数TForm21.ExecuteStoredProc（aSPName：string; aParams：TADParams ）：Boolean;
var
LSP：TADStoredProc;
i：整数;
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;
结束
LSP.ExecProc;
finally
LSP.Free;
结束
结果：= True;
结束
我用
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）;
结束
但是，存储过程无法执行。也就是说，代码运行正常，没有显示错误消息，但是存储过程不运行。
进一步的信息 - 如果我放弃一个组件，它运行正常并且在代码中设置参数。
任何人都知道我缺少什么？
看到这个q已经没有得到答复了一段时间，我以为我会尝试让代码工作，而不使用评论的线索，发现它不是那么容易，我想象的。
我马上遇到了SP参数。我发现这个
http ：//docwiki.embarcadero.com/RADStudio/XE5/en/TFDQuery,_TFDStoredProc_and_TFDUpdateSQL_Questions
其中说
如果手工定义参数有困难，
将自动填充Params集合，并检查
参数是如何定义的，然后将其与您的代码
但我找不到一个自动填充Params的方法。我问EMBA
FireDac新闻组和FD作者Dimitry Arefiev，衷心地解释说，
你可以通过检查FetchOptions包括fiMeta，然后清除和设置FDStoredProc的StoredProcName来实现。
在SqlServer上的pubs demo数据库中使用StoredProc定义如下：
create procedure test（@ANumber int，@AName varchar（20））
as
begin
选择
@ANumber * 2作为Number，
@AName + @AName asName
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] 。值;
结束
[...]
程序TForm21.Button1Click（发件人：TObject）;
var
LParams：TFDParams;
参数：TFDParam;
begin
LParams：= TFDParams.Create;
参数：= LParams.Add;
Param.Name：='@ANumber';
Param.Value：= 612;
参数：= LParams.Add;
Param.Name：='@AName';
Param.Value：='2008';
ExecuteStoredProc（'test'，LParams）;
结束
它的工作正常。
OP提到，他首先遇到了SP失败执行
的问题，但是他发现如果他放弃了一个组件并在代码中设置了参数，那么我就以为我在这里包括一个控制台应用程序，当然这些都是代码中的一切。这并不困难，但是我把Uses条款正确使用的时间是发布这个作为答案的主要原因，以备将来参考。 W / o正确的用途，您收到错误的抱怨各种类工厂的缺失。
控制台应用程序（在XE6中创建和测试）：
程序ConsoleStoredProcProject3;
{$ APPTYPE CONSOLE}
{$ R * .res}
使用
System.SysUtils，FireDac.DApt，FireDAC .Stan.Def，FireDAC.Stan.ASync，
FireDAC.Stan.Param，FireDAC.Stan.Option，FireDAC.Comp.Client，
FireDAC.Phys.MSSQL，VCL.ClipBrd;
程序TestSP;
var
连接：TFDConnection;
StoredProc：TFDStoredProc;
参数：TFDParam;
begin
连接：= TFDConnection.Create（Nil）;
Connection.DriverName：='MSSQL';
Connection.Params.Values ['Server']：= //您的服务器名称'';
Connection.Params.Values ['Database']：='pubs';
Connection.Params.Values ['user_name']：='user'; //调整适合
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;
参数：= StoredProc.Params.ParamByName（'@ ANumber'）;
Param.Value：= 333;
参数：= StoredProc.Params.ParamByName（'@ AName'）;
Param.Value：='A';
StoredProc.Active：= True;
WriteLn（StoredProc.FieldByName（'Number'）。AsInteger）;
WriteLn（StoredProc.FieldByName（'Name'）。AsString）;
ReadLn;
结束
begin
try
TestSP;
除了
对于E：Exception do
Clipboard.AsText：= E.Message;
结束
结束。
I am relatively new to FireDAC. I want to be able to call a stored procedure "on the fly", dynamically. 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 with
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 fails to execute. That is, the code runs fine, no error message is shown, but the stored procedure doesn't run.
Further info -- it runs fine if I drop a component and set up the params in code.
Anyone have any idea what I am missing?
Seeing as this q has been left unanswered for a while, I thought I'd try to get the code working without using the clues from the comments and found it not quite as easy as I'd imagined.
I immediately got stuck with the SP params. I found this
http://docwiki.embarcadero.com/RADStudio/XE5/en/TFDQuery,_TFDStoredProc_and_TFDUpdateSQL_Questions
which says
"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. "
but I couldn't find a way of "automatically" populating the Params. I asked on the EMBA FireDac newsgroup and the FD author, Dimitry Arefiev, kindly explained that you can do that by checking that the FetchOptions include fiMeta, and then clearing and setting the FDStoredProc's StoredProcName.
Using a StoredProc in the pubs demo database on my SqlServer defined as follows:
create procedure test(@ANumber int, @AName varchar(20))
as
begin
select
@ANumber * 2 as "Number",
@AName + @AName as "Name"
end
I changed a couple of sections of the OP's code like this
[...]
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;
and it worked fine.
The OP mentions in the q he'd first had the problem that the SP failed to execute but that he'd found that it worked if he "[dropped] a component and set up the params in code" so I thought I'd include here a console application where of course necessarily everything is done in code. This wasn't difficult, but the time it took me to get the Uses clause right is my main reason for posting this as an answer, for future reference. W/o the correct uses, you get errors complaining about various class factories being missing.
Console app (created and tested in 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.
这篇关于使用FireDac在Delphi中动态创建和调用存储过程的正确方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
