Devart Forums

Hello,

I have created this stored procedure :

SET TERM ^^ ;
CREATE PROCEDURE P_STATUS_DOMINANT (
PRJT Char(30),
TRBN Char(30),
DATE_DEBUT TimeStamp,
DATE_FIN TimeStamp)
returns (
DEBUT_STATUS TimeStamp,
CODE_STATUS Char(10),
NOM_STATUS Char(50),
FAMILLE_ARRET_CNSTRCTR Char(30),
FAMILLE_ARRET_VALOREM Char(30))
AS

begin

select FIRST 1 DEBUT_STATUS,CODE_STATUS, NOM_STATUS,FAMILLE_ARRET_CNSTRCTR,FAMILLE_ARRET_VALOREM from STATUS
where NOM_PROJET=:prjt and REF_TURBINE_VALOREM=:trbn
and CODE_BRAKING_PRGRM'0' and DEBUT_STATUS '0' and DEBUT_STATUS between :DATE_DEBUT and :DATE_FIN
order by DEBUT_STATUS asc into :debut_status, :code_status,:nom_status,:famille_arret_cnstrctr,:famille_arret_valorem
do
begin
suspend;
end

end
^^
SET TERM ; ^^

Using Delphi i do this :

frmMain.IBCStrPrc.StoredProcName:='P_STATUS_DOMINANT';
frmMain.IBCStrPrc.Prepare;
frmMain.IBCStrPrc.ParamByName('PRJT').AsString:=prjt;
frmMain.IBCStrPrc.ParamByName('TRBN').AsString:=trbn;
frmMain.IBCStrPrc.ParamByName('DATE_DEBUT').AsDateTime:=dt;
frmMain.IBCStrPrc.ParamByName('DATE_FIN').AsDateTime:=IncMinute(dt,10);
frmMain.IBCStrPrc.Open;

When Open is called i have the message "sql statement doesn't return rows"
How can i do to read the values returned by the stored function?

Thank you for help

Regards

You should use the PrepareSQL method instead of Prepare to fill SQL and create parameters for the stored procedure.
This method has the IsQuery parameter. If you pass True for this parameter, the TIBCStoredProc component generates SELECT statement in the SQL property, and you can call the Open method:

I have tried it. Now i have no error but if i do

While not frmMain.IBCStrPrc.eof do
showmessage(frmMain.IBCStrPrc.Fields[0].Asstring)

i only see one record but the select should return me more than on record.

Thank you for help

Regards

This is an error in your stored procedure. You should use a stored procedure like the following:

Hum This is strange.

Are you sure that IBCStorcProc is able to return more than one record?
Because i have tried with IBCQuery and all works good.

Regards

The TIBCStoredProc component can return more that one record. Maybe you should try with another stored procedure.

Thank you plash

I will have a look on my code.
Like my code works IBCquery i would like to know the difference between IBCQuery and IBCStoredProc. What is the better to use to call a stored procedure?

Thank you

Regards

The TIBCStoredProc component generates a value for the SQL property automatically, while the TIBCQuery component does not. This is the only difference between these components.