Cross Database Find

Classic Servoy
1

Servoy 5.2.6 and 6 a3 don’t currently allow you to do a find on a related field if the relation points to a different database server. It would be nice if there was a way to tell Servoy that your databases are linked so that Servoy would go ahead and select the find from both databases (e.g. select db1.table1.keyField from db1.table1 join db2.table2 on db1.table1.keyField = db2.table2.forignKeyField where db2.table2.someField = ‘xyz’). I submitted a feature request for this.

Here is a workaround suggested by pbakker in a separate post (viewtopic.php?t=15402)

pbakker:
…if the database supports it, create a database level link between the two databases and in your master database create a view on the linked table and in Servoy use that view instead…

I tried this in MySQL 5.1 and it worked great (I also found out that you can use the federated engine in MySQL to allow remote access to an innodb (or other) table - so if your using MySQL across more than one physical server you can create a federated table in your local DB to access a table in your remote DB from either Servoy or MySQL since a view won’t work in this case).

btw - Sorry for this new post when one already existed on the topic. I was just hoping to make this easier to find my giving it a descriptive title.

2

One thing to keep in mind is that you shouldn’t mix-and-match those federated tables with the real table for displaying/updating data. When Servoy does caching and data broadcasting, it is unaware that the federated table is really the same table as exists in another connection. So, lets say you have:

Server1: Table A
Server2: Table A (Federated table to Server 1:Table A)

If you have a form based on “Server1: Table A” for displaying data. And then you use a foundset object to do a find with “Server2: Table A” , and then lets say you modify data in a record in the “Server2: Table A” foundset, then that change will NOT be immediately reflected on the user’s screen. Even though they point to the same table, Servoy will treat them like separate tables.

So, its ok to do your find that way, but be careful if you modify any data using that same technique.