For different types of databases, the SQL queries, which are supported by all the databases and have similar syntax, may still be slightly different, making them not in common use. The main reason is that these databases have different rules for function parameters.

 

For example, converting the string “2020-02-05” to a date will be written differently in different 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 we want the application to be migrated between different databases, the SQL statement needs to be rewritten, which is very troublesome.

The sqltranslate function provided in SPL can automatically translate functions in form of simple SQL of SPL into the form of other database, making migration much more convenient.

 

1. the usage of sqltranslate function

sql.sqltranslate(dbtype)

This function translates functions in form of simple SQL of SPL into various forms of database. Here the sql is the SQL statement to be translated, and dbtype is the database type. Functions are defined in SPL’s simple SQL, and those undefined functions will not be translated. For a list of defined functions and database types, see sqltranslate function help

 

Write a SPL script file sqltrans.dfx, and set two parameters, sql and dbtype:

	A	Annotation
1	=sql.sqltranslate(dbtype)	Translate sql into a SQL statement that conforms   to the syntax of dbtype
2	=connect(dbtype)	Connect to the database
3	=A2.query@x(A1)	Use the translated A1 to query the   results in the database, and close the database connection

Suppose the sql is the following statement:

SELECT EID, NAME, BIRTHDAY, ADDDAYS(BIRTHDAY,10) DAY10 FROM EMP

 

When this script executes, the parameter dbtype is ORACLE, and the result of A1 is as follows:

SELECT EID, NAME, BIRTHDAY, BIRTHDAY+NUMTODSINTERVAL(10,'DAY') DAY10 FROM EMP

 

If the parameter dbtype is SQLSVR (that is, SQL SERVER) , then the result of A1 is as follows:

SELECT EID, NAME, BIRTHDAY, DATEADD(DD,10,BIRTHDAY) DAY10 FROM EMP

 

2. being called in Java

SPL can be called by Java through JDBC,and the code for calling sqltrans.dfx is as follows:

Class.forName("com.esproc.jdbc.InternalDriver");
Connection con = DriverManager.getConnection("jdbc:esproc:local://");
PreparedStatement st = con.prepareCall("call sqltrans(?,?)");
st.setObject(1, "SELECT EID, NAME, BIRTHDAY, ADDDAYS(BIRTHDAY,10) DAY10 FROM EMP");
st.setObject(2, "ORACLE");
ResultSet rs = st.executeQuery();

The rs in this case is the query result set returned by cell A3 of the script file.

For further usage, see How to Call an SPL Script in Java

 

If the SQL statements only need to be translated in a Java program, the API methods can be used directly:

String sql = "SELECT NAME, BIRTHDAY, ADDDAYS(BIRTHDAY,10) DAY10 FROM EMP";
sql = com.raqsoft.dm.sql.SQLUtil.translate(sql, "ORACLE");

 

3. Add new database and function definitions

Database types and functions in form of simple SQL of SPL are defined by dictionary file /com/raqsoft/dm/sql/function.xml in the release package esproc-bin-xxxx.jar. Part of the document reads as follows:

The FUNCTIONS node in the file represents a function group, type is a function group type, and FixParam refers to a function group with a fixed number of parameters.

 

The FUNCTION node represents a simple SQL function, name is the function name, paramcount is the number of parameters, value is the default value when translating the function, and empty string means no translation is required.

 

The INFO node represents a database, dbtype is the database name, empty string refers to simple SQL of SPL, and value is the corresponding value translated to this database. ? or ?1 of value represents the first parameter value of the function, ?2 represents the value of the second parameter value of the function... and so on. When the value in INFO is an empty string, the value of the parent node FUNCTION is used.

 

If the INFO node definition of the database is not specified under the FUNCTION node when translating, the FUNCTION remains untranslated.

 

What if the target database to be translated is not defined in the dictionary file? We can modify the dictionary file to add the target database and add an INFO node under the FUNCTION node that needs to be translated. For example, if the target database is named MYDB and the syntax for the ADDDAYS function is dayPlus (date,days), then add an INFO node to the dictionary as follows:

Run sqltranslate("MYDB") after modifying the dictionary and,and the SQL statements in the previous example are translated into:

SELECT EID, NAME, BIRTHDAY, dayPlus(BIRTHDAY,10) DAY10 FROM EMP

 

If a new function needs to be added, write a FUNCTION node to the dictionary file and add some child nodes INFO to it to define what syntax this function should be translated into in the forms of various databases.