We are using servoy 4.1 on a windows xp machine and there seems to be a problem sorting when there is more than one database server in the app.
If we have a form on a tab that uses a relationship, and the table is in a different database server then the sorting will not work. However, when we change the app to use a single database server that holds all the tables the same sort works.
Part of the reason for using Servoy is because of the publicized feature that you can use more than one database server on the same form without problems. But not being able to sort is a problem.
Weird, never had this issue.
What are you trying to sort exactly, the child records or the parent records? Which sort criteria are you trying? Is it possible that you are trying to sort on an unstored calculation? That would fail.
Do you see any errors in servoy log?
There are no errors in the logs and the fields are not calculations.
We narrowed in on the problem a little. It doesn’t matter if the tab is through a relationship or not, it’s the dataprovider. Let’s say we have a form with some dataproviders on in. The dataproviders that are from the same table as the form sort fine. If there is a dataprovider using a relationship, and the target table in the relationship is in the same database server as the form’s table sorting also works. If the dataprovider uses a relationship and the target table for the relationship is in a different server from the form’s table then sorting does not work. Have you been able to get this to work?
Note that servoy does not sort data in memory, sorting is always done in the database.
If you want to sort on a related field, we add a join to the related table and order by the column in the join table.
Obviously this only works when both tables are in the same database.
Rob
Thanks for explaining how it’s working Rob. I’m still confused though. Are you saying that it is expected behaviour for servoy to not be able to sort by a related column if that column is in a different database server? It’s not obvious to me why a join can’t be performed on a table in a different server. I have done this before successfully using SQL, you just have to specify the server name before the table eg: foreignServer…table2. If I go into a query browser for the database in question and attempt to order by the field that is not sorting in servoy through a join with the parent table it works.
That works when you use 1 connection to access both databases located on the same server.
Servoy uses a separate connection for each server, they may be located on different systems, they may even be of different vendors, doing a cross-db join will not work.
You can probably work around this in your situation if you create a view b in database A (using sql) that points to table b in database B and base your relation on view A.b
Rob