How to Write a SQL Query Adaptable for All Databases

To implement a same function within different database products using SQL during both application development and database management, the SQL syntax is more or less different from each other. That’s because each type of database has its own characteristic functions and features.
Though databases of various types have basically consistent syntax, they implement SQL with different functions or operators that sometimes cannot replace each other. To convert string “2020–02–05” to date type, for instance, the standard SQL query is select DATE(‘2020–02–05’) from USER, which is implemented differently by different types of databases:
ORACLE: select TO_DATE(‘2020–02–05’, ‘YYYY-MM-DD’) from USER;
SQLServer: select CONVERT(varchar(100), ‘2020–02–05’, 23) from USER;
MYSQL: select DATE_FORMAT(‘2020–02–05’,’%Y-%m-%d’) from USER;
If one wants to switch between different databases, they need to rewrite the SQL query. It’s really a hassle.
But by using esProc sqltranslate function, you can automatically switch functions in a SQL query between different database dialects. This makes computation easily and convenient.
Download esProc installation package and free license file HERE.

Let’s take an example to see how we can do this with esProc. Task: find which week of the year each employee’s birthday settles.

1. Start esProc, configure database connection and find a name for it. The parameters should be consistent with those in the JDBC connection configurations.

2. Use the standard esProc function to write the following SQL:
select EID, NAME, BIRTHDAY, WEEKOFYEAR(BIRTHDAY) WEEKS from EMP

3. Write script (sqltrans.dfx) in esProc:

The script uses the above SQL as a parameter:

4. Debug and execute the script to get A1’s value:
select EID, NAME, BIRTHDAY, WEEK(BIRTHDAY) WEEKS from EMP

Which is a MySQL SQL query.

To switch to another type database, we just need to modify the parameter in sqltranslate(dbtype) to the desired database name。

Below are the types of database esProc supports:

esProc defines most of the commonly seen functions, and supports user-defined functions if necessary. Learn more about sqltranslate function in esProc Function Reference.

esProc has JDBC driver to conveniently integrate the script into a Java program:

public static void testSqltranslate(){
    Connection con =null;
    java.sql.PreparedStatement st;
    // Use SQL statement as parameter
    String sql=”select EID, NAME, BIRTHDAY, WEEKOFYEAR(BIRTHDAY) WEEKS from EMP”;
    try{
        Class.forName(“com.esproc.jdbc.InternalDriver”);
        con= DriverManager.getConnection(“jdbc:esproc:local://”);
        // Call esProc script sqltrans.dfx, where the parameter is sql
        st =con.prepareCall(“sqltrans ?”);
        st.setObject(1, sql);
        st.execute();
        // Get result set
        ResultSet rst = st.getResultSet();
        System.out.println(rst);
    }catch(Exception e){
        System.out.println(e);
    }finally{
        // Close database connection
        if(con!=null) {
            try{
                con.close();
            }catch(Exception e) {
                System.out.println(e);
            }
        }
    }
}

Read How to Call an SPL Script in Java to learn more about integration of esProc script into a Java program.