Sql query to load records

Questions and answers regarding general SQL and backend databases

Sql query to load records

Postby hardina09 » Mon Oct 08, 2012 9:25 pm

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

Code: Select all
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.
hardina09
 
Posts: 62
Joined: Tue Apr 24, 2012 9:46 pm

Re: Sql query to load records

Postby ROCLASI » Mon Oct 08, 2012 10:28 pm

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.
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Mastodon: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5438
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Sql query to load records

Postby jcarlos » Tue Oct 09, 2012 5:49 am

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
jcarlos
 
Posts: 578
Joined: Thu May 04, 2006 8:55 pm
Location: Palo Alto, California USA


Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 3 guests

cron