An application system may connect to different types of databases at deployment. In this case, if we use a set of syntax unique to a certain database (a database dialect) for report development, migrating code to another type of database will be rather difficult. Actually, there is a high demand of an approach that can make reports “easy to migrate” across various databases.

The approach needs to avoid the use of any syntax or functions that are very specific to a database. One solution is to retrieve data from the database and perform report data source preparation with Java at the application side. In this way, we only need to change the retrieval interface to switch to another type of data source without modifying the data processing algorithm. Yet there is a hard problem with Java (including Stream). The high-level language lacks special class libraries for structured data computations, and is inconvenient to code complex report data source preparation – even a simple grouping & aggregation operation needs dozens of lines of code. More often than not, using Java to prepare data source brings more difficulties than benefits in order to achieve easy to migrate reports.

It’s time to find a beneficial solution.

The solution is esProc SPL. It is an open-source data processing engine that excels at computing structured data, provides rich class libraries to deal with complex report data source preparations, and gives direct support of various data sources (such as RDB, NoSQL, JSON, CSV, WebService, etc.) for achieving diverse source mixed computing.

Similar to Java, esProc is an independent computing engine separate from the database, which possesses its own computational capabilities. It connects to the data source to retrieve data and prepares data for report development step by step outside the database. Whenever the data source is changed, users just need to modify the SPL script for retrieving data and copy the code for the subsequent computations.

To find orders records of the first n big customers whose order amounts take up at least half of the total, for instance, SPL has the following code:

The stepwise coding finds the eligible big customers and then gets their orders information. The whole structured data computation process is much simpler than that using the native Java.

Sometimes the database is really needed to do some computing work. As databases have their own syntax, SPL provides SQL translation functionality, implemented by the sql.sqltranslate(dbtype) function, to convert standard SQL functions into the counterparts of the target database. Then users just need to modify the database connection configurations.

esProc can act as the embedded JDBC to integrate with the reporting tool, and as it accesses a database, the latter will access a SPL result set through JDBC.

For the database, SPL code is like the external stored procedure independent of it. For the application, SPL is like an upgraded Java computing engine. esProc achieves reports’ seamless migration between various data sources because it has the independent, all-around computational capabilities and offers solutions to Java’s computing difficulties.