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