Hi Grant,

Not bad for your first SP. I am not familiar with FIB so I can't tell
you if you are using that right, but here is some observations from
the firebird code.

Your stored procedure is a selectable stored procedure (because it
returns something).

Basically

select Result_ID
from P_SAVE_DATA_LIST_ITEM (1,'test',1);

should return you something if you run it in iSQL or any other
Firebird tool. (replace the parameters with something that makes
sense in your database).

Your first problem is that it will not return anything. Firebird uses
the keyword suspend to output a record from a stored procedure. Every
time you call suspend, whatever is sitting in the output variables of
the stored procedure is returned.

So place

suspend;

between the last two "end" statements in the stored procedure.

Then the above select statement should return as expected, and you
can check the doco on the FIB components to make sure you are using
them properly.

Hope that helps

Adam.










--- In firebird-support@yahoogroups.com, Grant Brown <grant@s...>
wrote:

> Hi,
>
> Please forgive my errors as this is the first stored procedure that

I

> have written or used.
>
> Using Delphi 5 and FIB+ 6.1.0 and I have tested that the

connection to

> the database is live.
>
> When I try calling the following function I get ant exception when

the

> Prepare statement is executed.
>
> The purpose of the procedure is to update a record in my table and
> create one if the record does not exist. The stored procedure then
> returns the ID number of the record.
>
> Could someone show me what I am doing wrong ?
>
> SaveStoredProc is a TpFIBStoredProc
> SaveStoredProc.Transaction is set to WriteTrans001 which is a
> TpFIBTransaction
> SaveStoredProc.SQL is declared as
>
> EXECUTE PROCEDURE P_SAVE_DATA_LIST_ITEM (?FDTYPE, ?ITEM01,
> ?CUR_DETECT_ID, ?RESULTID)
>
> WriteTrans001 .TRParm list is set to write, concurrency and nowait.
>
> function TDtM.SaveDataListItem(var DetectID, DT: Integer; const
> ItemText: string): Integer;
> begin
> //
> with SaveStoredProc_001 do
> begin
> try
> Params[0].Value := DT;
> Params[1].Value := ItemText;
> Params[2].Value := DetectID;
> Prepare;
> ExecProc;
> DetectID := Params[3].Value;
> if Transaction.InTransaction then Transaction.Commit;
> Result := 0;
> except
> begin
> if Transaction.InTransaction then Transaction.Rollback;
> Result := -200;
> end;
> end;
> end;
> end;
>
> This is the stored procedure that is delcared in my database.
>
> SET TERM ^^ ;
> CREATE PROCEDURE P_SAVE_DATA_LIST_ITEM (
> FDTYPE BigInt,
> ITEM01 VarChar(150),
> CUR_DETECT_ID BigInt)
> returns (
> RESULT_ID BigInt)
> AS
> declare variable tmpid integer;
> begin
> if (CUR_DETECT_ID < 0) then
> begin
> tmpid = GEN_ID(GEN_PK_D_LIST_HEAD_ID, 1);
> insert into D_LIST_HEAD(DETECTID, DATATYPE,ITEM001)
> values (:tmpid, :FDTYPE, :ITEM01);
> RESULT_ID = :tmpid;
> end
> else
> begin
> update D_LIST_HEAD set ITEM001=:ITEM01 where DETECTID

= :CUR_DETECT_ID;

> RESULT_ID = :CUR_DETECT_ID;
> end
> end
> ^^
> SET TERM ; ^^
>
> --
> Regards,
> Grant Brown
>
> Product Development Manager
> Phone : 02 4229 1185
> Mobile : 0412 926 995
> Email : grant@s...
> Web : www.sitedoc.com.au
>
> SiteDoc - Easy to Use - Powerful Results
>
> This email and any attached files (of whatsoever nature) are

confidential, may be privileged and intended solely for the use of
the intended recipient and are subject to copyright and the Privacy
Act (Cth) Any confidentiality or privilege is not lost or waived
because of any error in sending this email to you. If you have
received this email in error, please advise by return email, delete
it from your system and destroy any copies. Our company is not
responsible for any viruses or harmful programs passed on down via
email. You should have installed, up to date virus scanning software.
The views expressed in this email are those of the sender and should
not necessarily be taken as those of the company.