Beware: CSD field’s name case always matter (HANA really hates lowercase)

#tipoftheday will be a series of blog post that I’m mirroring from my Medium account.

In my free time I‘m playing around SAP HANA CDS (Core Data Services) to create a full backend/frontend scenario for a TDD (Test Driven Development) example.

I come from languages/syntax where you are used to only use lowercase/camelcase names for variables, attributes, fields and so on and in all online examples (of developing on HANA CDS/Procedures) everyone use a mixed syntax (someone use only uppercase, someone use lowercase).

When you define your xsodata that bind http requests to your CDS, the JSON structure in Input and Output use the same fields name defined on the CDS. I dislike to see JSON in Camelcase/Uppercase so I thought that I could stick with my ol’ good syntax habit (TLDR: I was damn wrong).

Here’s an example of a simple CDS structure:

namespace dev.tdd_bulletin.db.src.data;
@Schema: 'ROOT'

context Post {

    Entity Posts { 
        key post_id:        Integer;
        create_timestamp:   UTCDateTime;
        author:             String(50);
        title:              String(100);
        description:        String(500);
        is_open:            Integer;
    }
    
    Type RequestErrors {
        HTTP_STATUS_CODE : Integer;
        ERROR_MESSAGE : String(100);
        DETAIL : String(100);
    };

}

I think that it’s pretty much straightforward as an example, there’s no explanation needed.

post_id uses a Sequence to be initialized and valorized do I created a custom exit in my xsodata do do that:

service {
    "ROOT"."dev.tdd_bulletin.db.src.data::Post.Posts" as "PostSet"
        create using "dev.tdd_bulletin.db.src.procedure::create_post";
}

And here you go with the HDB Procedure’s code. Please be kind, this is my first time playing around with this new tools so if you have any kind of suggestions and criticism don’t mind to express yourself 😉

PROCEDURE ROOT."dev.tdd_bulletin.db.src.procedure::create_post" ( 
        IN row ROOT."dev.tdd_bulletin.db.src.data::Post.Posts",
        OUT error ROOT."dev.tdd_bulletin.db.src.data::Post.RequestErrors"
    )
   LANGUAGE SQLSCRIPT
   SQL SECURITY INVOKER
   DEFAULT SCHEMA ROOT
   AS
BEGIN

    declare lv_id integer;
    declare lv_now TIMESTAMP;
    declare lv_title string;
    declare lv_description string;
    declare lv_author string;
    declare lv_open integer;
   
    select post_id, create_timestamp, author, title, description, is_open
        into lv_id, lv_now, lv_author, lv_title, lv_description, lv_open
        from :row;
        
    SELECT CURRENT_TIMESTAMP into lv_now from dummy;
        
    if :lv_author = '' then
        error = select 500 as http_status_code,
            'No Author' as error_message,
            'Author is empty' as detail from dummy;
    elseif :lv_title = '' then
        error = select 500 as http_status_code,
            'No Title' as error_message,
            'Title is empty' as detail from dummy;
    elseif :lv_description = '' then
        error = select 500 as http_status_code,
            'No Description' as error_message,
            'Description is empty' as detail from dummy;
    else
    
    
        insert into ROOT."dev.tdd_bulletin.db.src.data::Post.Posts"
            values (
                ROOT."dev.tdd_bulletin.db.src.data::PostId".NEXTVAL,
                lv_now,
                lv_author,
                lv_title,
                lv_description,
                1
            );
        
    end if;
   
END

Anyway, if you try to save (and activate it) the Web Workbench will give you this nice error:

[17:54:02] Error while activating /dev/tdd_bulletin/db/src/procedure/create_post.hdbprocedure:
[dev.tdd_bulletin.db.src.procedure:create_post.hdbprocedure] Could not create catalog object: invalid column name; POST_ID: line 18 col 12 (at pos 495)

invalid column name; POST_ID

As you can see in the first gist above, the field name inside my CDS Entity is entirely lowecase. After some online reading and a good cup of coffee (because as you know you cannot think correctly without it) I understood that you have two options to solve this problem (and personally I don’t like both):

1. You must define all your entity’s fields in uppercase
2. You must doublequote all fields in your procedure (and God-only-knows where else) [side note: maybe this solution is uglier and more timeconsuming that having in your input/output OData JSON everything uppercased]

So beware of it: HANA does not like lowercase things, it does not like it at all! #tipoftheday #SAPHANA #HANA