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:
The corresponding osdl file for the OData service is quite simple:
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:
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.
asked 25 Sep '14, 05:58
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.
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:
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 ;-)
answered 25 Sep '14, 10:06