on 08-11-2017 7:22 PM
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;
"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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.