Hello Grant,

GB> Using Delphi 5 and FIB+ 6.1.0 and I have tested that the connection to
GB> the database is live.

GB> When I try calling the following function I get ant exception when the
GB> Prepare statement is executed.

GB> Could someone show me what I am doing wrong ?

GB> SaveStoredProc is a TpFIBStoredProc
GB> SaveStoredProc.Transaction is set to WriteTrans001 which is a
GB> TpFIBTransaction
GB> SaveStoredProc.SQL is declared as

GB> EXECUTE PROCEDURE P_SAVE_DATA_LIST_ITEM (?FDTYPE, ?ITEM01,
GB> ?CUR_DETECT_ID, ?RESULTID)

You don't have to put it there. You just have to assign
P_SAVE_DATA_LIST_ITEM to SaveStoredProc.StoredProcName.

GB> WriteTrans001 .TRParm list is set to write, concurrency and nowait.

GB> function TDtM.SaveDataListItem(var DetectID, DT: Integer; const
GB> ItemText: string): Integer;
GB> begin
GB> //
GB> with SaveStoredProc_001 do
GB> begin
GB> try
GB> Params[0].Value := DT;
GB> Params[1].Value := ItemText;
GB> Params[2].Value := DetectID;
GB> Prepare;
GB> ExecProc;
GB> DetectID := Params[3].Value;
GB> if Transaction.InTransaction then Transaction.Commit;
GB> Result := 0;
GB> except
GB> begin
GB> if Transaction.InTransaction then Transaction.Rollback;
GB> Result := -200;
GB> end;
GB> end;
GB> end;
GB> end;
It should work. But I'd recommend you after assigning StoredProcName,
firstly Prepare, then assign input parameters, then ExecProc, and then
get output parameters.
Also I'd avoid Variants.

GB> This is the stored procedure that is delcared in my database.

GB> SET TERM ^^ ;
GB> CREATE PROCEDURE P_SAVE_DATA_LIST_ITEM (
GB> FDTYPE BigInt,
GB> ITEM01 VarChar(150),
GB> CUR_DETECT_ID BigInt)
GB> returns (
GB> RESULT_ID BigInt)
GB> AS
GB> declare variable tmpid integer;
GB> begin
GB> if (CUR_DETECT_ID < 0) then
GB> begin
GB> tmpid = GEN_ID(GEN_PK_D_LIST_HEAD_ID, 1);
GB> insert into D_LIST_HEAD(DETECTID, DATATYPE,ITEM001)
GB> values (:tmpid, :FDTYPE, :ITEM01);
GB> RESULT_ID = :tmpid;
GB> end
GB> else
GB> begin
GB> update D_LIST_HEAD set ITEM001=:ITEM01 where DETECTID = :CUR_DETECT_ID;
GB> RESULT_ID = :CUR_DETECT_ID;
GB> end
GB> end
GB> ^^
GB> SET TERM ; ^^
You shouldn't convert your SP to selectable one as Adam says:
executable SPs can have output parameters too. But I'd recommend you
to refer to Dimity's advice about the conent of your SP.


HTH
--
Best regards,
Pavel Menshchikov
http://www.ls-software.com