Hello,

I'm currently setting up an OData-Service and have some problems with not null values and default values.

For instance I have the following table created via SQL script:

drop table if exists Stammdaten.Anreden;
create table Stammdaten.Anreden(
   MANDANTNR numeric( 4 ,0) not null default 0,
   GUID uniqueidentifier not null default newid(),
   BEZEICHNUNG not null,
   CREATOR varchar(80) not null default CURRENT USER,
   CREATED timestamp not null default CURRENT UTC TIMESTAMP,
   LASTUSER varchar(80) not null default LAST USER,
   LASTUPDATE timestamp not null default UTC TIMESTAMP,
   constraint PK_Anreden primary key (GUID)
);

The corresponding osdl file for the OData service is quite simple:

service namespace "Engel" {
"Stammdaten"."Anreden" keys ("GUID");
}

Setting up the Service Reference in Visual Studio generates the code to access my Service but when I try to create a new Entity Anreden the corresponding method .CreateAnreden wants all values of the table which are set to be not null. So in my case I need to provide values not only for the GUID and the BEZEICHNUNG but also for the MANDANTNR, CREATED, CREATOR, LASTUSER and LASTUPDATE. This isn't what I really want because those fields should be automatically field by the database itselfs.

So far so good, I decided to change my table/column constraints to allow null values for those fields but also defining a default value for them, so I have use the following SQL script now:

drop table if exists Stammdaten.Anreden;
create table Stammdaten.Anreden(
   MANDANTNR numeric( 4 ,0) default 0,
   GUID uniqueidentifier not null default newid(),
   BEZEICHNUNG not null,
   CREATOR varchar(80) default CURRENT USER,
   CREATED timestamp default CURRENT UTC TIMESTAMP,
   LASTUSER varchar(80) default LAST USER,
   LASTUPDATE timestamp default UTC TIMESTAMP,
   constraint PK_Anreden primary key (GUID)
);

When opening this table in Central and adding a new row those five columns getting the correct default values after saving the new row.

Going back to Visual Studio and updating the Service Reference I get a modified method .CreateAnreden which only wants values for the GUID and the BEZEICHNUNG. So this seems quite nice so far ... till I saved my changes and took a look into the database ... unfortunatly the values for MANDANTNR, CREATOR, CREATED, LASTUSER and LASTUDDATE are all set to NULL.

This is a real showstopper for us. Any help are very much appreciated.

TIA

Armin

asked 25 Sep '14, 05:58

Armin%20Back's gravatar image

Armin Back
86338
accept rate: 0%

forgot to mention the product Version. It is: 16.0.0.2003

(25 Sep '14, 08:35) Armin Back

I believe the issue is that our producer does not emit the DefaultValue facet in the metadata.

There is an issue with the V2/V3 specification (or the interpretation by most clients) in that the DefaultValue facet can only handle constant values as the default. Therefore defaults like CURRENT USER are misunderstood by the clients as being a string constant.

When our producer did emit the DefaultValue facet, clients did the wrong thing so now we document it as not being generated.

Some clients will send NULL or the DefaultValue string instead of not specifying the value for a property when the property is not specified. That’s a problem for defaults like AUTOINCREMENT since "AUTOINCREMENT" is not a valid integer value.

To confirm the behaviour you are seeing. Restart the producer with full verbosity logging (LogFile=MyProducerLog.txt and LogVerbosity=4 in the producer configuration file) and retrieve the metadata using a browser (https://host:port/serviceroot/$metadata). You can then repeat your create request and look at the SQL the producer used in the log.

permanent link

answered 25 Sep '14, 10:44

PhilippeBertrand%20_SAP_'s gravatar image

PhilippeBert...
1.4k41733
accept rate: 22%

edited 25 Sep '14, 10:59

Hi Philippe,

thanks for your answer. I have put together a small recreational for you. You can download the file from:

www.engeldataconcept.de/_special/privat/odata_null_and_default_values.zip

In the ZIP-fiel you will find 3 folders.

In the Clients Folder is a small Visual Studio example showing the described behaviour. In the Database Folder are two sub Folders one for the genereated Database and one for the script files to generate the database. In the script subfolder there is another one for the Initial Setup with a Setup.bat file which will generate the database. And the last Folder is for the ODataServer. After generating the database you can start the ODataServer from there.

BTW, your assumpting is correct that there is no Information in the metadata regarding the default values.

Armin

(25 Sep '14, 11:11) Armin Back

OK, I found a solution or to be more correct a workarround - but not a nice one ;-(.

I allow null values for the colums CREATOR, CREATED, LASTUSER and LASTUPDATE with default values - so not really null values are allowed ;-).

Additionally I define an instead of trigger like:

create or replace trigger "Anreden_TIOIU_CreatedLastUpdate" instead of insert, update
on Stammdaten.Anreden
referencing new as newrow
for each row
begin
     if inserting then
          if (mewrow.Creator is null) then
               insert into Stammdaten.Anreden (MandantNr,GUID,Bezeichnung) values (newrow.MandantNr,newrow.GUID,newrow.Bezeichnung);
          else
               insert into Stammdaten.Anreden (MandantNr,GUID,Bezeichnung,Creator) values (newrow.MandantNr,newrow.GUID,newrow.Bezeichnung,newrow.Creator);
          endif;
     else
          if (newrow.LastUser is null) then
               update Stammdaten.Anreden set MandantNr=newrow.MandantNr,Bezeichnung=newrow.Bezeichnung where GUID=newrow.GUID;
          else
               update Stammdaten.Anreden set MandantNr=newrow.MandantNr,Bezeichnung=newrow.Bezeichnung,LastUser=newrow.LastUser where GUID=newrow.GUID;
          endif
     endif;
end

This works so far but from a maintainence point of view this is a totally nightmare. Changing a table layout means to change the corresponding trigger as well. For this small table it's not really a problem but our OData-Service consist of 55 tables till now (and there are some with over 100 columns).

In my opinion it is not correct to allow null values for these columns from the database point of view. Instead I would like to adjust the OData-Service in the osdl file to allow null values for these columns - but this should mean that the default values from the database always gets used.

I hope these enhancements in the OData-Service will make it into the next service release.

BTW, if you need a beta tester for these enhancements you got my contact-informations ;-)

Armin

permanent link

answered 25 Sep '14, 10:06

Armin%20Back's gravatar image

Armin Back
86338
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×7

question asked: 25 Sep '14, 05:58

question was seen: 1,563 times

last updated: 26 Sep '14, 03:31