The forum will be down for maintenance over the weekend of August 18-20, 2017. The forum will be shut down on the evening (EDT) of Friday, August 18. Downtime is unknown but may be up to two days. The forum will be restarted as soon as maintenance is complete.

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

asked 15 Sep '14, 09:00

Michael%20Fischer's gravatar image

Michael Fischer
630111527
accept rate: 12%


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.

permanent link

answered 15 Sep '14, 09:12

PhilippeBertrand%20_SAP_'s gravatar image

PhilippeBert...
1.5k41735
accept rate: 22%

1

Philippe,

lightning speed, as always. I made a demonstration for a software company last week, comparing SA and MS SQL Server, and the support of you guys was one of the pros for SA.

Concerning the OData lingo: Maybe you could put db-specific constraint violations and raiserrors into a miscellaneous error category with a dedicated error code and for these errors just forward the SQLCODE in braces and the message text in the message tag of the response? Just an idea.

(15 Sep '14, 10:03) Michael Fischer

We already have a couple of error messages relating to constraint violations:

30144=The update entity request failed because it caused a constraint violation 
30145=The insert entity request failed because it caused a constraint violation
30146=The delete request failed because it caused a constraint violation

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

permanent link

answered 18 Sep '14, 15:01

PhilippeBertrand%20_SAP_'s gravatar image

PhilippeBert...
1.5k41735
accept rate: 22%

If this solution is satisfactory, please accept the answer.

(25 Sep '14, 10:13) PhilippeBert...
1

Hi Philippe,

PMFJI but I don't think that this isn't enough ... Keep in mind that it is possible to raise individual error messages in SQL to inform the client with the needed information what is going wrong. And these informations/error messages should be forwarded to the OData-Client as well.

Armin

(25 Sep '14, 11:47) Armin Back
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:

×10

question asked: 15 Sep '14, 09:00

question was seen: 4,078 times

last updated: 25 Sep '14, 11:47