Hello!

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 16.0.0.2003. Create a new C# Console App in VS 2012 Update 4 and add a service reference to the ODataSalesOrders server.

class Program
{
   public const String ODATA_ENDPOINT = "http://localhost:8090/odata/";
   public static ODataSalesOrders.SAPSybaseOData_Container oDataService;

   static void Main(string[] args)
   {
      oDataService = new ODataSalesOrders.SAPSybaseOData_Container(new Uri(ODATA_ENDPOINT));
      var product = ODataSalesOrders.Products.CreateProducts(300, "Test", "Test", "Small", "Black", 1, 10);
      oDataService.AddToProducts(product);
      oDataService.SaveChanges();
   }
}

This will raise an unhandled exception because there is already a product with the id 300:

Unbehandelte Ausnahme: System.Data.Services.Client.DataServiceRequestException: 
An error occurred while processing this request. 
---> System.Data.Services.Client.DataServiceClientException: 
<?xml version="1.0" encoding="utf-8"?>
<error xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">
   <code>30063</code>
   <message xml:lang="en-US">Eine Entitätsinstanz mit diesem Schlüssel ist bereits vorhanden.</message>
</error>

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.

ALTER TABLE "GROUPO"."Products" ALTER "Quantity" ADD CONSTRAINT "ProducsQuantityCheck" check("Quantity" >= 0 and "Quantity" <= 1000);

INSERT INTO "GROUPO"."Products" ("ID","Name","Description","Size","Color","Quantity","UnitPrice") 
VALUES(901,'Test','Test','Small','Black',-1,10)

The database will inform ISQL or other clients (ODBC, ADO.NET) about the problem:

Could not execute statement.
Constraint 'ProducsQuantityCheck' violated: Invalid value for column
'Quantity' in table 'Products'
SQLCODE=-209, ODBC 3 State="23000"

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:

var product = ODataSalesOrders.Products.CreateProducts(901, "Test", "Test", "Small", "Black", -1, 10);

We would have expected to see the error information of the database in the DataServiceRequestException. But this is that we get:

<error xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">
   <code>30000</code>
   <message xml:lang="en-US">Im Producer ist ein unerwarteter Fehler aufgetreten. Weitere Hinweise erhalten Sie vom Serveradministrator.</message>
</error>

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:

R. 2014-09-15 13:07:27. [main] 0:0:0:0:0:0:0:1 - - [15/Sep/2014:13:07:27 +0200] "POST /odata/Products HTTP/1.1" 200 0 "-" "Microsoft ADO.NET Data Services" "-"
R. 2014-09-15 13:07:27. [default:4] Request POST http://localhost:8090/odata/Products
D. 2014-09-15 13:07:27. [default:4] DataServiceVersion 1.0 Antwort 2.0
D. 2014-09-15 13:07:27. [default:4] SQL-Anweisung wird ausgeführt: SELECT i1."ID", i1."Name", i1."Description", i1."Size", i1."Color", i1."Quantity", i1."UnitPrice", i1."Photo" FROM( INSERT INTO "GROUPO"."Products" ("Name", "Description", "Quantity", "ID", "UnitPrice", "Photo", "Size", "Color") VALUES (?, ?, ?, ?, ?, ?, ?, ?)) REFERENCING( final AS i1 );
E. 2014-09-15 13:07:27. [default:4] (30000) Ein unerwarteter Fehler ist im Producer aufgetreten und wurde in die Datei "C:\ProgramData\SQL Anywhere 16\diagnostics\ODP16_20140915_130727_981.txt" geschrieben

The log file in the diagnostics directory is:

Datenbankfehler: "[Sybase][JDBC Driver][SQL Anywhere]Constraint 'ProducsQuantityCheck' violated: Invalid value for column 'Quantity' in table 'Products'".
    at com.sybase.odata.producer.database.sqlanywhere.SASQLExceptionInterpreter.interpret(SASQLExceptionInterpreter.java:75)
    at com.sybase.odata.producer.database.InsertEntry.execute(InsertEntry.java:131)
    at com.sybase.odata.producer.handler.PostHandler.handleInsertEntityRequest(PostHandler.java:211)
...

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,

Michael