January 29th, 2011, 07:54 AM
Committing records inserted by Stored Procedure
I created the following Stored Procedure
And with the following code I call the stored procedure with my Delphi application
SET TERM ^ ;
CREATE OR ALTER PROCEDURE EDIT_FATURA (fat_id integer, mflag integer) as
update inv_lines set mflag = :mflag where fat_id = :fat_id;
delete from inv_lines_temp where 1=1 ;
insert into inv_lines_temp select ID,CODE_ID,ADET,UNIT_PRICE,LINE_NET,LINE_VAT,KDV,LINE_GRAND,CRN
from inv_lines where fat_id = :fat_id;
SET TERM ; ^
GRANT SELECT,UPDATE ON INV_LINES TO PROCEDURE EDIT_FATURA;
GRANT SELECT,INSERT,DELETE ON INV_LINES_TEMP TO PROCEDURE EDIT_FATURA;
GRANT EXECUTE ON PROCEDURE EDIT_FATURA TO SYSDBA;
As it is seen, some records inserted to a table by stored procedure called in my delphi application.
dm.q_genel.SQL.Add('EXECUTE PROCEDURE EDIT_FATURA(:p1,:p2)');
dm.q_genel.ParamByName('p1').AsInteger := fid;
dm.q_genel.ParamByName('p1').AsInteger := sayi;
But since they are not committed after the execution of the stored procedure, I cannot access newly inserted records in my application.
How i can commit rows inserted by Stored Procedure so that i can access them in my application?
As far as i know, it is not allows "commit" within a Stored Procedure
January 29th, 2011, 08:07 AM
I don't know Delphi, but why don't you simply issue a commit after calling [i]dm.q_genel.execute;[/]?
There must be a way in Delphi to issue a commit in the current transaction.
January 29th, 2011, 03:53 PM
I resolved it. Mine was a programming error in Delphi side.
January 31st, 2011, 05:11 AM
I don't know is this the real code but you set P1 twise
q_genel.ParamByName('p1').AsInteger := fid;
dm.q_genel.ParamByName('p1').AsInteger := sayi; - here must be P2
January 31st, 2011, 05:51 AM
Problem has been resolved. Thanks.
I was assigning a wrong fid value. Thats why i didnt get it