OData Service Definition Modification Exist for Tables with an IDENTITY Column

Recently I was using a table in HANA with IDENTITY column that was just added in HANA in SP08. Lars Breddemann has a great blog post on that here. My table had a PK ID that was IDENTITY field which HANA would create automatically incremented on INSERT (ie. PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY).

The SQL for the CREATE looked like this:

CREATE COLUMN TABLE SENSOR_READING (

   SENSOR_READING_ID INTEGER   PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,

   SENSOR_ID         INTEGER   NOT NULL,

   READING           DOUBLE    NOT NULL,

   READ_DATE         TIMESTAMP NOT NULL

);

I initially used a very basic mapping in my sensor_reading.xsodata file:

[sensor_reading.xsodata]

service namespace “workshop.workshop_i826714″{

  “I826714″.”SENSOR_READING” as “sensorReading”;

}

This normally works great and the OData Service definition is simple and short. Initially I tried to do POST/CREATE to the OData service with values for only 3 of 4 columns (SENSOR_ID, READING, READ_DATE) as I assumed the PK (SENSOR_READING_ID) would be auto-generated.

Unfortunately, when testing this out I was receiving “400 Bad Request” errors. The full response is below:

<?xml version=”1.0″ encoding=”utf-8″ standalone=”yes”?>

<error

    xmlns=”http://schemas.microsoft.com/ado/2007/08/dataservices/metadata“>

    <code/>

    <message xml:lang=”en-US”>The serialized resource has an missing value for member 'SENSOR_READING_ID'.</message>

</error>

This happens because I was sending the JSON (3x columns only):

{

    “SENSOR_ID”: 1,

    “READING”: “10”,

    “READ_DATE”: “/Date(1425487218000)/”

}

What the oData service wanted was this JSON (all 4x columns) regardless if one of the columns in the table was marked as IDENTITY.

{

   “SENSOR_READING_ID” : 1,

    “SENSOR_ID”: 1,

    “READING”: “10”,

    “READ_DATE”: “/Date(1425487218000)/”

}

*** NOTE: I also tried sending a <NULL> thinking that maybe the service would replace it on CREATE, but that didn’t work either. Besides having the column/value enumerated in the JSON, since the column was marked as PRIMARY KEY, it also couldn’t be <NULL>. It had to be not null value. ***

After some searching for some ideas from other HANA/OData experts what I was resorted to was using Modification Exit with XS JavaScript via the OSDL in my *.xsodata file. In this manner I was able to send the IDENTITY column (SENSOR_READING_ID) with a ‘dummy value’ and then in *.xsodata file call out to JavaScript function to do SQL INSERT locally, ignoring the ‘dummy value’.

The final *.xsodata file looked like this with the OSDL Syntax for “using” keyword. (The documentation I believe says it can be used for ‘create’, ‘update’ and ‘delete’. I only tried ‘update’).

[sensor_reading.xsodata]

service namespace “workshop.workshop_i826714″{

  “I826714″.”SENSOR_READING” as “sensorReading” create using “workshop.workshop_i826714a:sensor_reading_create.xsjslib::sensor_reading_create”;

}

The OSDL syntax to bind XS JS to specific entity modification is:

<Package.Path>:<file>.<suffix>::<XSJS_FunctionName>

So a sample would look like this:

service { “sample.odata::table” as “Table” update using “sap.test:jsexit.xsjslib::update_instead”;}

In the *.xsjslib file, the XS JS function gets the single row table in the param and then builds a prepared statement to INSERT into the table. The param has a propery “afterTableName” which we use to columns values after temporary table name.

[sensor_reading_create.xsjslib]

function sensor_reading_create(param) {

    let after = param.afterTableName;

    let pStmt = param.connection.prepareStatement(‘select * from “‘+after+'”‘);
    pStmt.executeQuery();
    var rs = pStmt.executeQuery();

    if (rs.next()) {

        pStmt = param.connection.prepareStatement(‘insert into “I826714”.”SENSOR_READING”(“SENSOR_ID”, “READING”, “READ_DATE”) values(?, ?, ?)’);

        pStmt.setInteger(1, rs.getInteger(2));         
        pStmt.setDouble(2, rs.getDouble(3));           
        pStmt.setTimestamp(3, rs.getTimestamp(4));     

        pStmt.executeUpdate();
        pStmt.close();

        }

    rs.close();

}

Really this just allows me to call SQL INSERT in XS JS instead of using the automatic *.xsodata service mapping.

The ADVANTAGES:

• Code works with IDENTITY columns. It gets by the OData column/entity requirement issues.
• Developers can also add in more custom logic and SQL injections

The DISADVANTAGES:

• PK column dummy value must be sent to server. Extra overhead/resources wasted over the wire.
• Extra overhead @ HANA to pause and call-out XS JS function. Not sure exactly how much, but I assume it’s going to be slower.
• A lot more code/work if you want crate OData Service Definition for tables with IDENTITY column

In summary, this will work, but not sure it’s ideal. I’m wondering if anyone knows of a better solution, such as a OSDL keyword that would allow columns to be tagged as not required on the POST/CREATE or POST/UPDATE for OData service. This would save a lot of hassle of creating your own XS JS function. If you know of solution, please reply in the comments!

If anyone has any corrections, suggestions or other feedback, please post as well.

Thanks,

w.

You can find more about Modification Exists with XS JavaScript and OSDL in general in Section 7.1.6 in the SAP HANA Developer Guide.

Special thanks to David Fishburn and Marcus Pridham who also helped me debug this.