getDataSetByQuery - multiple servers

Classic Servoy
1

Hi

I have a saas application with a core db and multiple client db’s I am trying to query both databases at the same time is this possible ie how do you add 2 server_name’s

The servers are clipper_core and clipper_client. The aim is to switch the client based on the user login which works we then want to return the following query data

The query is:
SELECT clipper_core.nav_elements.nlm_menuname, clipper_core.nav_elements.nlm_form, clipper_core.nav_elements.nim_weight, clipper_core.nav_elements.nim_context, clipper_core.products.prd_name, People.ppl_firstname, People.ppl_surname, AccessPoints.prd_id,
clipper_core.navigation.nav_name, People.ppl_id, AccessPoints.org_id
FROM People INNER JOIN AccessPoints ON People.ppl_id = AccessPoints.ppl_id
INNER JOIN clipper_core.products ON AccessPoints.prd_id = clipper_core.products.prd_id
INNER JOIN clipper_core.navigation ON AccessPoints.prd_id = clipper_core.navigation.prd_id AND AccessPoints.acc_level = clipper_core.navigation.nav_acc_level
INNER JOIN clipper_core.nav_elements ON clipper_core.navigation.nav_id = clipper_core.nav_elements.nav_id
WHERE People.ppl_username = ? AND People.ppl_password = ? AND People.ppl_status = ?

Many thanks
Gordon

2

This is only possible if you use the same brand of databases AND the databases support something like database aliases.

If they do, you need to setup the aliases in your database and then rewite your SQL to use the aliases when refering to tables.

Syntax might differ per database vendor

Paul

3

OK thanks Paul - that make sense !!

I am using MySQL for both so it should be possible.

Cheers
Gordon