我已在Firebird数据库中定义了此存储过程:
创建或更改过程GET_MSG( IDLNG smallint, IDMSG整数)返回( MSG varchar(200)字符集UTF8) as 开始 IF(:IDMSG> 40000)如果(:IDLNG = 1),则然后开始如果(X_IDLNG = 1),则从XMSG2中选择NOMBRE01,其中ID_XMSG2 =:IDMSG进入:MSG; 出口; END IF(:IDLNG = 2)然后开始从XMSG2选择NOMBRE02,其中ID_XMSG2 =:IDMSG INTO:MSG; 出口; END END ELSE 开始 IF(:IDLNG = 1)然后 BEGIN 从XMSG那里选择NOMBRE01 ID_XMSG =:IDMSG INTO:MSG; 出口; END 如果(:IDLNG = 2)然后开始从XMSG选择NOMBRE02,其中ID_XMSG =:IDMSG进入:MSG; 退出; END END 结尾,我使用此代码从Firedac调用此存储过程:
SPGeneric.StoredProcName:='GET_MSG'; SPGeneric.FetchOptions.Items:= SPGeneric.FetchOptions.Items-[fiMeta]; SPGeneric.Prepare;使用SPGeneric的$ b $b。Params确实以清除; 和Add一起开始名称:='IDLNG'; ParamType:= ptInput; DataType:= ftSmallint; 价值:= IdLan; 结尾; 和Add一起开始名称:='IDMSG'; ParamType:= ptInput; DataType:= ftInteger; 价值:= ID; 结尾; 和Add一起开始名称:='MSG'; ParamType:= ptOutput; DataType:= ftString; 大小:= 200; 结尾; 结尾; SPGeneric.ExecProc; 结果:= VarToStr(SPGeneric.Params [2] .Value);问题是,当我使用正确的参数调用此代码(在Firebird中检查)时,结果是始终为null。这段代码有什么问题吗?谢谢
这是可以正常运行的代码:
SPGeneric。 StoredProcName:='GET_MSG'; SPGeneric.FetchOptions.Items:= SPGeneric.FetchOptions.Items-[fiMeta]; SPGeneric.Params.Clear; 与SPGeneric.Params.Add一起开始名称:='IDLNG'; ParamType:= ptInput; DataType:= ftSmallint; 结尾; 与SPGeneric.Params.Add一起开始名称:='IDMSG'; ParamType:= ptInput; DataType:= ftInteger; 结尾; 与SPGeneric.Params.Add一起开始名称:='MSG'; ParamType:= ptOutput; DataType:= ftWideString; 大小:= 200; 结尾; SPGeneric.Prepare; SPGeneric.Params [0] .Value:= IdLan; SPGeneric.Params [1] .Value:= ID; SPGeneric.ExecProc; 结果:= VarToStr(SPGeneric.Params [2] .Value);
- 调用填写参数后进行准备。
- 在调用准备后分配参数值。
从文档:
在调用Prepare之后,应用程序无法更改命令参数数据类型和大小。否则,在下一次Execute / ExecSQL / ExecProc / Open调用期间,将引发异常。 建议在准备调用之前设置参数。
在这里,您选择不使用
SPGeneric.FetchOptions.Items:= SPGeneric.FetchOptions.Items-[fiMeta];因此,由于您是手动定义参数,因此应在之前调用准备。
I've this Stored Procedure defined within a Firebird Database:
create or alter procedure GET_MSG ( IDLNG smallint, IDMSG integer) returns ( MSG varchar(200) character set UTF8) as begin IF (:IDMSG > 40000) THEN BEGIN IF (:IDLNG = 1) THEN BEGIN SELECT NOMBRE01 FROM XMSG2 WHERE ID_XMSG2 = :IDMSG INTO :MSG; EXIT; END IF (:IDLNG = 2) THEN BEGIN SELECT NOMBRE02 FROM XMSG2 WHERE ID_XMSG2 = :IDMSG INTO :MSG; EXIT; END END ELSE BEGIN IF (:IDLNG = 1) THEN BEGIN SELECT NOMBRE01 FROM XMSG WHERE ID_XMSG = :IDMSG INTO :MSG; EXIT; END IF (:IDLNG = 2) THEN BEGIN SELECT NOMBRE02 FROM XMSG WHERE ID_XMSG = :IDMSG INTO :MSG; EXIT; END END endand I use this code to call this Stored Procedure from Firedac :
SPGeneric.StoredProcName:= 'GET_MSG'; SPGeneric.FetchOptions.Items:= SPGeneric.FetchOptions.Items - [fiMeta]; SPGeneric.Prepare; with SPGeneric.Params do begin Clear; with Add do begin Name:= 'IDLNG'; ParamType:= ptInput; DataType:= ftSmallint; Value:= IdLan; end; with Add do begin Name:= 'IDMSG'; ParamType:= ptInput; DataType:= ftInteger; Value:= Id; end; with Add do begin Name:= 'MSG'; ParamType:= ptOutput; DataType:= ftString; Size:= 200; end; end; SPGeneric.ExecProc; result:= VarToStr(SPGeneric.Params[2].Value);The problem is that when I call this code with correct parameters (checked within Firebird), the result is always null. Is there anything wrong with this code?. Thanks
This is the code that works ok:
SPGeneric.StoredProcName:= 'GET_MSG'; SPGeneric.FetchOptions.Items:= SPGeneric.FetchOptions.Items - [fiMeta]; SPGeneric.Params.Clear; with SPGeneric.Params.Add do begin Name:= 'IDLNG'; ParamType:= ptInput; DataType:= ftSmallint; end; with SPGeneric.Params.Add do begin Name:= 'IDMSG'; ParamType:= ptInput; DataType:= ftInteger; end; with SPGeneric.Params.Add do begin Name:= 'MSG'; ParamType:= ptOutput; DataType:= ftWideString; Size:= 200; end; SPGeneric.Prepare; SPGeneric.Params[0].Value:= IdLan; SPGeneric.Params[1].Value:= Id; SPGeneric.ExecProc; result:= VarToStr(SPGeneric.Params[2].Value);
- call Prepare after filling the parameters.
- assign the parameters values after call prepare.
From the documentation :
After Prepare is called, the application cannot change command parameter data types and sizes. Otherwise, during the next Execute / ExecSQL / ExecProc / Open call, an exception will be raised. It is recommended to setup parameters before the Prepare call.
Here you have elected to not autopopulate the parameter information with
SPGeneric.FetchOptions.Items:= SPGeneric.FetchOptions.Items - [fiMeta];So, since you are manually defining the parameters you should do this before calling Prepare.
更多推荐
从FireDac存储过程中检索输出参数
发布评论