How to retrieve results of stored procedure execution?

[pawels]

I have in my database the following procedure:

Code: Select all

create or alter procedure ACCES_CTRL (
    IUSER varchar(12),
    IFORMA varchar(30),
    IPROGRAM varchar(30))
returns (
    RKOD_DOST smallint,
    RFULLNAME varchar(66))
as
BEGIN
  SELECT kod_dost, fullname
  FROM USER_ACCES
  WHERE (usymbol = :iuser) AND (forma = :iforma)
  AND (program_name = :iprogram)
  INTO :rkod_dost, :rfullname; 
END

and I'm migrating from ibx.
So until now I used to use tibstoredproc in the following way:

Code: Select all

 with spActrl do
   begin
     if Admin then result:=99
     else
       begin
          ParamByName('iUser').AsString := pUser;
          ParamByName('iForma').AsString := pForma;
          ParamByName('iProgram').AsString := Application.Title;
         execproc;
          Result := parambyname('RKOD_DOST').AsInteger;
          
       end;
     end;
 end;

How should i access the storedprocedure results and return rkod_dost because the above code (of course with replaced execproc to execute) tells me that rkod_dost is unknown.

I'm looking forward to your help.
Regards
Pawel

[ZEuS]

Hello,

To access the stored procedure's parameters you need to call the PrepareSQL method, like this:

Code: Select all

 with spActrl do
   begin
     if Admin then result:=99
     else
       begin
          PrepareSQL; // this line should be added

          ParamByName('iUser').AsString := pUser;
          ParamByName('iForma').AsString := pForma;
          ParamByName('iProgram').AsString := Application.Title;
         execproc;
          Result := parambyname('RKOD_DOST').AsInteger;
         
       end;
     end;
 end;

[pawels]

So lets help myself...
To access output parameters of the stored procedure, you have to right click on tibcstoredproc component and invoke ... Fields editor and then ctrl+F on it to create "fields" which are reald output params and then they are seen on parameters list.
Easy..
Then the procedure :

Code: Select all

 with spActrl do
   begin
     if Admin then result:=99
     else
       begin
          ParamByName('iUser').AsString := pUser;
          ParamByName('iForma').AsString := pForma;
          ParamByName('iProgram').AsString := Application.Title;
          execute;
          Result := parambyname('RKOD_DOST').AsInteger;
       end;
     end;

works.

[AndreyZ]

We have added automatic parameter creation on choosing the stored procedure name. This functionality will be included in the next IBDAC build.