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:

the source SQL dialect that your source code is written in
the target SQL dialect expected by the currently connected DBMS
rules on how to convert SQL commands between source and target forms.

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.

   SET SQL DIALECT TO ORACLE

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*"

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

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

 DOUBLE_TO_SINGLE_QUOTES

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

 MATCHES_TO_LIKE

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

 MATCHES_TO_REGEX

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

 TABLE_ALIAS_AS

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

 COLUMN_ALIAS_AS

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

 ANSI_UPDATE_SYNTAX

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

 CONSTRAINT_NAME_AFTER

    Move the constraint name in a constraint command to after the
    constraint definition,
    eg:
       ... constraint c_name unique
       ->
       ... unique constraint c_name

 CONSTRAINT_NAME_BEFORE

    Move the constraint name in a constraint command to before the
    constraint definition,
    eg:
       ... 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 )

 SUBSTRING_FUNCTION = string

   Change Informix-style string subscripting to a function call,
   Replace 'string' with the name of the sql function.
   eg. 
       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',
   eg.
      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.
   
   eg.
      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
   eg. 
      REPLACE_COMMAND set isolation to dirty read =

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