Problem using TIBCStoredProc

calou · Post by **calou** » Wed 05 Nov 2008 10:43

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

Plash · Post by **Plash** » Thu 06 Nov 2008 09:24

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:

Code: Select all

frmMain.IBCStrPrc.StoredProcName:='P_STATUS_DOMINANT';
frmMain.IBCStrPrc.PrepareSQL(True);
...
frmMain.IBCStrPrc.Open;

calou · Post by **calou** » Thu 06 Nov 2008 10:39

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

Plash · Post by **Plash** » Fri 07 Nov 2008 11:26

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

Code: Select all

CREATE PROCEDURE SEL_FROM_EMP 
RETURNS (
    EMPNO INTEGER,
    ENAME VARCHAR(10),
    JOB VARCHAR(9),
    MGR INTEGER,
    SAL INTEGER,
    COMM INTEGER,
    DEPTNO INTEGER)
AS
BEGIN
  FOR  SELECT EMPNO, ENAME, JOB, MGR, SAL, COMM, DEPTNO FROM emp
    INTO :EMPNO, :ENAME, :JOB, :MGR, :SAL, :COMM, :DEPTNO
  DO
    suspend;
END^

calou · Post by **calou** » Fri 07 Nov 2008 12:34

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

Plash · Post by **Plash** » Mon 10 Nov 2008 08:40

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

calou · Post by **calou** » Mon 10 Nov 2008 18:15

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

Plash · Post by **Plash** » Tue 11 Nov 2008 07:59

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.