a while ago, we reported the problems when generating Service References in Visual Studio to the SQL Anywhere OData Server. This has been fixed and now we are proceeding on our OData journey.
First of all, let me tell that the basic implementation architecture is like we know and love SQL Anywhere: simple, straight-forward and yet very powerfull. Having said that, here are the current issues that keeps us from using it. Which hurts because we have a customer waiting for it.
All code belowe assumes that you start the OData Server from the ODataSalesOrders sample that ships with the product. Version is 18.104.22.1684. Create a new C# Console App in VS 2012 Update 4 and add a service reference to the ODataSalesOrders server.
I can read strings with umlauts from the database but I can't write them.
First, in ISQL, I translate one expense type into German. Note the Umlaut in 'Gebühr'.
Now let's iterate through the FinancialCodes.
The program correctly displays the german description. But when I change the description from C# (and it contains an Umlaut), I run into trouble.
After that, the description in the database is 'ServicegebÃ¼hr'. Both ISQL and the C# client show the scrambled Umlaut.
I understand that my column in the database is just a varchar and that the C# string is unicode. But I strongly feel that if the SQL Anywhere OData producer converts from the database codepage to UTF8 when delivering the result sets to the clients, it should do the same thing on the way back. If we had to change all existing columns to nvarchar ... I don't even want to think about this. I tried messing around with Encoding the strings in C# to UTF8 or setting the CharSet connection parameter of the OData Server DbConnectionString. With no success.
For us, this is a showstopper. Am I missing something?
2. GUIDs as primary keys
I can't get OData to work with tables that use an uniqueidentifier as primary key.
Create a new table in the demo database:
Shut down the OData Server, restart it and update the Service Reference. We can update one of the salutations, no problem.
Now let's try the same with a guid as the primary key:
Again, shut down the OData Server, restart it and update the Service Reference. This time the client will crash with the following DataServiceClientException:
telling us, that we are not supposed to change an entity key. Well, we didn't try. Second showstopper for us.
3. Bad implementation of startswith
The implementation prevents the database from using the appropriate index.
Let's query the customers from the client:
resulting in this OData request being send to the SA OData Server:
The server translates the request into the following select statement:
which I don't understand. The database cannot use the existing index on customer name with this statement. Startswith is a very, very common OData filter condition we need for tables with sometimes millions of rows. If the OData Server is preventing the database from using matching indexes we have got a problem. Is there any reason why the OData Server doesn't generate a
here? I see that the LOCATE is necessary for
but why for StartsWith()?
Right now, the problems described seem to be the only ones left that stop our customer to push some 50 databases into the cloud. Any help is gladly appreciated.
Kind regards, Michael Fischer
The first 2 items we have confirmed as bugs. The third item functionally works but clearly is non-optimal and we are looking at it.
Item 1. .NET client did not set the charset on the HTTP content type header and the RFC 2616 default for HTTP requests is ISO-8859-1 but the OData default for Json and XML is UTF-8.
Item 2. .NET client would send the GUID value in one form on the URL and another (equivalent) form in the update body and we didn't handle that.
Item 3. This optimization is challenging due to the limits on LIKE, and whether the search string is a constant and the searched item is a column reference.
Regarding diacritics, what collation did you use when creating your database?
The following works for us ( SQLA 12 database with content in about 30 different languages, from German to Turkish, Chinese or Arabic, and read/write access from heterogenous clients ( PowerBuilder, C#, Java ):
We retested this in version 22.214.171.1243. All three issues have been solved.
answered 09 Sep '14, 09:29