SAP SQL Anywhere

Question

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

Answer 1

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.

Answer 2

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

SAP SQL Anywhere

OData: Not null values and default values

Follow this question

Related questions