Can't Insert into CDS Entity as expected

[Former Member]

I have created the following in a Hana 2.0 Express instance using the XS Advanced Web IDE. This builds fine and is available in the Catalog views.

context Survey {
entity SurveyDefinition {
        key id          : entityID generated always as identity(start with 1 increment by 1);
            description : String(100) not null;
            created : UTCDateTime default CURRENT_UTCTIMESTAMP null;
            validfrom   : UTCDateTime default CURRENT_UTCTIMESTAMP null;
            validto     : UTCDateTime null;
            questions   : association[*] to SurveyQuestionAssignment on questions.surveyid = id;
    }
}

Note: i have tried both simply having default settings and having default + null settings for `created` and `validfrom`.

I would expect the following SQL Statement to work for this table as the first key cannot be provided and every other attribute is allowed to be empty.

INSERT INTO "MYNAMESPACE::Survey.SurveyDefinition" VALUES ('2017 Survey');

as there is only one field that requires a value and isn't pre-filled, but it does not. It give the error "ERROR: (dberror) not enough values"

As a bonus, when i try to use the Graphical table entry for this, it blanks out all the fields and gives the opposite message. "ERROR: (dberror) too many values."

It works when I give the following instead, but now I have null values that show up as '?' in the data viewer instead of the two default timestamps described by the CDS.

INSERT INTO "MYNAMESPACE::Survey.SurveyDefinition" VALUES ('2017 Supplier Survey',null,null,null);

This also works but completely defeats the purpose

INSERT INTO "SNICS.snicsdb::Survey.SurveyDefinition" VALUES ('2017 Supplier Survey',CURRENT_UTCTIMESTAMP,CURRENT_UTCTIMESTAMP,null);

So how can i provide an insert that allows for the default values i've provided in my CDS definition? Also what impact does this behavior have on generated fields.

[pfefferf · Accepted solution]

You have to define the description column in which you wanna insert the description value. W/o that, the statement does not know for which column the value is.

Using

INSERT INTO "MYNAMESPACE::Survey.SurveyDefinition" ("description") VALUES ('2017 Survey');

inserts one line with the description value and adds the default values to the columns for which you have one defined.

Regards,
Florian