cancel
Showing results for 
Search instead for 
Did you mean: 

Odata exit - SQL Procedure COMMIT

BenedictV
Active Contributor
0 Kudos

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;

Accepted Solutions (0)

Answers (1)

Answers (1)

lbreddemann
Active Contributor

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

BenedictV
Active Contributor
0 Kudos

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.

lbreddemann
Active Contributor
0 Kudos

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