#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2011
    Posts
    14
    Rep Power
    0

    Committing records inserted by Stored Procedure


    I created the following Stored Procedure

    Code:
    SET TERM ^ ;  
    CREATE OR ALTER PROCEDURE EDIT_FATURA (fat_id integer, mflag integer) as 
    begin     
      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; 
    end^  
    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

    Code:
    dm.q_genel.Close;     
    dm.q_genel.SQL.Clear;     
    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; 
    dm.q_genel.execute;
    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
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    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.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2011
    Posts
    14
    Rep Power
    0
    Thanks shammat,

    I resolved it. Mine was a programming error in Delphi side.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    226
    Rep Power
    12
    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

    After
    dm.q_genel.execute;
    Use
    dm.q_genel.Transaction.Commit;
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2011
    Posts
    14
    Rep Power
    0
    Problem has been resolved. Thanks.

    I was assigning a wrong fid value. Thats why i didnt get it

IMN logo majestic logo threadwatch logo seochat tools logo