Skip to Content

HANA oData create procedure - created NEXTVAL missing

Oct 31, 2017 at 09:24 AM


avatar image

Hello Experts,

We have an UI5 app which contains multiple CRUD calls. I create a new item via a pop-up (enter some details and click OK). With my JSON data for the new entity, I then call 'create' on my oData model. The oData entity (as defined in my xsodata service) uses a stored proc to create db records, and uses a sequence to generate the next ID (NEXTVAL). Up to this point, all is well. The record is created, and my success callback is executed. However, what I now need is to have the new record's ID in my response payload, but I cannot manage to get it.

How do I get that NEXTVAL value into my response payload, so it can be accessed in the success calback of the model's create function? Surely there is an easy way to do this? The key of the inserted record is paramount. I don't want to create the ID on the client side, as it won't be accurate - I need it to be returned from the INSERT.

The oData call is only a link with basic auth and a JSON body. This is the code for the CREATE procedure in the db:

PROCEDURE "XXXXXXX"."XXXXXX.xsjs.procedures.public::kpiTargetCreate" ( 
	    IN in_kpiTarget "XXXXXX.xsjs.database.model::types.kpitarget_editable_fields",
	    OUT out_return_message "XXXXXX"."XXXXXXX.xsjs.database.model::types.return_message"
	--DEFAULT SCHEMA <schema>   

declare lv_customerId Integer;
declare lv_role String;
declare lv_kpiTargetId Integer;
declare lv_kpiId Integer;
declare lv_periodKey String;
declare lv_entityId Integer;
declare lv_minimumValue Decimal;
declare lv_belowTargetThreshold Decimal;
declare lv_targetValue Decimal;
declare lv_aboveTargetThreshold Decimal;
declare lv_maximumValue Decimal;
declare lv_isRecurring Integer;
declare lv_targetKpiCustomerId Integer;
declare lv_targetEntityCustomerId Integer;
SELECT "customer_id", "role" INTO lv_customerId, lv_role FROM "_SYS_BIC"."XXXXXXXX.xsjs.calcviews.dim/CA_GRA_ENTITY" WHERE "user"=SESSION_USER; SELECT "kpi_id", "period_key", "entity_id", "minimumValue", "belowTargetThreshold", "targetValue", "aboveTargetThreshold", "maximumValue", "isRecurring" INTO lv_kpiId, lv_periodKey, lv_entityId, lv_minimumValue, lv_belowTargetThreshold, lv_targetValue, lv_aboveTargetThreshold, lv_maximumValue, lv_isRecurring FROM :in_kpiTarget; SELECT "" INTO lv_targetKpiCustomerId FROM "XXXXX"."XXXXX.xsjs.database.model::tables.Kpi" WHERE "id"=:lv_kpiId; SELECT "" INTO lv_targetEntityCustomerId FROM "XXXXX"."XXXXXX.xsjs.database.model::tables.Entity" WHERE "id"=:lv_entityId; if (:lv_role <> 'champion') then out_return_message = SELECT 406 AS "httpStatusCode", 'Unauthorized to create KPI' AS "message", 'User must be a champion to create new kpi targets' AS "detail" FROM DUMMY; elseif (:lv_targetKpiCustomerId<>:lv_customerId) then -- Trying to insert kpi target for different customer's kpi out_return_message = SELECT 401 AS "httpStatusCode", 'Unauthorized' AS "message", 'Tried to insert unauthorized data' AS "detail" FROM DUMMY; elseif (:lv_customerId <> :lv_targetEntityCustomerId) then -- Trying to create kpitarget to an entity of a different customer out_return_message = SELECT 403 AS "httpStatusCode", 'Unauthorized' AS "message", 'Not authorized to create priority for specified entity' AS "detail" FROM DUMMY; else -- Create new kpi target SELECT "XXXXXX"."XXXXXX.xsjs.database.model::kpiTarget_id".nextval INTO lv_kpiTargetId FROM DUMMY; INSERT INTO "XXXXXX"."XXXXXX.xsjs.database.model::tables.KpiTarget" VALUES ( :lv_kpiTargetId /*id <INTEGER>*/, :lv_kpiId /* <INTEGER>*/, :lv_periodKey /*period.key <STRING>*/, :lv_entityId /* <INTEGER>*/, :lv_minimumValue /*minimumValue <DECIMAL>*/, :lv_belowTargetThreshold /*belowTargetThreshold <DECIMAL>*/, :lv_targetValue /*targetValue <DECIMAL>*/, :lv_aboveTargetThreshold /*aboveTargetThreshold <DECIMAL>*/, :lv_maximumValue /*maximumValue <DECIMAL>*/, :lv_isRecurring /*isRecurring <INTEGER>*/, :lv_customerId /* <INTEGER>*/, 0/*isClosed <VARCHAR(1)>*/, -- Should not be closed upon creation '9999-12-31' /*closedDate <DATE>*/, CURRENT_TIMESTAMP /*createdOn <SECONDDATE>*/ ); end if; end;
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

0 Answers