How to Access Databases using One SQL Statement #eg71
Problem description & analysis
There is a sales data table in Oracle. The table stores data of the year 2013, as shown below:
There is also a sales data table in MySQL. It stores data of the year 2014, as shown below:
We are trying to get data of the 35th week from both sales tables and concatenate the results. Below is the desired result:
Solution
Write the following script p1.dfx in esProc:
Explanation
A1Â The standard SQL to be executed.
A2Â Define a sequence of data source names.
A3Â Define a sequence of data source types.
A4Â Connect to each database.
A5Â Translate functions in the standard SQL statement into their counterparts in the specific databse.
A6Â Concatenate results returned from databases in which the corresponding SQL is executed.
Read How to Call an SPL Script in Java to learn how to integrate the script code into a Java program.
