Devart Forums

Posted: **Tue 10 Apr 2012 12:59**

I have in my database the following procedure:

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

Posted: **Wed 11 Apr 2012 07:50**

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;

Posted: **Wed 11 Apr 2012 07:52**

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.

Posted: **Thu 12 Apr 2012 09:05**

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

Devart Forums

How to retrieve results of stored procedure execution?

How to retrieve results of stored procedure execution?