How To Write SQL Statements That Are Migratable Between Databases

Not all SQL statements are migratable. Different databases have different functionalities. When the syntax of certain does not have their counterpart in another database, migration becomes impossible.

If we do not use the databases’ special functionalities but only use their common functionalities - which actually are a rich collection - within the range of the international standards, does the migration work?

Basically, yes. For example, the following statement

select * from employee where age>50
select area,sum(amount) from orders group by area

Works normally for all databases.

However, there are still many SQL functions whose syntax vares with databases, particularly string and date-related functions, for example:

MySQL: year( x )
Oracle: extract( year from x )

The international standards do not impose any rules on these functions, so SQL statements using them are database-dependent and un-migratable. The problem is that those functions are very commonly-seen in application development.

Hibernate can convert standard HQL statements containing them into SQL for different databases. But, the number of functions HQL supports is too small. Users need to register each unfamiliar function for each database they encounter, which leads to the loss of migratability. Moreover, the SQL statements HQL can generate are relatively simple, making the range of applications too narrow.

esProc SPL is a better choice for converting different functions in SQL statements.

esProc SPL is an open-source software written in Java. It is offered at https://github.com/SPLWare/esProc.

There is a sqltranslate function in SPL. It can translate SQL statements using “standard” function syntax to statements using function syntax of corresponding databases. For example:

sql = select * from Orders where year(OrderDate)=2000
sql.sqltranslate(MySQL) -> select * from Orders where year(OrderDate)=2000
sql.sqltranslate(Oracle) -> select * from Orders where extract( year from OrderDate )=2000

The sqltranslate function pre-defines a lot of function syntax of many databases. For example:

sql = select * from Orders where ADDDAYS(OrderDate,3)>ShipDate
sql.sqltranslate(MySQL) -> select * from Orders where (OrderDate + INTERVAL 3 DAY)>ShipDate
sql.sqltranslate(Oracle) -> select * from Orders where (OrderDate + NUMTODSINTERVAL(3, DAY)>ShipDate
sql.sqltranslate(DB2) -> select * from Orders where (OrderDate + 3 DAYS )>ShipDate

For functions or databases encountered for the first time, esProc allows programmers to add them into configuration list.

esProc offers JDBC driver and can be easily integrated in a Java application to achieve SQL migration. Find complete migration directions here: SPL: Translating SQL across databases .

Of course, esProc is designed more than for SQL migration. SPL is a powerful structured data processing language. SQL conversion is only a small feature SPL implements passingly. The main role of SPL is to replace SPL to achieve complex query operations.

The syntax or functions that are unique to certain databases are used to achieve complex computing requirements. For example, Oracle has KEEP function to get the first/last record of each group conveniently, but many other databases do not have a counterpart. If a SQL statement uses this function, the above simple method would fail to migrate it to another database. In this case, we can retrieve data using the basic SQL and use SPL to handle the complicated computations. SPL is completely database-independent, and the SPL code continues to be migratable.

select Area, max(Amount) KEEP( dens_rank first order by extract( month from OrderDate) )
from Orders where extract( year from OrderDate)=2000 group by Area

The above Oracle SQL statement can be rewritten in SPL as follows:

	A	B
1	’select OrderDate, Area, Amount from Orders where year(OrderDate)=2000	/Retrieve data in SQL
2	=A1.sqltranslate(Oracle)	/Convert to SQL of the target database
3	=db.query@x(A2)	/Import data
4	=A3.group(Area;~.minp@a(month(OrderDate)).max(Amount))	/Achieve computing goal in SQL

(Unlike other programming languages, SPL code is written in grid. Find more information here: A programming language coding in a grid )

SPL possesses all computing abilities SQL as (filtering, grouping and join,etc.). It implements them by itself (instead of depending on translation to SQL) and is database independent. This ensures that the code can be correctly executed on any databases and becomes migratable.

In fact, SPL’s computing capability is far more powerful and richer than SQL’s (This makes it impossible to be translated to SQL for execution). SPL implements some complicated computations in a simpler way than SQL does, even when the latter can. Here is one familiar example: to find the largest number of days a stock rises consecutively, the SQL statement is a nested query, which is lengthy and difficult to understand:

select max(ContinuousDays) from (
    select count(*) ContinuousDays from (
        select sum(UpDownTag) over (order by TradeDate) NoRisingDays from (
            select TradeDate,case when Price>lag(price) over ( order by TradeDate)then 0 else 1 end UpDownTag from Stock ))
    group by NoRisingDays )

SPL offers more basic functions. It writes the same logic in a much simpler way:

Stock.sort(TradeDate).group@i(Price<Price[-1]).max(~.len())

The code is easy to write and migrate.