Sql query to load records

Classic Servoy
1

I want to load those records that are found in two database table. For example If my formA datasouce is table Transactions and want to look up in other Database Employee in Tb_Employee table and my SQL query is

var query="SELECT trans_Id FROM transactions WHERE emp_id IN  (SELECT e.emp_id FROM Employee.Tb_Employee e WHERE e.Status='Active')"
foundset.loadRecords(query,null) //throws an error INVALID OBJECT NAME  'Employee.Tb_Employee'.

Any suggestions where I am wrong.

2

Hi,

You can’t query 2 different databases with one query without some help.

  1. Servoy itself allows for relations over different databases but it does in fact 2 queries and then combine it for you before it shows it, so Servoy does the heavy lifting for you (but has it’s limitations).
  2. You do the same as Servoy does yourself in code. This way you can use SQL for more complicated SELECTs.
  3. Another way is to use a database connection inside the database server. Which means that (some) tables of database A are accessible in database B. This requires some configuration in database B and not all DB vendors support it. The SQL standard for this is called SQL/MED but not all vendors support it completely or have their own variation of it.

But only with option 3 you can use SQL to query your data from 2 databases in one go.

Hope this helps.

3

Regarding option 3 above:

PostgreSQL is known to support SQL/MED. But if you use MySQL, you’ll need to use FederatedX instead of the default storage engine (InnoDB in 5.5 and MyISAM in 5.1 and older versions).

FederatedX is the storage engine that supports querying among various databases.

Best, Carlos