HANA XS Core Data Services (CDS) – Troubleshooting, tips and tricks

Today I have decided to create the blog post for people who are struggling with Cloud Platform and HANA XS. My googling skills revealed that the blog posts available here do not tell you important things that you have to know. E.g. there are even mistakes in the code and you should invest your precious time to solve them.

I hope, this blog article will be interesting and helpful for you as it might be interesting for me in cast I forget something. It will be also great if developers read it and improve the product (doesn’t matter which one, but simply improve). By the way, I plan to update the article as long as I will be working with CDS.

Ok. Let us start. I hope searching engines will index this page.

Introduction

Some blogs tell you that XSDS library should be imported into the code, but there are typos. The word XSDS is spelled using small letters, but this library is used with capital letters. So, instead of:

var xsds = $.sap.hana.xs.libs.dbutils.xsds;
var Post = XSDS.$importEntity(...)

I suggest you to write:

const XSDS = $.sap.hana.xs.libs.dbutils.xsds;
var Post = XSDS.$importEntity(...)

or

const XSDS = $.import("sap.hana.xs.libs.dbutils", "xsds");

Why const? Because you do not plan to change the reference to the imported library, do you?

Other bloggers tell that you can pre-generate the Entity metadata, but they don’t tell how to import this library. Here is what I use in my sample:

const XSDS_GEN = $.import("sap.hana.xs.libs.dbutils", "xsds_gen");

xsds_gen – how to generate the entity

Let us assume you have the file something.hdbdd in the package com.sap.test.something:

namespace com.sap.test.something;
@Schema: 'Vlad'
context someContext {
    @Catalog.tableType : #COLUMN
    Entity MyEntry {
        ...
    }
}

To generate the metadata, you have to use the function from the library xsds_gen with the prototype. Please remember that this function returns the string object:

function generateEntity(cdsNamespace, cdsName, fields, options) { }

Note: By the way, this library is not documented in the JSDoc.

This is how you can export the code from your XSJS code:

$.response.status = $.net.http.OK;

// you can use text/plain to get the formatted output, however
// application/javascript is a correct Content-Type value.
$.response.contentType = "application/javascript";

$.response.setBody(XSDS_GEN.generateEntity("com.sap.test.something",
                                           "someContext.MyEntry"));

Possible Errors

• Keep in mind that the context name has to be mentioned. Otherwise you will get the error:

Found the following errors:
===========================
InternalError: dberror(Connection.prepareStatement): 259 - invalid table name: Could not find table/view com.sap.test.something::MyEntry in schema Vlad: line 1 col 21 (at pos 20) (line 76 position 1 in /sap/hana/xs/libs/dbutils/xsds.xsjslib)

This happens, because the table has the name com.sap.test.something::someContext.MyEntry instead of com.sap.test.something::MyEntry. You can check this in HANA Catalog.

As a result, you will get a JavaScript block that you can copy-paste into your package. Don’t forget to refresh the generated file, if you change your CDS-entity.

• The help article of the function $getEntity says that every entity should be imported only once. Otherwise you will get the error. Here is the error that I got and you probably get as well:

Error: XSDS: register: duplicate entity: com.sap.test.something::someContext.MyEntry (line 33 position 1 in enerateEntity@/sap/hana/xs/libs/dbutils/xsds_gen.xsjsli)

Here is the small code block that generates the entity for me when the URL contains the parameter:

if ($.request.method === $.net.http.GET) {
    const action = $.request.parameters.get("action");
    if (action === "gensrc") {
        // . . . . . . .
        $.response.setBody(XSDS_GEN.generateEntity("com.sap.test.something",
                                                   "someContext.MyEntry"));
    } else {
        const MyEntry = XSDS.$importEntity("com.sap.test.something",
                                           "someContext.MyEntry");
        // . . . . . . .
    }
}

CRUD Operations

When the entity is imported, you can create its instances (actual records in the corresponding tables). The code the definitely works (I have tested it) is:

let test1 = new MyEntry();
test1.$save();
$.response.setBody("ok");

Possible Errors

• there is the JSDoc help where the method $save is defined with the mistake:

<static> Entity#$save() → {boolean}

The method doesn’t return anything. Here is the method’s prototype (taken from the corresponding class. Yes, you can open HANA packages in WebIDE/HANA Studio and read the code):

Entity.prototype.$save = function(tx) { ... }

The optional parameter is tx – the object of type .xsds.Transaction. If you do not provide it, the entity will be immediately persisted into the database. The method doesn’t return anything, that is why the code above can be adjusted with the comment:

test1.$save(); // no argument, no return.

If there is an exception, you have to handle it with try … catch.

Default literals

I am using HANA XS in Cloud (the version used in this chapter is 1.00.112.04.1467296086) and my idea was to implement the default value for the date field (UTCDateTime). Based on the help article CDS Entities it is allowed to enter literals only.

 Entity MyEntry {
     ...
     success: Boolean default FALSE;
     startDate: UTCDateTime default CURRENT_TIMESTAMP;
 };

The table in HANA Catalog has the default value for the particular field:

The INSERT statement is able to generate the default value for the field startDate if you execute it in the following manner:

INSERT INTO "PUBLIC"."test.cds::myContext.MyEntry" ("id") VALUES (23)

The result is:

17:47:44 (SQL Editor) Statement 'INSERT INTO "PUBLIC"."test.cds::myContext.MyEntry" ("id") VALUES (23)'

successfully executed in 5 ms - Rows Affected: 1

Possible Errors

• when you create the entity in the way how I showed this above, you will be surprised that this field will not be initialized:

To find out why, put the breakpoint at the line 1185 (xsds_queries.xsjslib) and check what the function serializeInsert(sql) returns. In my case it was the INSERT statement below:

INSERT INTO "test.cds::myContext.MyEntry" ("startDate", "success", "quantity", "name", "id") VALUES (?, ?, ?, ?, ?)

I do not want to waste your time with other findings. But finally I have found the line 1334, where CDS inserts NULL values for undefined JavaScript fields. In my case, all fields were initialized with NULL values.

For your information, the SQL statement below fills the default Integer field only:

INSERT INTO "PUBLIC"."test.cds::myContext.MyEntry" ("id", "startDate") VALUES(24, NULL);

CDS Syntax

This chapter has a goal to review some syntax-specific mistakes that you can accidentally make. There is no much info in Internet that is why you are here.

Boolean Fields

Let us imagine that you want to create a boolean field in your .hdbdd file:

Entity MyEntry {

    ...

    success: Boolean;

};

According to CDS Primitive Data Types the field should accept Boolean values such as true/false.

Possible Errors

• the field is not initialized when the new entity is created. The corresponding table field is NULL. Here is the code block that you probably have:

 const MyEntry = XSDS.$importEntity("...", "...", {

    success: {

        $init: false

    }

 });

 let test1 = new MyEntry();

 test1.$save();

This happens, because the field doesn’t accept Boolean values. To find this out, change the code into the snippet below, and you will see an interesting result:

...

 success: {

    $init: function() { return false; }

 }

...

The result will be:

Error: PreparedStatement.setString: expected string for second argument, but got: boolean (line 1291 position 1 in /sap/hana/xs/libs/dbutils/xsds_queries.xsjslib)

In other words, this field has got the boolean value instead of string. Strange, but let us investigate why. After some debugging I found out that there is no BOOLEAN type in the $.db.types, and by default HANA expects string values. Here is what you can use: “false”, “False”, “FaLsE”, “TRUe”, “1” and “0”.

If you do not want to use JavaScript to initialize Boolean fields, you can change the CDS table definition file in the following manner:

context myContext {

    Entity MyEntry {

        ...

        success: Boolean default FALSE;

     };

};

Sequences

Another topic worth looking at is Sequence. You can create the sequence either in HANA Catalog, or a plain .hdbsequence file. This is useful, because you do not need to remember CREATE SEQUENCE statement, but you have to know the file syntax.

E.g. here is my simple sample_sequence.hdbsequence in the package test.cds:

schema= "VLAD";

depends_on=["test::myContext.MyEntry"];

Some entity is using it, I will not show it here. According to the documentation above, you can specify the sequence as $key value:

const MyEntry = XSDS.$importEntity("test.cds", "myContext.MyEntry", {

    id: {

        $key: "test.cds::sample_sequence"

    }

});

Looks simple, isn’t it? The same code works well with SEQUENCE created in SQL.

Possible Errors

• this code fails with the error below:

InternalError: dberror(Connection.prepareStatement): 257 - sql syntax error: incorrect syntax near "::sample_sequence": line 1 col 13 (at pos 13)

To find out why this error happens, you have to put the breakpoint at the line 1327 (xsds_queries.xsjslib) and check the argument:

1327: var stmt = conn.prepareStatement(sqlString);

Here is what I have in my debugger:

sqlString = 'SELECT test.cds::sample_sequence.NEXTVAL AS "NEXTVAL" FROM DUMMY "0"'

If you take the SELECT statement and evaluate it in HANA Catalog, you will get the error:

14:08:41 (SQL Editor) Could not execute 'SELECT test.cds::sample_sequence.NEXTVAL AS "NEXTVAL" FROM DUMMY "0"'

Error: (dberror) 257 - sql syntax error: incorrect syntax near "::sample_sequence": line 1 col 12 (at pos 13

This happens, because the name is not in double quotes (single quotes do not work, please remember this). E.g.

SELECT "test.cds::sample_sequence".NEXTVAL AS "NEXTVAL" FROM DUMMY "0"

To fix this error, you have to wrap the key field into double quotes. This is important, because for JavaScript there is no difference between quotes. You can nest them inside. But the XS code expects double quotes only. This is how you can fix it:

const MyEntry = XSDS.$importEntity("test.cds", "myContext.MyEntry", {

    id: {

        $key: "\"test.cds::sample_sequence\""

    }

});