How to Execute a SQL Statement on a Different Type of Database #eg74

Problem description & analysis

There is a data table CLUE in MySQL database, as shown below:

We have a SQL statement SELECT ID, WEEKOFYEAR(DATES) AS WOF, CUSTOMER, AREA FROM CLUE, where the standard function WEEKOFYEAR(d) is used to find which week where d falls in the current year.

But the counterpart function in MySQL is WEEK(d).

Our need is to execute the above SQL without in MySQL without rewriting it. Below is the desired result:

Solution

Write the following script p1.dfx in esProc:

Explanation

A1   Translate the SQL statement according to the specific database type. Both sql and dbtype are script parameters.

A2  Connect to MySQL database.

A3  Perform query on MySQL database using A1’s translation result and close database connection.

Read How to Call an SPL Script in Java to learn how to integrate the script code into a Java program.

Open source SPL source address

Download