Automatic SQL Syntax Conversion

Aubit4GL allows you to connect to DBMS's (database management systems) from various vendors, as long the connection is via the SQL command language.

Unfortunately, the syntax of the SQL language can differ considerably from one vendor to another, and often valid syntax for one DBMS fails when executed against some other DBMS. One way around this is to maintain different versions of your application, eg. one for use with Informix, another for running against Oracle, another for PostgreSQL, and so on. Another way is to replace each SQL command in your source code with a number of alternatives in a case statement, depending on the target database type. Either way, your code will be difficult to maintain and harder to read.

Aubit4GL resolves this by providing a module that lets you write code using just one version or "dialect" of SQL, and have this converted into the correct form for whatever database you connect to at run-time.

In order to do this, Aubit4GL needs to know the following:

Source SQL dialect

By default, the compiler assumes SQL is written using standard Informix syntax.

This can be changed by setting the environment variable A4GL_SQLDIALECT, or by setting the value of SQLDIALECT in the /etc/opt/aubit4gl/aubitrc file.

You can also change it at run-time using the SET SQL DIALECT command eg.

This will cause all subsequent statements to be treated as if they were written using Oracle syntax.

Note - the 4GL compiler is not guaranteed to handle commands using non-Informix syntax. If the compiler cannot understand a particular command, simply place itin a char variable (string), prepare it, and execute it.

Target (DBMS) SQL dialect

The database connection driver will inform Aubit4GL at run-time what dialect of SQL it speaks, so you do not have to configure this explicitly.

Conversion rule 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*"
  1. replace double quotes with single quotes
  2. replace matches with the regular expression operator ~
  3. use the function substr() instead of subscripting with []
  4. replace the word today with date(now())
  5. 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:

REPLACE today = date(now())

Converting SQL script files

Many 4GL programmers keep script files of SQL commands to be run through SQL command interpreters like isql, psql, etc., rather than via a 4GL program.

A command line utility, convertsql is available to convert these as well.

You may have to compile this program from source. Go to /opt/aubit4g/tools/convertsql, and follow the instructions in README.txt.

For example, to convert a file full of Informix SQL commands into SapDB compatible commands, you might execute:

    convertsql INFORMIX SAPDB < mystuff.sql > mystuff2.sql

Conversion file syntax reference

The file contains a series of conversion directives, one to a line, with the following formats:

1) Simple directives, taking no arguments


   Change double quotes (") to single quotes (') around literal strings.


    Change Informix_style 'matches' clause to one using 'like',
    and change * and ? to % and _ respectively.
       matches 'X?Z*'   ->  like 'X_Z%'


    Similar to 'matches-to-like' but uses the Postgres style regular
    expression syntax,
       matches 'X?Z*'   ->  ~ '^X.Z.*'


    Insert the word "as" before table alias names in a 'from' clause
       from ..., table1 t1, ...  ->  from ..., table1 as t1, ...


    Insert the word "as" before column/expression alias names in a
    'select' clause
       select ..., sum(amount) amt, ...
       select ..., sum(amount) as amt, ...


    Convert Informix-style "update ... set (..,..) = (..,..) " to the
    ANSI standard format "update ... set ...=..., ...=... "
       update mytable set (col1,col2,col3) = ("01", "X", 104) where ...
       update mytable set col1="01", col2="X", col3=104  where ...


    Move the constraint name in a constraint command to after the
    constraint definition,
       ... constraint c_name unique
       ... unique constraint c_name


    Move the constraint name in a constraint command to before the
    constraint definition,
       ... unique constraint c_name
       ... constraint c_name unique

2) Directives that take an argument ( in the rules below, replace the word "string" with the appropriate values )


   Change Informix-style string subscripting to a function call,
   Replace 'string' with the name of the sql function.
       where ... foo[3,5] = ....
       where ... substr(foo,3,3)

3) Text replacement directives

   Search and replace is not case-sensitive. For legibility, lower case
   is used in the rules for search/replace strings to distinguish them from 
   the keywords (in upper case).

   You may leave the replacement string (after the = sign) blank. This will
   have the effect of removing the matched string from the converted output.

 REPLACE before = after

   Replace any occurrence of the string 'before' with 'after',
      REPLACE rowid = oid
      REPLACE current year to second = sysdate
      REPLACE today = date(now())

 REPLACE_EXPR before = after

   Replace only if the 'before' text is found in an expression or where
   an expression is allowed, such as in a where clause or a select clause.
      REPLACE_EXPR sysdate = current year to second
      REPLACE_EXPR today = date(now())

 REPLACE COMMAND before = after

   Replace, but only if the whole SQL statement matches the 'before' string
      REPLACE_COMMAND set isolation to dirty read =

   The example above has the effect of completely erasing the command.