Thanks to Bertrand, all 3 problems I described in my previous post about OData have been solved. Our customer has restarted the project and we had a very successfull kickoff seminar.
It seems that we have only one issue left before we can really start to use it (don't we always believe that with new technologies?):
Others than primary key violations, the OData producer doesn't properly forward database error messages to the OData client.
All code belowe assumes that you start the OData Server from the ODataSalesOrders sample that ships with the product. Version is 126.96.36.1993. Create a new C# Console App in VS 2012 Update 4 and add a service reference to the ODataSalesOrders server.
This will raise an unhandled exception because there is already a product with the id 300:
The translation of the german message would be: "An entity instance with this key already exists."
In production code we would try-catch the call to SaveChanges() and forward the error message to the user.
Now let's add a constraint to the database. We expect the quantity between 0 and 1,000.
The database will inform ISQL or other clients (ODBC, ADO.NET) about the problem:
Unfortunately, the OData producer doesn't disclose this vital information. Change the test console application so that it uses a free product id but tries to store a wrong quantity:
We would have expected to see the error information of the database in the DataServiceRequestException. But this is that we get:
Saying that "There was an unexpected error in the producer. Your server administrator has more information."
We need more information here. We need the producer to forward the complete database error message to the OData client, including the SQLCODE.
Behind the scenes:
Here are the relevant lines from the OData producer log file SalesOrdersLog.txt:
The log file in the diagnostics directory is:
The fact that this file is in the diagnostics folder implies that the producer regards and handles database error message as a kind of severe exception. It is not. It's daily business. The same thing happens when there is a trigger that fires a raiserror. We have tons of trigger code in our databases carefully protecting the semantic integrity of our data and all of these triggers raise meaningfull error messages that we forward to the users. I kindly request that the producer simply forwards the SQLCODE and the message to the OData client. Otherwise we do not see how we could continue to use the strong SQL Anywhere SQL language to protect data integrity. Telling end users connected through odata that "there is something wrong with your input" won't do.
Thanks for looking into it,
asked 15 Sep '14, 09:00
Our philosophy has been that all errors from the OData Producer must be in terms of OData lingo (i.e Entities and key sets, etc) thus we translate all anticipated database errors into OData-specific errors. It becomes more difficult to deal with some errors when tables are mapped to different entity set names.
I will discuss this issue with members of our team and get back to you.
answered 15 Sep '14, 09:12
We already have a couple of error messages relating to constraint violations:
I have fixed the interpretation of SQLCODE -209 to generate one of the above messages as appropriate.
This will be available in the next SP with build number >= 2015
answered 18 Sep '14, 15:01