Recent

Author Topic: Firebird procedure commiting  (Read 5009 times)

bambamns

  • Full Member
  • ***
  • Posts: 223
Firebird procedure commiting
« on: October 09, 2014, 05:32:48 am »
Hi,

I have a procedure in Firebird with few loops and some data reads and writes.
I'm calling it from code :
Code: [Select]
with DM.SpremiTabeleQ do
      begin   
       SQL.Text := 'execute procedure NAPUNI_SVE '+ IntToStr(_thread)+','+IntToStr(_agent)+','+IntToStr(_full);
       open;
       //ExecSQL; // If you do not expect return data use TSQLQuery.ExecSQL instead of TSQLQuery.Open
       rezultat := FieldByName('REZULTAT').AsInteger;
       close;
       DM.SQLTransaction.CommitRetaining;
      end;       

Next step is to read values written with this procedure.

Sometimes server is very busy and commit takes few seconds.

Is there a way to check Transaction finished commiting ?

Thank you
Lazarus 1.8.4 + FPC 2.6.4 x86 (rebuild) and Lazarus 2.0, Windows 7 x64, unless otherwise specified

exdatis

  • Hero Member
  • *****
  • Posts: 668
    • exdatis
Re: Firebird procedure commiting
« Reply #1 on: October 09, 2014, 07:38:48 am »
Try : Connection.ExecuteDirect(sql_text)
http://wiki.freepascal.org/SqlDBHowto#How_to_execute_direct_queries.2Fmake_a_table.3F
or
select REZULTAT From your_procedure(params); then qery.ApplyUpdates() and finally transaction.CommitRetaining
« Last Edit: October 09, 2014, 07:42:06 am by exdatis »

bambamns

  • Full Member
  • ***
  • Posts: 223
Re: Firebird procedure commiting
« Reply #2 on: October 18, 2014, 05:01:34 am »
I did all the changes and put it on the live production, but problem is still present.

Now my Firebird procedure is changed and I don't need return result, so I use ExecSQL.

Average use of this procedure is around 100 times in a day, with 2-3 commit delays.

So, back to first question - How to detect Transaction commit is finished ?

Thx
Lazarus 1.8.4 + FPC 2.6.4 x86 (rebuild) and Lazarus 2.0, Windows 7 x64, unless otherwise specified

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Firebird procedure commiting
« Reply #3 on: October 18, 2014, 05:20:23 am »
Perhaps you can use Firebird events?
http://wiki.lazarus.freepascal.org/TFBEventMonitor
Haven't looked into them but sounds like an elegant solution.

Otherwise have the procedure write a value to a table and check that table in other code?

Edit: IIUC, this info should also be retrievable through the Firebird monitoring tables.
« Last Edit: October 18, 2014, 06:08:50 am by BigChimp »
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Firebird procedure commiting
« Reply #4 on: October 18, 2014, 06:10:38 am »
select REZULTAT From your_procedure(params); then qery.ApplyUpdates() and finally transaction.CommitRetaining
@bambamns: have you tried this? Does it work? No need to actually do anything with the result, but you'd be sure that the transaction has finished when you get it.
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

bambamns

  • Full Member
  • ***
  • Posts: 223
Re: Firebird procedure commiting
« Reply #5 on: October 18, 2014, 07:45:53 am »
@BigChimp

I did tried to select "rezultat" - inside procedure I'm giving integer value 0 to "rezultat" on start of procedure and 1 on the end of procedure, but still result is the same.

Procedure writes some data to another table and I read content from it.
I've used to re-read data from the table with TDataset.Refresh - now I changed to TDataset.close and TDataset.Open.

I'll test it and I'll report results.
Lazarus 1.8.4 + FPC 2.6.4 x86 (rebuild) and Lazarus 2.0, Windows 7 x64, unless otherwise specified

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Firebird procedure commiting
« Reply #6 on: October 18, 2014, 10:53:51 am »
I did tried to select "rezultat" - inside procedure I'm giving integer value 0 to "rezultat" on start of procedure and 1 on the end of procedure, but still result is the same.
IIRC you *need* to use SUSPEND in selectable stored procedures to get the output into the variable. Are you doing that?
Also "result is the same" - what is the result? What is the value of REZULTAT that you get in your code? 0? Error message?

Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

exdatis

  • Hero Member
  • *****
  • Posts: 668
    • exdatis
Re: Firebird procedure commiting
« Reply #7 on: October 18, 2014, 05:44:52 pm »
Example(to use something like - select success_id from GET_BLITEMS(:curr_order):
SET TERM ^ ;
CREATE PROCEDURE GET_BLITEMS (
    CURR_ORDER integer )
RETURNS (
    SUCCESS_ID integer )
AS
DECLARE ZERO_OUT NUMERIC(12,3);
DECLARE CURR_ITEM INTEGER;
DECLARE CURR_QUANTITY NUMERIC(12,3);
BEGIN
  ZERO_OUT = 0;
  FOR SELECT
    A.BLI_PRODUCT,
    A.BLI_QUANTITY
  FROM
    BLIST_ITEMS A  /* THIS IS FROM BAR_MAG */
  WHERE
    A.BLI_ORDER = :CURR_ORDER
  INTO
    :CURR_ITEM,
    :CURR_QUANTITY
  DO
    BEGIN
      INSERT INTO
        BL_ITEMS
        (
          BLIT_PRODUCT,
          BLIT_QUANTITY,
          BLIT_OUT,
          BLIT_ORDER
        )
      VALUES
        (
          :CURR_ITEM,
          :CURR_QUANTITY,
          :ZERO_OUT,
          :CURR_ORDER
        );
    END
  SUCCESS_ID = 1;
  SUSPEND;
END^
SET TERM ; ^

works ok.

example2(return records):
SET TERM ^ ;
CREATE PROCEDURE SP_PRODUCT_PRICES (
    CURR_PLIST integer )
RETURNS (
    _P_ID integer,
    _P_PRODUCT integer,
    _P_PRICE numeric(12,3),
    _ P_PLIST integer,
    _P_CODE varchar(30),
    _ P_NAME varchar(70),
    M_NAME varchar(50),
    T_NAME varchar(90),
    FULL_PRICE numeric(12,3) )
AS
BEGIN

  FOR SELECT
    A.P_ID,
    A.P_PRODUCT,
    A.P_PRICE,
    A.P_PLIST,
    B.P_CODE,
    B.P_NAME,
    C.M_NAME,
    D.T_NAME,
    (A.P_PRICE * (1.000 + (D.T_VALUE / 100.000))) AS FULL_PRICE
  FROM
    PRICES A,
    PRODUCT B,
    MEASURE C,
    TAXES D
  WHERE
    A.P_PLIST = :CURR_PLIST
    AND
    B.P_ID = A.P_PRODUCT
    AND
    C.M_ID = B.P_MEASURE
    AND
    D.T_ID = B.P_TAX
  ORDER BY
    A.P_ID
  INTO
    :_P_ID,
    :_P_PRODUCT,
    :_P_PRICE,
    :_P_PLIST,
    :_P_CODE,
    :_P_NAME,
    :M_NAME,
    :T_NAME,
    :FULL_PRICE
  DO
  BEGIN
    SUSPEND;
  END
END^
SET TERM ; ^
« Last Edit: October 18, 2014, 05:48:04 pm by exdatis »

bambamns

  • Full Member
  • ***
  • Posts: 223
Re: Firebird procedure commiting
« Reply #8 on: October 22, 2014, 05:05:27 am »
Thank you all.

Everything is working now.
Lazarus 1.8.4 + FPC 2.6.4 x86 (rebuild) and Lazarus 2.0, Windows 7 x64, unless otherwise specified

 

TinyPortal © 2005-2018