January 29th, 2011, 07:54 AM
Join Date: Jan 2011
Time spent in forums: 3 h 4 m 26 sec
Reputation Power: 0
Committing records inserted by Stored Procedure
I created the following Stored Procedure
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;
And with the following code I call the stored procedure with 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;
As it is seen, some records inserted to a table by stored procedure called in my delphi application.
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