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

asked 12 May '14, 10:50

Michael%20Fischer's gravatar image

Michael Fischer
630101527
accept rate: 12%

edited 09 Sep '14, 10:56

I will have to investigate and get back to you shortly.

(12 May '14, 11:16) PhilippeBert...

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.

permanent link

answered 12 May '14, 14:52

PhilippeBertrand%20_SAP_'s gravatar image

PhilippeBert...
1.4k41733
accept rate: 21%

edited 15 May '14, 19:56

Philippe,

thanks for your fast response. Our customer confirmed that we could start the project with the first two items fixed. I know that it is sometimes difficult to tell, but do you have a timeline for a patch that will fix these? We would be glad to help with testing.

(13 May '14, 04:48) Michael Fischer
Replies hidden

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.

(13 May '14, 10:15) PhilippeBert...
1

I have a solution for all three items. If you send me an email, we can discuss it further.

(15 May '14, 20:00) PhilippeBert...
Replies hidden

Philippe,

how do I send you an email? I don't find an email function here. You can contact me at mifi(at)appfact(dot)de.

(19 May '14, 10:53) Michael Fischer

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 ):

  • Database CHAR collation: UTF8BIN, NCHAR collation: UCA
  • Connection Character Set: UTF-8
permanent link

answered 12 May '14, 11:25

Vincent%20Buck's gravatar image

Vincent Buck
70681520
accept rate: 15%

edited 12 May '14, 11:26

1

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.

(12 May '14, 11:59) John Smirnios
1

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.

(13 May '14, 04:59) Michael Fischer

We retested this in version 16.0.0.2003. All three issues have been solved.

permanent link

answered 09 Sep '14, 09:29

Michael%20Fischer's gravatar image

Michael Fischer
630101527
accept rate: 12%

@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...

(09 Sep '14, 10:35) Volker Barth
Replies hidden

I don't mind at all, Volker. I can't reopen it, can I?

(09 Sep '14, 10:57) Michael Fischer

Don't know, but apparently I could do so:)

(09 Sep '14, 11:46) Volker Barth
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:

×8

question asked: 12 May '14, 10:50

question was seen: 20,271 times

last updated: 09 Sep '14, 11:46