NativeSelect + Oracle + ORDER BY issue

Hi, everybody!
Please, help me.

I’ve just encountered a strange problem with querying Oracle database from my Vaadin project using FreeFormQuery.

So, my actions are:

  1. Creating a
    NativeSelect
    ;

  2. Getting an SQLcontainer with a
    FreeFormQuery
    like “SELECT id, name FROM mytable ORDER BY name”;

  3. Binding the data to my NativeSelect:

	
my_select.setContainerDataSource(my_SQL_container);
my_select.setItemCaptionPropertyId("NAME");
my_select.setImmediate(true);


Thats all. After that I get a NativeSelect with ordered data, but when I choose something (even without listener), I get an exception:

I should add that if I get rid of “ORDER BY” clause in my query then everything works fine, but i need the data to be ordered!

When the containsId(ID) method is called, the FreeformQuery will attempt to add a WHERE clause to your query string, so it will become something like “SELECT foo FROM bar ORDER BY baz WHERE key=ID”. I’m guessing that such an ordering of the keywords is not allowed. You should look into implementing the FreeformStatementDelegate interface and especially the getContainsRowQueryStatement(Object… keys) method to deliver a syntactically correct query string to your database.

If the table you are quering from is small enough you can also do select * from ( select foo from bar order by bar) as grapes. This would keep you from having to implement the FreeFormStatementDelegate.

Just to inform: I am see this warning in my logs and, after doing some traces, it seems it’s not NativeSelect neither Oracle related in my case. It seems ole SQLContainer is the one to blame here again. When I scroll down in a Grid with a handful of rows and it tries to do some “lazy-loading”, it stops on this line. Buggy code, I suppose. Yeah, I’m aware that SQLContainer it’s been deprecated.