asked on

Stored Procedure

I made a StoredProcedure to do an update on a table.:

ALTER PROCEDURE "CLIENTS"
(
"CODE" INTEGER,
"PURCHASE" DOUBLE PRECISION
)
AS
BEGIN
update CLIENTS
set TOTAL = TOTAL + :PURCHASE
where CLIENT_CODE = :CODE;
END
^

Do I have to put a commit after the update or something ? Because when I execute the StoredProcedure from my application, then right after try to do an Edit on that table it gives me an Update Deadlock ! What´s wrong ??

Ivanov_G

commit the transaction before execute the stored procedure ...

psycho_cat_69

Try this

ALTER PROCEDURE "CLIENTS"
(
"CODE" INTEGER,
"PURCHASE" DOUBLE PRECISION
)
AS
update CLIENTS
set TOTAL = TOTAL + :PURCHASE
where CLIENT_CODE = :CODE;
GO

fabyola

ASKER

But do I have to do a commit after or does it do it for me automaticlly ?

ASKER CERTIFIED SOLUTION

esoftbg

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

esoftbg

>But do I have to do a commit after or does it do it for me automaticlly ?
TIBTransaction must be Activated before execution of a TIBStoredProc, and must be Commited after its execution.

esoftbg

A little bit improvement of above code:
if B then
begin
IBTR.Commit;
IBQ.Active := False; // It is better to be here in case IBQ and CLIENT_UPDATE_ use different TIBTransaction components
IBQ.Active := True;
IBQ.Locate('CLIENT_CODE', S, []);
end
else
IBTR.Rollback;

fabyola

ASKER

Ok but I do a Commit right after I execute the StoredProcedure

esoftbg

right-click mouse button over the transaction componens and open the Transaction Editor (or double click). Select Read Commited. This will solve your problem.

esoftbg

if IBQ is a TIBTable and IBTQ is it's TIBTransaction, you must use the events:

procedure TFormCLIENTS.IBQBeforePost(DataSet: TDataSet);
begin
if not IBTQ.Active then
IBTQ.StartTransaction;
end;

procedure TFormCLIENTS.IBQAfterPost(DataSet: TDataSet);
begin
if IBTQ.Active then
IBTQ.CommitRetaining;
end;