newRelation in other solution to avoid multi server error

[developers10]

Hi,

All our 'hardcoded' relations reside in one solution which we use for things like that and which is shared amongst other solutions.

No however, the need has arisen to programatically create a relation using solutionModel.newRelation, but is there a way to create this relation in a particular solution?
Our businesscode and forms reside in different solutions, and when I create the relation in the businesscode solution and use this on a form
in the forms-solution, I get an error saying 'Relation xxxxx cannot be used in a find (multi server)' when in enter find() mode.

Thanks in advance
Chris

[patrick]

The "error" is a warning stating that cross database joins cannot work. So it seems your relation points from one DB to another and you cannot join over two DBs (at least not with Standard SQL).

[developers10]

Hi Patrick,

Form, datasource and relation are created all in one and the same session.
It's definitely the same database, but the relation is between a in memory created datasource
and an existing table in the database but that's not really a different database, is it?

Code: Select all

_form.dataSource = _created_dataset.createDataSource('name_of_ds');
var rel = solutionModel.newRelation(this.foreignrelation_name, _form.dataSource , 'db:/development/customer', JSRelation.INNER_JOIN);



How else could one create a relation in runtime, between a datasource created in runtime and an existing table?

Thx
Chris

[patrick]

The in-memory DB is a different database (in the memory of the servoy server). You can create and use relations between two DBs just fine, but you cannot do a find over it. For a find we need to do a join and that's just not possible.

[developers10]

Hi Patrick,

Impossible is such a hard word

I would imagine that if a join should occur, the databaseManager identifies a dataSource as in-memory and hence creates a Sql-statement/query with the in-memory datasource as XML
on which the real table is joined. (using the sp_xml_preparedocument for instance or the WITH test_xml(data) AS (VALUES.... in PostGres)
Very easy and a technique which I use often (instead of the time consuming 'where in' statement)
So not impossible, just not (yet?) feasible in Servoy.

I'll look for a workaround
Thx
Chris

[patrick]

That is a technique, yes, but it has nothing to do with standard sql. How are you ever going to make sure this works on all databases? So every time someone comes along with a new DB, we have to investigate how that is done there.

[developers10]

I would imagine that would indeed be the case.
But that's a pain every ORM has to go through in order to provide compatibility I imagine.
Even an ORM like Entity Framework can connect to sql, postgres, oracle and so on...
So a mechanism per supported database could be provided and initially non-supported databases could be documented as such.