Skip to Content

Odata exit - SQL Procedure COMMIT

Hi,

I implemented a odata exit for CREATE. One problem I faced was. In the procedure I have an INSERT statement that does not commit to the table unless I explicitly issue a COMMIT after the insert statement.

Now, I understand that a procedure commits changes once it exits. Without the commit, when I try SELECT on the table for the inserted record, inside the procedure, I am able the select the record. But a data preview on the table returns empty.

My question is, Why does not the INSERT work, is it any different for an odata exit? Does a SQL procedure have a defined buffer or internal space for tables used?

BEGIN
/***************************** 
Write your procedure logic 
 *****************************/
 DECLARE var_lastupddate VARCHAR(30);
 DECLARE var_count INT;
 

 var_lastupddate := :in_row.LAST_UPDATE_DTTM[1];

 INSERT INTO "SCHEMA"."TABLE" 
 SELECT * FROM :in_row;    //:inrow is the input table
 COMMIT;

/* this was just to check if the insert was successful*/
 SELECT count(*) INTO  var_count
 FROM "SCHEMA"."TABLE" 
 WHERE "LAST_UPDATE_DTTM" = :var_lastupddate;

 IF :var_count = 0 THEN
 out_msg = SELECT 'FAILED' AS "STR_FIELD", :var_count AS "INT_FIELD" FROM DUMMY;
 ELSE
 out_msg = SELECT :var_lastupddate AS "STR_FIELD", :var_count AS "INT_FIELD" FROM DUMMY;
 END IF;

END;
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Aug 20, 2017 at 11:17 PM

    "Now, I understand that a procedure commits changes once it exits." - that notion is not correct.

    In HANA Studio, the default session setting for transaction control is AUTOCOMMIT, which means, the HANA server executes a COMMIT after every command. So, when you call a procedure, there will be a COMMIT, but just due to the AUTOCOMMIT.

    If you want to ensure that a COMMIT is executed in your procedure, you have to include it in the code. But be aware that this is not a procedure-local COMMIT, but one that affects the overall transaction. In case you want the procedure to independently COMMIT, consider using an AUTONOMOUS TRANSACTION.

    Add comment
    10|10000 characters needed characters exceeded