How to get the ID of an inserted record in HANA back to SAPUI5 application?

[former_member563095]

Hi,

I got an SAPUI5-application in which I create entries and save them via OData-service. That works, the code for the create operation can be found below. What I have to do now is, I need the ID of the inserted record in HANA back in my application. So what I did I implemented a success handler and thought I would get back this ID in the response from my OData-service. But that is not the case, I get back the same value I provided, in this case 0 since this just a dummy value for the OData-service. I did some research on how to tackle this problem, but none of the approaches worked. So I hope someone of you can help me out or got a hint how to do this. Below the code for create-operation, odata-service and the create.xsjs:

Kind regards

Maximilian

Create-operation in UI5-application:

this.getOwnerComponent().getModel("Mitarbeiter").create("/ARB", oEntry, {
    success: function(oData, response){
        console.log(response);
        }
    });

Definition of OData-service: (xsodata)

service {
"MITARBEITERABTEILUNG"."ABTEILUNG" as "ABT" navigates ("Mitarbeiter" as "ARB");
"MITARBEITERABTEILUNG"."Mitarbeiter" as "ARB" create using "public.MitarbeiterAbteilung:mitarbeiterMethods.xsjslib::mitarbeiterCreate";
association "Mitarbeiter"
principal "ABT"("ID")
multiplicity "1"
dependent "ARB"("Abteilung")
multiplicity "*";
}

Create-method in OData-service (xsjslib)

function mitarbeiterCreate(param) {
let aAfterTableName = param.afterTableName;
var oEntry = {};
try {
var statement = param.connection.prepareStatement("SELECT * FROM\"" + aAfterTableName + "\"");
var rs = statement.executeQuery();

var statement2 = param.connection.prepareStatement('SELECT "MITARBEITERABTEILUNG"."MASEQUENCE".NEXTVAL from dummy');
var rs2 = statement2.executeQuery();
while (rs2.next()) {
oEntry.ID = rs2.getInteger(1);
}

statement2.close();
while (rs.next()) {
oEntry.Name = rs.getString(2);
oEntry.Adresse = rs.getString(3);
oEntry.bz = rs.getString(4);
oEntry.Abteilung = rs.getInteger(5);
}
statement = param.connection.prepareStatement('INSERT INTO "MITARBEITERABTEILUNG"."Mitarbeiter" VALUES (?,?,?,?,?)');
statement.setInteger(1, oEntry.ID);
statement.setString(2, oEntry.Name);
statement.setString(3, oEntry.Adresse);
statement.setString(4, oEntry.bz);
statement.setInteger(5, oEntry.Abteilung);
statement.execute();

statement.close();
} catch (e) {
statement.close();
}
<br>

[pfefferf · Accepted solution]

The only thing you need to do, is to update the ID value in the "afterTableName" table provided by the function parameter. Something like that:

let statement = param.connection.prepareStatement('update "' + param.afterTableName + '" set ID = ' + oEntry.ID);
statement.executeUpdate();

Of course that needs to be done after you have determined your new ID. In case your column name is not "ID", please replace it with the real column name.

Regards,
Florian

[former_member563095 · Accepted solution]

Good Morning Florian,

thank you for your kind explanation and the piece of code you provided. I just tested it and it works like a charm. After seeing the code and your explanation it is clear to me why this works and why it did not work the ways I tried it before. Thank you again!

Kind regards

Maximilian

[former_member236392 · Accepted solution]

Hi guys,

The described approach didn't work for me on XSA SP04. I received error "feature not supported: update statement for volatile table" on a direct update of the after table. So I used the 'truncate + insert' combination described here. That one worked.

Thanks,
Anton

[brian_keenan]

Have you tried putting a return value in your xsjs? something like...

@function mitarbeiterCreate(param) {
..
..
return {id: oEntry.ID, msg:"The id was created successfully"};
}

[former_member563095]

Hi,

thanks for your answer. I tried your suggestion, the devtools of chrome are logging the following:

"log-dbg.js:412 2018-07-11 16:08:12.408500 The following problem occurred: HTTP request failed400,Bad Request,{ "error": { "code": "", "message": { "lang": "en-US", "value": "Custom error occurred. See error details."},"innererror":{"errordetail":{"msg":"The id was created successfully"}}}}"