Skip to Content
avatar image
Former Member

commit in stored procedure ?

Hi,

Can I use „commit“ in a stored procedure?

I am using SAP HANA SPS5 Rev52v3.0.

I get the error message: SAP DBTech JDBC: [7]: feature not supported

For  example:

create procedure xxx

as

begin

update table_a set …….;

commit;

call long_time_calculation();

end;


Regards,

YH

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Oct 26, 2013 at 04:26 AM

    No you can't. What are you trying to achieve? The UPDATE command will always commit when it is run.

    John

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 29, 2013 at 09:11 PM

    Hi John,

    I tested your code. The test steps and the result look like the following:

    Step

    Database connection 1

    with your code

    Database connection 2 connection 2 result My comment 1 drop table "_JobPool_RS"


    2 create row table "_JobPool_RS"


    3 create index "IX_JobPool_RS_JobID"


    4 drop table interim_results


    5 create row table interim_results


    6 drop procedure "JobPool_Create"


    7 create procedure "JobPool_Create"


    8 CALL "JobPool_Create"()


    9 drop procedure "JobPool_Run"


    10 create procedure "JobPool_Run"


    11 call "JobPool_Run"


    12

    /*
    1000000 inserts and updates
    inside procedure "JobPool_Run"

    and procedure "JobPool_Run" is still running.

    */

    select count(*)
    from interim_results 0 inserts and updates don't commit immediately.
    Autocommit doesn't work here inside procedure  JobPool_Run . 13 select count(*)
    from interim_results 0 ... 14 select count(*)
    from interim_results 0 ... 15 ......
    ... 16 /* call "JobPool_Run" is finished */ select count(*)
    from interim_results 100000 Autocommit works here
    after the end of the procedure JobPool_Run

    Now you can test please once again this with exec 'commit' inside procedure. You will immediately see the committed data in the table "interim_results" with connection 2.

    Now the result:


    1) Your said "The UPDATE command will always commit when it is run"

    => This is not correct. Autocommit doesn't work inside a procedure.


    2) My question again: Were the changes inside the procedure really committed before the end of the procedure call ?

    => Please your answer.


    3) exec 'commit' is a workaround for autocommit for insert/update inside a procedure.

    => Please your test result.

    Regards

    YH

    Add comment
    10|10000 characters needed characters exceeded

    • Yes, it all works perfectly on my system including the auto commit. I am not sure why you get different results so you should open a support call with SAP.

      John