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@...
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.