Skip to Content
0

Odata exit - SQL Procedure COMMIT

Aug 11, 2017 at 06:22 PM

125

avatar image
Former Member

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;
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Lars Breddemann
Aug 20, 2017 at 11:17 PM
1

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

Show 2 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Thanks Lars, for the correction. An BEGIN-END block would work.

But my original question was why the AUTOCOMMIT after a procedure execution does not commit my insert to the table? But a SELECT inside the procedure, from the table, works.

0

So, you're saying that you run the procedure in autocommit mode in HANA studio, but the insert is not comitted?

0