CODEWITHSUNDEEP
hanasapui5
hagen
hagen

Reputation: 141

HANA OData create procedure - where is my new ID?

In my UI5 app, I have a list of items. 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. 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. Here is the JS code running the create, with the success/error callbacks:

// Update the model and add our new forecast
    this.getView().getModel().create('/Forecasts', oData, {
        success : jQuery.proxy(function(mResponse) {
            // nav to newly created Forecast
            this.getRouter().navTo("forecast", {
                forecastId: mResponse.ID, // EMPTY!
                tab: "data"
            }, false);
        }, this),
        error : jQuery.proxy(function(mResponse) {
            this.showErrorAlert("Problem creating new forecast");
        }, this)
    });

mResponse.ID is always empty. I figure, it shouldn't be. ID is defined as a key in the entity forecast.

@Catalog.tableType: #ROW
entity forecast {
    key ID: IdType;
    NAME: NameType;
    CREATED: UTCDateTime;
    USER: UsernameType;
    EFFECTIVE: LocalDate;
    DAYS: Integer;  
};

And the xsodata service (schema and namespace references removed):

service {

    // Forecasts
    "<namesapce>.data::models.forecast" as "Forecasts" 
    create using "<namesapce>.procedures::createForecast";
}

Here is the stored proc, performing the insert:

PROCEDURE "<schema>"."<namespace>.procedures::createForecast" ( 
    IN intab "<schema>"."<namespace>.data::models.procedures.forecast",
    OUT outtab "<schema>"."<namespace>.data::models.procedures.errors"
) 
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
-- DEFAULT SCHEMA <schema>
-- READS SQL DATA AS
BEGIN
    DECLARE lv_name string;
    DECLARE lv_days integer;

    SELECT NAME, DAYS INTO lv_name, lv_days FROM :intab;

    -- Now validate and insert
    IF :lv_name = '' THEN
        outtab = SELECT 500 AS http_status_code,
                    'Empty Forecast name' AS error_message,
                    'The Forecast name cannot be empty' AS detail FROM dummy;
    ELSEIF :lv_days = 0 THEN
        outtab = SELECT 500 AS http_status_code,
                    'Empty Forecast days' AS error_message,
                    'The Forecast days cannot be empty' AS detail FROM dummy;
    ELSE
        -- Perform the INSERT
        INSERT INTO "<namespace>.data::models.forecast"
        VALUES ("<namespace>.data::forecast".NEXTVAL, 
                lv_name,
                CURRENT_TIMESTAMP,
                CURRENT_USER,
                CURRENT_DATE,
                lv_days);
    END IF;
END;

How do I get that NEXTVAL value (now CURRVAL) into my response payload, so it can be accessed in the success calback of the model's create function? I've tried multiple out tables, but encounter constant problems. 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.

Upvotes: 3

Views: 2963

Answers (3)

zygimantus
zygimantus

Reputation: 3777

The official documentation for SAP HANA OData Service Definitions (XS Advanced) states that it is not possible to use a sequence to create an entry in a table exposed by an OData service:

Without any support for IN/OUT table parameters in SQLScript, it is not possible to use a sequence to create an entry in a table exposed by an OData service. However, you can use XS JavaScript exits to update a table with a generated value.

Upvotes: 0

Ank
Ank

Reputation: 402

We face when we implement transactions where we need to make sure that any transactions are run correctly. We do it by first going for an Update and then reading the record. If we have properly read the record which we updated then that means it is updated in the backend. While the time we update and read we should lock the table so inconsistency should not come up. If we are not able to read then update has not happened. Also internally, DB have latency understand that update can take time and if your queries are Async then you might not get the data back, so make sure the Sequence steps are in Sync.

Changes in sequence in this particular case :

  1. OUT outtab "".".data::models.procedures.errors" should be the schema record not errors table
  2. Take the timestamp in the procedure
  3. Apply lock to the Table ".data::models.forecast"
  4. After Insert Add Select With Most Recent Value
  5. If the timestamp matches with select record all ok
  6. Return error if timestamp don't match
  7. Make the outtab the newly selected record

Upvotes: 0

Prabhu
Prabhu

Reputation: 59

POST request to HANA Odata service will return the created object's data in the response with status 201 if it is successful. So from the response, you can get the ID of the new inserted data.

Upvotes: 1

Related Questions