Delphi Stored Procedure can't recognize param

Question

I'm using Firebird 2.5 and IBExpert.

I have the following stored procedure:

SET TERM ^ ;

CREATE OR ALTER PROCEDURE "ButtonGroupName_proc" ("ButtonGroupName_in" "SystemObjectName")
returns ("ButtonGroupName_out" "SystemObjectName")
as
begin
  for
    select "ButtonName"
    from   "ButtonGroupName_ButtonName"
    where  "ButtonGroupName_ButtonName"."ButtonGroupName" = :"ButtonGroupName_in"
    into   :"ButtonGroupName_out"
  do
  suspend;
end
^
SET TERM ; ^

At runtime I coded:

...
var
  lStoredProc : tFDStoredProc;
...
lStoredProc := tFDStoredProc.Create (Application);
  with lStoredProc do begin
        Connection     := dmSysData.SysData_Connection;
        StoredProcName := DoubleQuotedStr ('ButtonGroupName_proc');
        ParamByName ('ButtonGroupName_in').Value := 'ButtonGroup_System_Tasks';
        Open;
        ...
    end;

When running, I get the "parameter 'ButtonGroupName_in' not found" error, though it is declared as input paramter in the Stored Procedure, as can be verified from the script above.

The code above, was adapted from a very similar example from the Web, but it doesn't work with my code.

Answer 1

Although Delphi can infer parameters from a SELECT statement automatically by parsing the contents of the SQL property, it cannot do the same for the parameters of a stored procedure, so you need to define them explicitly with the Params array:

lStoredProc := tFDStoredProc.Create (Application);
  with lStoredProc do begin
        Connection     := dmSysData.SysData_Connection;
        StoredProcName :=   DoubleQuotedStr ('ButtonGroupName_proc');
        Params.Clear;
        Params [0] := tFDParam.Create (Params, ptInput);
        Params [0].Name := 'ButtonGroupName';
        ParamByName ('ButtonGroupName').Value := 'ButtonGroup_System_Tasks';
        Open;
           while   not Eof do begin
                 lButtonName := Fields [0].Value;
                  Next;
           end;

It retrieves what I need: that is, the names of the buttons that belong to a specific Group of buttons.