SAPUI5 Hana Odata v2

[former_member597255]

Hello,

I use Hana server with SAPUI5, I always have this problem when I run create method, but Delete, Read and upadate do well.

Can you help me, if I can do it.

I thank you

[former_member607988]

Hi all,

just to let you know: That is (was?) a common issue at least until 2015: https://blogs.sap.com/2015/03/06/odata-service-definition-xsodata-for-tables-with-an-identity-column... In this post a developer has talked to experienced HANA-Experts who told him that he has to create a xsjs-Script doing the insert, because the ODATA-Services - somehow - do not support the Job.

Is this still the case? Sounds like a bug to me?

An explanation for that behaviour would be really nice.

Kind regards

Marc

----

Update, 2019-04-12:

As you might have suggested, I'm a colleague of Rakan. We've now accepted that it migh not be possible to use an auto-increment ID-field combined with an odata-service with the above configuration. We're now working on a workaround: We want to use a procedure to generate an id-value. Of course the prefered way would be to use a sequence. The problem is that this might not be possible:

„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.“ 
(Source: https://help.sap.com/viewer/4505d0bdaf4948449b7f7379d24d0f0d/2.0.00/en-US/b8900dfa26ea48bd9a6596612a... 

According to that documentation, it is not possible to use a sequence in an Odata-Service-Definiton / Stored Procedure. Just to let you know, Thomas Jung (SAP-coworker) has published the following youtube-video in 2013, where it seems to be possible to use stored procedures pretty well: https://www.youtube.com/watch?v=c41anxrDleg

To check if we get a procedure to work, we've created one per hdbprocedure:

path: RH_Orga_1/Orga_DB/procedure/setDBEntry.dhbprocedure

PROCEDURE "RH_Orga_1.Orga_DB.procedure::setDBEntry" ()
 LANGUAGE SQLSCRIPT SQL
 SECURITY INVOKER AS
 --READS SQL DATA AS
BEGIN
 INSERT INTO "table" (
 	"table"
 ) VALUES (
 	'Hello World'
 ); 
END;

The service calls that procedure like this:

service {
    "Orga.firma2" as "firma2" 
    create using "RH_Orga_1.Orga_DB.procedure::setDBEntry";
} 

We do now get the following error if we do a create:

Request:

POST https://xsa.realcore.local:51056/xsodata/Firma2.xsodata/firma2

Body: { "FirmaID": "12345", "FirmaName": "Test1", "Bezeichnung": "Test2" }

Response:

Error while doing a DB Query (Error-Code: "500")

If the procedure is commented out, the insert works just fine.

We are using the following HANA-version: SPS 03 Patch 0 ( Build 4.3.23 )

What's the matter with the procedures?

Kind regards

Marc

[former_member607988]

Hi,

a solution for this issue has been debated and documented here: https://blogs.sap.com/2017/03/13/how-to-consume-an-odata-service-with-openui5-sapui5/comment-page-1/...

Kind regards

Marc

[SergioG_TX]

when you have the ID (key) with auto increment .. .and you try to insert a new record.. do you pass the ID column in the body of your request? try passing it w a null value so the auto increment does what it needs. then also try without including the ID property in the body of your request to see if that behaves differently. good luck

[former_member597255]

Hi Sergio,

thank you for your fast reply.

My colleague and I have also tried to build the payload like this. Unfortunately we still get an error message if we're posting with ID = null:

{
  "FirmaID": null,
  "Bezeichnung": "test", 
  "FirmaName": "test2" 
}

Response:

{
 "error": {
 "code": 500,
 "message": {
   "lang": "en-US",
   "value": "Error while executing a DB query"
  } 
 }
}

As you can see that does not work. Also sending a "0" does not work. It's because of the fact that the ID field ("FirmaID") has been configured as an identity-field.

Just as said before - It works if I remove the identity-definition from that field. I don't know why it's such a big deal here.

Do you have any additional thoughts? Isn't it possible to use the auto-increment / identity like this with the Hana-DB? That would be really strange. It's a standard-operation.

Kind regards

Rakan

[SergioG_TX]

make sure all the object properties are populated when creating a new object.

if get/delete/update work... create should not be any different other than you are inserting a new record.. do you have your key identified? auto-incremented?

[former_member597255]

Hi Sergio,

we've defined the table like this in our hdbcds:

@Comment : 'Firma'
    entity firma {
            @Comment : 'Firma ID'
        key FirmaID     : Integer generated always as identity(start with 1 increment by 1);
            FirmaName   : Fname;


            @Comment : 'Supplier'
            Bezeichnung : bezeichnung;
    }
    technical configuration {
        column store;
    };

As you can see our ID has been defined as an identity-field (which should increment the id by one after each new entry).

Of course we do also think that the behaviour between rud and c(reate) should not be differten. Still we're facing the following error-message:

{"error":{"code":500,"message":{"lang":"en-US","value":"Error while executing a DB query"}}}

Our service is defined like this:

service{
    "Orga.firma" as "firma";
}

Read, Update and Delete do not cause any issues.

The JS-Call:

var entity = "/firma";
var data = {
	FirmaName: "hallo",
	Bezeichnung: "halalo2e12"
};
FirmaModel.create(entity, 
	data,
	{
	success: function(oData) {
			console.dir(oData);
		}, 
		error: function (jqXHR) {
			console.log("Fehler:Firma_Create" + jqXHR);
		}
	}
);

Server-Log:

4/10/19 2:04:44.000 PM [RTR] OUT   <IP>  - - to <server> "POST /xsodata/Firma.xsodata/firma HTTP/1.1" 500 sent 121 in 178 by <stuff> <br>

Do you have any ideas?

Thank you very much in advance for your support.

Kind regards

Rakan

UPDATE:

We've found out that the issue is somehow related to the definition of the ID-column. If we create a table without the ID, we can insert datasets. If we then readd the identity-field, it throws the "403"-error. Interestingly, there is no such problem while inserting the data via the SQL-console of SAP HANA / WebIDE.

Why?

[former_member749558]

Rakan is rightfully asking why is ID autoincrementation working fine while inserting rows in a table using SQL console in WebIDE for Hana, but the node.js xsodata service generated does not. It's one of the basic features of CRUD operations and its absence generates a lot of additional code just for reading the "next id".

Does anybody have more insight into this issue?