9.3 Configuration files
The syntax of an SQL command is converted from its source dialect to the DBMS’ native dialect, by applying a number of transformations one after another on the SQL text.
For example, consider the steps taken to get the following Informix SQL statement to run correctly with PostgreSQL:
select last_name, first_name[1], (today-birthday)/365 age
from client
where last_name matches "M*"
-
replace double quotes with single quotes
-
replace matches with the regular expression operator ~
-
use the function substr() instead of subscripting with []
-
replace the word today with date(now())
-
insert the word "AS" before the column alias age
The result is:
select last_name, substr(first_name,1,1), (date(now())-birthday)/365 AS age
from client
where last_name ~ ’^M.*’
Special configuration files are used to indicate what conversions are needed.
They are located in the directory /opt/aubit4gl/etc/convertsql (this can be changed by setting the environment variable A4GL_SQLCNVPATH to an alternative location).
There is one file for each combination of source and target dialect, each file being named as source-target.cnv. For example, the rules for translating from Informix to PostgreSQL are in a file called INFORMIX-POSTGRESQL.cnv, in which the conversion rules for the above example are given as:
DOUBLE_TO_SINGLE_QUOTES
MATCHES_TO_REGEX
SUBSTRING_FUNCTION = substr
REPLACE today = date(now())
COLUMN_ALIAS_AS