...

Associating Tables from Different Databases

 
SQL(179)

【Question】
Can I relate 2 tables from different relational databases (In mysql db)?

 

【Answer】

It’s complicated to associate tables from different relational databases. Yet it’s easy to do it in esProc SPL. esProc supports database table stored in different servers.

Here’s an example of associating two tables from different databases in SPL:

Task: associating sales table from db2 database and employee table from mysql database through sales.sellerid and employee.eid to find all sales data and employee data where state=“California” (Same method for other databases).

 

Here’s structure and data of sales table:
undefined

Here’s structure and data of employee table:

undefined

 

Here’s SPL script:

 

A

1

=db2.query("select * from Sales")

2

=mysql.query("select * from   Employee")

3

=join(A1,SELLERID;A2,EID)

4

=A3.select(#2.STATE=="California")

5

=A4.new(#1.ORDERID, #1.CLIENT, #2.NAME:SELLERNAME, #1.AMOUNT,   #1.ORDERDATE)

6

return A5

A1,A2: Retrieve sales table and employee table from db2 and mysql respectively; the two data sources are configured in advance;

A3: Associate sales table and employee table according to sellerid=eid using SPL’s object reference mechanism;

A4: Find records meeting the condition state="California";

A5: Create a new table sequence with desired fields;

A6: Return A5’s table sequence to the caller of esProc SPL script.

The you can call the SPL script from another application via esProc JDBC. For details, see How to Call an SPL Script in Java.


SQL(179)
 
 •  419 View  •