on 2014 May 12 11:50 AM
Hello,
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 16.0.0.1824. Create a new C# Console App in VS 2012 Update 4 and add a service reference to the ODataSalesOrders server.
1. Umlauts
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'.
update FinancialCodes set Description = 'Gebühr' where Code = 'e1'; commit;
Now let's iterate through the FinancialCodes.
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)); foreach (var expenseType in oDataService.FinancialCodes.Where(c => c.Type == "expense")) { Console.WriteLine("{0}: {1}", expenseType.Code, expenseType.Description); } } }
The program correctly displays the german description. But when I change the description from C# (and it contains an Umlaut), I run into trouble.
var finCode = oDataService.FinancialCodes.Where(c => c.Code == "e1").SingleOrDefault(); if (finCode != null) { finCode.Description = "Servicegebühr"; oDataService.UpdateObject(finCode); oDataService.SaveChanges(); }
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:
drop table if exists GROUPO.Salutation; create table GROUPO.Salutation ( ID integer identity not null primary key , SalText varchar(30) not null ); grant select, insert, update, delete on Salutation to Updater; insert into Salutation (SalText) values ('Mrs.') , ('Mr.') , ('Dear'); commit;
Shut down the OData Server, restart it and update the Service Reference. We can update one of the salutations, no problem.
var salutation = oDataService.Salutation.Where(s => s.SalText == "Mr.").SingleOrDefault(); if (salutation != null) { salutation.SalText = "Herr"; oDataService.UpdateObject(salutation); oDataService.SaveChanges(); }
Now let's try the same with a guid as the primary key:
drop table if exists GROUPO.Salutation; create table GROUPO.Salutation ( ID uniqueidentifier not null primary key , SalText varchar(30) not null ); grant select, insert, update, delete on Salutation to Updater; insert into Salutation (ID, SalText) values (newid(), 'Mrs.') , (newid(), 'Mr.') , (newid(), 'Dear'); commit;
Again, shut down the OData Server, restart it and update the Service Reference. This time the client will crash with the following DataServiceClientException:
<?xml version="1.0" encoding="utf-8"?> <error xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"> <code>30004</code> <message xml:lang="en-US">Ein Entitätsschlüssel kann nicht geändert werden.</message> </error>
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:
var customers = oDataService.Customers .Where(c => c.Surname.StartsWith("Ch")) .OrderBy(c => c.ID);
resulting in this OData request being send to the SA OData Server:
R. 2014-05-12 16:20:20. [default:12] Request GET http://localhost:8090/odata/Customers()?$filter=startswith(Surname,'Ch')&$orderby=ID
The server translates the request into the following select statement:
SELECT TOP 101 t1."ID", t1."Surname", t1."GivenName" , t1."Street", t1."City", t1."State", t1."Country", t1."PostalCode" , t1."Phone", t1."CompanyName" FROM "GROUPO"."Customers" t1 WHERE ( ( 1 = LOCATE( t1."Surname", LEFT( 'Ch', 254 ) ) ) ) ORDER BY t1."ID";
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
WHERE t1.Surname like 'Ch%'
here? I see that the LOCATE is necessary for
var customers = oDataService.Customers .Where(c => c.Surname.Contains("Ch")) .OrderBy(c => c.ID);
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
We retested this in version 16.0.0.2003. All three issues have been solved.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
@Michael: Feel free to "accept" any answer - that's the usual sign that your question has been answered successfully. Closing questions is not that common here, except for unfitting ones - and yours is fitting, for sure:)
I hope you don't mind that "nanny" comment...
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Bad news is we found item 2 is not in fact fixed. .NET client was sending the primary key guid value in the URL in a different form from the body and our sanity check didn't recognize that.
Good news is we have fixes for both and I am currently testing them. Unfortunately, there is an SP currently in QA so the next one won't be for 4-6 weeks. By then I should have a fix for item 3.
I have a solution for all three items. If you send me an email, we can discuss it further.
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 😞
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
And, not surprisingly, "ü" interpreted as cp1252 bytes is C3 BC which is the encoding for "ü" in UTF8. So, I'd say the C# client is presenting a UTF-8 encoded string to an interface that is expected cp1252 (or, for example, the client charset is set to cp1252). So, the server converted from 1252 to UTF8 when it was inbound to the server.
Vincent, John,
thanks for your input. I was on the same track first and tried to change the character set of the OData server connection to database server to UTF-8, with no success.
The OData server corresponds with the C# client in UTF-8 encoding. From the perspective of the database, on the other hand, it is just an ODBC client. When the OData server reads data from the database, it correctly translates the character values to UTF8 before it responds them to the client. But when it receives changes from the client, it doesn't seem to forward them to the database with the correct encoding. That's why I thought it looked like an OData server issue and not a database client connection problem. Philippe has confirmed that in the meantime (well, at least he confirmed it's a bug). Sorry if I wasn't clear enough in my initial problem description.
User | Count |
---|---|
68 | |
10 | |
10 | |
7 | |
6 | |
6 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.