Databases implement SQL in their own sets of syntax. We need to translate function syntax from one database to another so that functionally equal SQL query statements can be executed in an incompatible system.

The ORM technique lets programmers convert queries to SQL of another type of database. This, in effect, is equivalent to having some degree of portability. The technique, however, is only suitable for dealing with simple SQL in OLTP scenarios. It is hard to handle the complicated SQL migration tasks under OLAP scenarios, such as the use of functions that are not supported directly by ORM and complex SQL queries where the FROM clause contains a subquery.

esProc SPL designs a set of standard SQL query syntax that has a wealth of built-in functions (and the number is growing) for describing as many common computations as possible. The SPL sqltranslate function can translate the standard SQL to different database SQL dialects, achieving database migration.

Below is a standard SQL statement:

SELECT CLIENT, YEAR(ORDERDATE), SUM(AMOUNT)

            , COUNT(ORDERID)

FROM ORDERS

GROUP BY CLIENT, YEAR(ORDERDATE)

HAVING SUM(AMOUNT) > 2000

We translate it using .sqltranlate("MYSQL") and get the following statement:

SELECT CLIENT, YEAR(ORDERDATE), SUM(AMOUNT)

            , COUNT(ORDERID)

FROM ORDERS

GROUP BY CLIENT, YEAR(ORDERDATE)

HAVING SUM(AMOUNT) > 2000

And return the following statement when we translate it using .sqltranslate(“ORACLE”):

SELECT CLIENT, EXTRACT(YEAR FROM ORDERDATE), SUM(AMOUNT)

            , COUNT(ORDERID)

FROM ORDERS

GROUP BY CLIENT, EXTRACT(YEAR FROM ORDERDATE)

HAVING SUM(AMOUNT) > 2000

The standard SQL function can choose the right functions according to the target database.

The strategy SPL uses to achieve SQL migration is translating only functions in a standard SQL statement while leaving the other parts alone, that is, just copy them. This enables the standard SQL to describe as many computations as possible. In the following statement, for instance, the SQL subquery remains unchanged and can be executed whichever database it is translated to.

SELECT

            ORDERID,

            M

FROM

            (

            SELECT

                        ORDERID,

                        MONTH(ORDERDATE) M

            FROM

                        ORDERS) T1 

esProc SPL is an open-source software written in Java. It can be easily integrated by a Java application, where the SQL migration functionality can be used. When you need to translate a SQL statement within a Java application, you can invoke the API method directly, as shown below:

String sql = “SELECT CLIENT, YEAR(ORDERDATE), SUM(AMOUNT) , COUNT(ORDERID) FROM ORDERS GROUP BY CLIENT, YEAR(ORDERDATE) HAVING SUM(AMOUNT) > 2000”; 
sql = com.raqsoft.dm.sql.SQLUtil.translate(sql, “ORACLE”);