Should this be possible?

Classic Servoy
1

Suppose we have two database servers and we define a relation from a table on the first server to a table on the other one. Can we then use find in the following way?

var _fs = foundset

if (_fs.find()) {
	_fs.table_server1_to_table_server2.field = 1
	if (_fs.search()) {
		application.output('found!!!')
	}
}

We tried this and we are getting:

TypeError: Cannot set property "field" of undefined to "1"

Is this because we have a faulty relation or because Servoy does not support this?

2

that should be possible!

it complains, about the relation (undefined) so maybe you mispelled that?

3

Hi Harjo,

No, the relation works ok outside the find mode…

Regards,

4

so that relation works fine without the find, it is not a global relation (only globlals on the left side) and it is not defined over 2 servers ?

Then it should work as far as i can see.

5

It works fine without the find.
It is not a global relation.

But it IS defined over 2 servers.
Is that not permitted?

6

yes then you can’t search, we can’t generate then a sql statement for a the parent foundset that is then filtered over a relation that comes from a completely different server.

7

ah sorry, I see now what you are trying to do, you are doing a related search…

that is not working, because you can’t query over two total different servers.

8

As already mentioned, we do not support this in findmode, because it is not possible to run 1 SQL statement over 2 databases. Doing fancy stuff in the back, splitting the query in multiple parts, creating temptables with the results and thus merging the results is going to perform horribly.

What we can do through is make sure that such a scenario would raise a proper error. Would that help?

Paul

9

Yes, a proper error message would be nice, and maybe this can be added to the wiki.

I agree that it’s not easy and performance won’t be good but I think that for a normal relation (without the find) over 2 databases you also have to split the sql up into multiple queries (maybe you won’t need the temp-tables then).

10

Already tagged this to be added to the documentation.

Will also put it on the todo to raise a proper error.

As for the performance: under normal circumstances we know exactly which related records to get, so usually a relatively small subset.

In case of a find you don’t know yet, so potentially you need to do massive copying of data from one database to another.

Paul

11

I understand, you have to store the cartesian product in a temp table and do the find on this temp table.

But how can we solve this issue? Whether we use foundset/relations/search/find or ‘raw sql’, we have the same problem.

12

I don’t think there is a generic solution to this situation.

If you have a few identifiable locations in your code where this happens, you could implement the creation of the temptable yourself without too much overhead, because you could know the context in which the scenario happens and thus perform the temptable creation is the most optimized way possible.

Another option would be not to use two separate databases to begin with, but I assume you didn’t go for two separate databases just for fun.

Third option would be, if the database supports it, to create a database level link between the two databases and in your master database create a view on the linked t5able and in Servoy use that view instead.

All in all no easy solution to such a scenario. The best solution depends on the situation.

Paul

13

Okay, clear. Thanks for sharing your thoughts.

14

We found out the hard way on this one a while back as well. Thanks for clarifying the error Paul.

We solved it by using the solution model to test whether a relation was cross server, then splitting the cross server find up to return all the keys of the records found on the right-hand server and then finding within those keys the value from the left-hand server.

15

In Servoy 6, a message will appear in the developer console when a cross-server relation is used for find().

Rob

16

Wow, it took me a while to find this post once I came across the problem. This would be a nice feature to have even if performance were slow - I submitted a feature request.

David, would you be willing to provide more detail on your workaround?

Rob, thanks for adding the error message in v6. I noticed Servoy doesn’t prompt with errors in other “Find” instances, such as inputting characters into a numeric field. It would be nice if it did. On the otherhand, maybe we should be validating the user input ourselves, even in find mode?

17

Here is a related topic: viewtopic.php?t=15718