Firebird procedure commiting
lazarus / 16-Databases

----------------------------------------------------------------------
bambamns · 2014-10-09 03:32

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

----------------------------------------------------------------------
exdatis · 2014-10-09 05:38

Try : Connection.ExecuteDirect(sql_text)
http://wiki.freepascal.org/SqlDBHowto#How_to_execute_direct_queries.2F
make_a_table.3F
or
select REZULTAT From your_procedure(params); then qery.ApplyUpdates()
and finally transaction.CommitRetaining

----------------------------------------------------------------------
bambamns · 2014-10-18 03:01

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

----------------------------------------------------------------------
BigChimp · 2014-10-18 03:20

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.

----------------------------------------------------------------------
BigChimp · 2014-10-18 04:10

Quote from: exdatis on October 09, 2014, 07:38:48 amselect 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.

----------------------------------------------------------------------
bambamns · 2014-10-18 05:45

@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.

----------------------------------------------------------------------
BigChimp · 2014-10-18 08:53

Quote from: bambamns on October 18, 2014, 07:45:53 amI 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?

----------------------------------------------------------------------
exdatis · 2014-10-18 15:44

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 ; ^

----------------------------------------------------------------------
bambamns · 2014-10-22 03:05

Thank you all.

Everything is working now.