Back to Contents

SQL Programming

Summary:

• Programming
  • Database utility library
  • Implicit database connection
  • Managing transaction commands
• Performance
  • Using dynamic SQL
  • Static SQL cache
  • Using transactions
• Portability
  • Database entities
  • Database users and security
  • Data definition statements
  • Using portable data types
  • Concurrent data access
  • The SQLCA register
  • Optimistic locking
  • Auto-incremented columns (SERIALs)
  • Informix SQL ANSI mode
  • Positioned Updates/Deletes
  • String literals in SQL statements
  • Naming database objects
  • Temporary tables
  • Outer joins
  • Sub-string expressions
  • Using rowids
  • MATCHES operator
  • GROUP BY clause
  • LENGTH() function

See also: Connections, Transactions, Static SQL, Dynamic SQL, Result Sets, SQL Errors, Programs, ODI Guides.

Programming

Database utility library

The BDL library "fgldbutl.4gl" provides several utility functions. You will find this library in the FGLDIR/src directory. For example, this library implements a function to get the type of the database engine at runtime. See the source file for more details.

Implicit database connection

In BDL, the DATABASE statement can be used in two distinct ways, depending on the context of the statement within its source module :

• To specify a default database : Typically used in a "GLOBALS" module, to define variables with the LIKE clause, but it is also used for the INITIALIZE and VALIDATE statements. Using the DATABASE statement this way results in that database being opened automatically at run time.
• To specify a current database : In MAIN or in a FUNCTION, used to connect to a database. A variable can be used in this context ( DATABASE varname ).

A default database is almost always used, because many BDL applications contain DEFINE ... LIKE statements. A problem occurs when the production database name differs from the development database name, because the default database specification will result in an automatic connection (just after MAIN):

01 DATABASE stock_dev
02 DEFINE
03    p_cust RECORD LIKE customer.*
04 MAIN
05    DEFINE dbname CHAR(30)
06    LET dbname = "stock1"
07    DATABASE dbname
08 END MAIN

In order to avoid the implicit connection, you can use the SCHEMA instruction instead of DATABASE:

01 SCHEMA stock_dev
02 DEFINE
03    p_cust RECORD LIKE customer.*
04 MAIN
05    DEFINE dbname CHAR(30)
06    LET dbname = "stock1"
07    DATABASE dbname
08 END MAIN

This instruction will define the database schema for compilation only, and will not make an implicit connection at runtime.

Managing transaction commands

A BDL program can become very complex if a lot of nested functions do SQL processing. When using a database supporting transactions, you must sometimes manage to execute all SQL statements in the same transaction block. This can be done easily by centralizing transaction control commands in wrapper functions.

The fgldbutl.4gl library contains special functions to manage the beginning and the end of a transaction with an internal counter, in order to implement nested function calls inside a unique transaction.

Example:

01 MAIN
02     IF a() <> 0 THEN
03        ERROR "..."
04     END IF
05     IF b() <> 0 THEN
06        ERROR "..."
07     END IF
08 END MAIN
09
10 FUNCTION a()
11   DEFINE s INTEGER
12   LET s = db_start_transaction( )
13   UPDATE ...
14   LET s = SQLCA.SQLCODE
15   IF s = 0 THEN
16      LET s = b( )
17   END IF
18   LET s = db_finish_transaction((s==0))
19   RETURN s
20 END FUNCTION
21
22 FUNCTION b()
23    DEFINE s INTEGER
24    LET s = db_start_transaction( )
25    UPDATE ...
26    LET s = SQLCA.SQLCODE
27    LET s = db_finish_transaction((s==0))
28    RETURN s
29 END FUNCTION

In this example, you see in the MAIN block that both functions a() and b() can be called separately. However, the transaction SQL commands will be used only if needed: When function a() is called, it starts the transaction, then calls b(), which does not start the transaction since it was already started by a(). When function b() is called directly, its starts the transaction.

The function db_finish_transaction() is called with the expression (s==0), which is evaluated before the call. This allows you to write in one line the equivalent of the following IF statement:

01 IF s==0 THEN
02    LET s = db_finish_transaction(1)
03 ELSE
04    LET s = db_finish_transaction(0)
05 END IF

Performance

Using Dynamic SQL

Although BDL allows you to write SQL statements directly in the program source as a part of the language (Static SQL), it is strongly recommended that you use Dynamic SQL instead when you are executing SQL statements within large program loops.  Dynamic SQL allows you to PREPARE the SQL statements once and EXECUTE N times, improving performance.

To perform Static SQL statement execution, the database interface must use the basic API functions provided by the database vendor. These are usually equivalent to the PREPARE and EXECUTE instructions. So when you write a Static SQL statement in your BDL program, it is actually converted to a PREPARE + EXECUTE.

For example, the following BDL code:

01 FOR n=1 TO 100
02    INSERT INTO tab VALUES ( n, c )
03 END FOR

is actually equivalent to:

01 FOR n=1 TO 100
02    PREPARE s FROM "INSERT INTO tab VALUES ( ?, ? )"
03    EXECUTE s USING n, c
04 END FOR

To improve the performance of the preceding code,  use a PREPARE instruction before the loop and put an EXECUTE  instruction inside the loop:

01 PREPARE s FROM "INSERT INTO tab VALUES ( ?, ? )"
01 FOR n=1 TO 100
03    EXECUTE s USING n, c
04 END FOR

Static SQL cache

Although we recommend using Dynamic SQL to improve performance when an SQL statement is executed multiple times within a program loop, you may not wish to review all your code.  You can improve program execution by enabling the STATIC SQL cache with the following FGLPROFILE entry:

dbi.sql.static.optimization.cache.size = <n>

This FGLPROFILE parameter defines the number of SQL statements that can be cached, for all active database connections. When this entry is set, the database interface caches Static SQL statements automatically to prepare them only when needed. By default this feature is disabled.

Using transactions

When you use an ANSI compliant RDBMS like Oracle or DB2, the database interface performs a COMMIT after each statement execution. This generates unnecessary database operations and can slow down big loops. To avoid this implicit COMMIT, you can control the transaction with BEGIN WORK / COMMIT WORK around the code containing a lot of SQL statement execution.

For example, the following loop will generate 2000 basic SQL operations ( 1000 INSERTs plus 1000 COMMITs ):

01 PREPARE s FROM "INSERT INTO tab VALUES ( ?, ? )"
01 FOR n=1 TO 100
03    EXECUTE s USING n, c   -- Generates implicit COMMIT
04 END FOR

You can improve performance if you put a transaction block around the loop:

01 PREPARE s FROM "INSERT INTO tab VALUES ( ?, ? )"
02 BEGIN WORK
03 FOR n=1 TO 100
04    EXECUTE s USING n, c   -- In transaction -> no implicit COMMIT
05 END FOR
06 COMMIT WORK

With this code, only 1001 basic SQL operations will be executed ( 1000 INSERTs plus 1 COMMIT ).

However, you must take care when generating large transactions because all modifications are registered in transaction logs. This can result in a lack of database server resources (INFORMIX transaction too long error, for example) when the number of operations is very big.  If the SQL operation does not require a unique transaction for database consistency reasons, you can split the operation into several transactions, as in the following example:

01 PREPARE s FROM "INSERT INTO tab VALUES ( ?, ? )"
02 BEGIN WORK
03 FOR n=1 TO 100
04    IF n MOD 10 == 0 THEN
05       COMMIT WORK
06       BEGIN WORK
07    END IF
08    EXECUTE s USING n, c   -- In transaction -> no implicit COMMIT
09 END FOR
10 COMMIT WORK

Portability

Writing portable SQL is mandatory if you want to succeed with different kind of database servers. This section gives you some hints to solve SQL incompatibility problems in your programs. Read this section carefully and review your program source code if needed. You should also read carefully the ODI Adaptation Guides which contain detailed information about SQL compatibility issues.

To easily detect SQL statements with specific syntax, you can use the -W stdsql option of fglcomp:

$ fglcomp -W stdsql orders.4gl
module.4gl:15: SQL Statement or language instruction with specific SQL syntax.

Remark: This compiler option can only detect non-portable SQL syntax in Static SQL statements.

Database entities

Most database servers can handle multiple database entities (you can create multiple 'databases'), but this is not possible with all engines:

When using a database server that does not support multiple database entities, you can emulate different databases with schemas, but this requires you to check for the database user definition. Each database user must have privileges to access any schema, and to see any table of any schema without needing to set a schema prefix before table names in SQL statements.

You can force the database driver to set a specific schema at connection with the following FGLPROFILE entry:

    dbi.database.<dbname>.schema = "<schema-name>"

Database users and security

To get the benefit of the database server security features, you should identify each physical user as a database user.

According to the type of server, you must do the following steps to create a database user:

1. Define the user as an operating system user.
2. Declare the user in the database server.
3. Grant database access privileges.

Each database server has its specific users management and data access privilege mechanisms. Check the vendor documentation for security features and make sure you can define the users, groups, and privileges in all database servers you want to use.

Data definition statements

When using Data Definition Statements like CREATE TABLE, ALTER TABLE, DROP TABLE, only a limited SQL syntax works on all database servers. Most databases support NOT NULL, CHECK, PRIMARY KEY, UNIQUE, FOREIGN KEY constraints, but the syntax for naming constraints is different.

The following statement works with most database servers and creates a table with equivalent properties in all cases:

CREATE TABLE customer
(
  cust_id INTEGER NOT NULL,
  cust_name CHAR(50) NOT NULL,
  cust_lastorder DATE NOT NULL,
  cust_group INTEGER,
    PRIMARY KEY (cust_id),
    UNIQUE (cust_name),
    FOREIGN KEY (cust_group) REFERENCES group (group_id)
)

Warning: Some engines like SQL Server have a different default behavior for NULL columns when you create a table. You may need to set up database properties to make sure that a column allows NULLs if the NOT NULL constraint is not specified.

When you want to create tables in programs using non-standard clauses (for example to define storage options), you must use Dynamic SQL and adapt the statement to the target database server.

Using portable data types

The ANSI SQL specification defines standard data types, but for historical reasons most databases vendors have implemented native (non-standard) data types. You can usually use a synonym for ANSI types, but the database server always uses the native types behind the scenes. For example, when you create a table with an INTEGER column in Oracle, the native NUMBER data type is used. In your programs, avoid BDL data types that do not have a native equivalent in the target database. This includes simple types like floating point numbers, as well as complex data types like INTERVAL. Numbers may cause rounding or overflow problems, because the values stored in the database have different limits. For DECIMALs, always use the same precision and scale for the BDL variables and the database columns.

To write portable applications, we strongly recommend using the following BDL data types only:

See the Adaptation Guides for more details about data type compatibility.

Concurrent data access

Data Concurrency is the simultaneous access of the same data by many users. Data Consistency means that each user sees a consistent view of the database. Without adequate concurrency and consistency controls, data could be changed improperly, compromising data integrity. To write interoperable BDL applications, you must adapt the program logic to the behavior of the database server regarding concurrency and consistency management. This issue requires good knowledge of multi-user application programming, transactions, locking mechanisms, isolation levels and wait mode. If you are not familiar with these concepts, carefully read the documentation of each database server which covers this subject.

Processes accessing the database can change transaction parameters such as the isolation level. The main problem is to find a configuration which results in similar behavior on every database engine. Existing BDL programs must be adapted to work with this new behavior. ALL programs accessing the same database must be changed. 

The following is the best configuration to get a common behavior with all types of database engines :

• The database must support transactions; this is usually the case.
• Transactions must be as short as possible (a few seconds).
• The Isolation Level must be at least "Read Committed" (= "Cursor Stability").
• The Wait Mode for locks must be "WAIT" or "WAIT n" (timeout).

Remarks: With this configuration, the locking granularity does not have to be at the row level. To improve performance with Informix databases, you can use the "LOCK MODE PAGE" locking level, which is the default.

The SQLCA register

SQLCA is the SQL Communication Area variable. SQLCA is a global record predefined by the runtime system, that can be queried to get SQL status information. After executing an SQL statement, members of this record contain execution or error data, but it is specific to Informix databases. For example, after inserting a row in a table with a SERIAL column, SQLCA.SQLERRD[2] contains the new generated serial number.

To write portable code, avoid using this variable. SQLCA.SQLCODE may be set to the Informix SQL error code, if the database driver can convert the native SQL error to an Informix SQL error. Additional members may be set, but that depends on the database server and database driver.

To identify SQL errors, you should use SQLSTATE: This variable holds normalized ISO SQL error codes. However, even if Genero database drivers are prepared to support this register, make sure the target databases you want to use do support standard SQLSTATE error codes:

Optimistic Locking

This section describes how to implement optimistic locking mechanisms in BDL applications. Optimistic locking is a portable solution to control simultaneous modification of the same record by multiple users.

Usually, BDL applications use SELECT FOR UPDATE to control the modification of a record by multiple users. This is called "pessimistic locking". The SELECT FOR UPDATE is executed before the interactive part of the code, to set an exclusive lock on the record during the user input :

1. A cursor with SELECT FOR UPDATE is opened.
   The lock is acquired or an error is raised if the record is already locked by another process.
2. The user enters modifications in the input form.
3. The UPDATE instruction is executed.
4. The transaction is committed or the cursor FOR UPDATE is closed.
   The lock is released.

Unfortunately, this is not a portable solution. The lock wait mode should preferably be "WAIT" for portability reasons. Pessimistic locking is based on a "NOT WAIT" mode to return control to the program if a record is already locked by another process. Therefore, following the portable concurrency model, the pessimistic locking mechanisms must be replaced by optimistic locking.

Optimistic locking uses Before Modification Values (BMVs) to verify whether the record has been updated by another process:

1. A SELECT is executed to fill a modification buffer ( usually, a BDL record variable ).
2. The buffer is copied into a backup buffer to keep Before Modification Values.
3. The user enters modifications in the input form; this updates the values in the modification buffer.
4. A transaction is started.
5. A SELECT FOR UPDATE is executed to put the current database values into a temp buffer.
6. If the status is NOTFOUND, the row has been deleted by another process, and the transaction is rolled back.
7. Otherwise, the program compares the temp buffer values with the backup buffer values (rec1.*==rec2.*)
8. If these values have changed, the row has been modified by another process, and the transaction is rolled back.
9. Otherwise, the UPDATE statement is executed.
10. The transaction is committed.

To compare 2 records, simply write:

01 IF new_record.* != bmv_record.* THEN
02    LET values_have_changed = TRUE
03 END IF

The optimistic locking technique could be implemented with a unique SQL instruction: An UPDATE could compare the column values to the BMVs directly (UPDATE ... WHERE kcol = kvar AND col1 = bmv.var1 AND ...). But, this is not possible when BMVs can be NULL. The database engine always evaluates conditional expressions such as "col=NULL" to FALSE. Therefore, you must use "col IS NULL" when the BMV is NULL. This means dynamic SQL statement generation based on the DMV values. Additionally, to use the same number of SQL parameters (? markers), you would have to use "col=?" when the BMV is not null and "col IS NULL and ? IS NULL" when the BMV is null. Unfortunately, the expression " ? IS [NOT] NULL " is not supported by all database servers (DB2 raises error SQL0418N).

To simplify the test of BMVs, a version column method can be used: You must add a NOT NULL INTEGER column to each table, with a default value of 1. This new column represents the version of the record, based on the modifications. Implement an update trigger to increment that column automatically (make sure this trigger is raised for each row). Any UPDATE statement will modify the record version. To implement optimistic locking, programs would just have to verify the record version column directly in the UPDATE statement. The NULL problem is resolved because the version number should never be NULL.

Auto-incremented columns

This section describes how to implement auto-incremented fields for portability.

INFORMIX provides the SERIAL data type which can be emulated by the database interface with most non-INFORMIX database engines. But, this requires additional configuration and maintenance tasks. If you plan to review the architecture of your programs, you should use this portable implementation instead of SERIALs emulated by the connectors when "ifxemul.serial" is true.

Solution 1: Use database specific serial generators.

Principle:

In accordance with the target database, you must use the appropriate native serial generation method. Get the database type with the db_get_database_type() function of fgldbutl.4gl and use the appropriate SQL statements to insert rows with serial generation.

Warning : Not all database engines provide a sequence generator. Check the documentation of your target database.

Implementation:

1. Create the database objects required for serial generation in the target database (for example, create tables with SERIAL columns in Informix, tables with IDENTITY columns in SQL Server and sequence generators in Oracle).
2. Adapt your BDL programs to use the native sequence generators in accordance with the database type.

BDL example:

01 DEFINE dbtype CHAR(3)
02 DEFINE t1rec RECORD
03           id    INTEGER,
04           name  CHAR(50),
05           cdate DATE
06     END RECORD
07
08 LET dbtype = db_get_database_type()
09
10 IF dbtype = "IFX" THEN
11    INSERT INTO t1 ( id, name, cdate )
12           VALUES ( 0, t1rec.name, t1rec.cdate )
13    LET t1rec.id = SQLCA.SQLERRD[2]
14 END IF
15 IF dbtype = "ORA" THEN
16    INSERT INTO t1 ( id, name, cdate )
17            VALUES ( t1seq.nextval, t1rec.name, t1rec.cdate )
18    SELECT t1seq.currval INTO t1rec.id FROM dual
19 END IF
20 IF dbtype = "MSV" THEN
21    INSERT INTO t1 ( name, cdate )
22            VALUES ( t1rec.name, t1rec.cdate )
23    PREPARE s FROM "SELECT convert(integer,@@identity)"
24    EXECUTE s INTO t1rec.id
25 END IF

Solution 2: Generate serial numbers by hand.

Purpose:

The goal is to generate unique INTEGER numbers. These numbers will usually be used for primary keys.

Prerequisites:

1. The database must use transactions. This is usually the case with non-INFORMIX databases, but INFORMIX databases default to auto commit mode. Make sure your INFORMIX database allows transactions.
2. The sequence generation must be called inside a transaction (BEGIN WORK / COMMIT WORK).
3. The lock wait mode must be WAIT. This is usually the case in non-INFORMIX databases, but INFORMIX defaults to NOT WAIT. You must change the lock wait mode with "SET LOCK MODE TO WAIT" or "WAIT seconds" when using INFORMIX.
4. Non-BDL applications or stored procedures must implement the same technique when inserting records in the table having auto-incremented columns.

Principle:

A dedicated table named "SEQREG" is used to register sequence numbers. The key is the name of the sequence. This name will usually be the table name the sequence is generated for. In short, this table contains a primary key that identifies the sequence and a column containing the last generated number.

The uniqueness is granted by the concurrency management of the database server. The first executed instruction is an UPDATE that sets an exclusive lock on the SEQREG record. When two processes try to get a sequence at the same time, one will wait for the other until its transaction is finished.

Implementation:

The "fgldbutl.4gl" utility library implements a function called "db_get_sequence" which generates a new sequence. You must create the SEQREG table as described in the fgldbutl.4gl source and make sure that every user has the privileges to access and modify this table.

BDL example:

01 DEFINE rec RECORD
02            id    INTEGER,
03            name  CHAR(100)
04      END RECORD
05 BEGIN WORK
06 LET rec.id = db_get_sequence( "CUSTID" )
07 IF rec.id>0 THEN                                    
08   INSERT INTO CUSTOMER ( CUSTID, CUSTNAME )
09          VALUES ( rec.id, rec.name )
10 ELSE
11   ERROR "cannot get new sequence number"
12 END
13 COMMIT WORK

Informix SQL ANSI Mode

INFORMIX allows you to create databases in ANSI mode, which is supposed to be closer to ANSI standard behavior. Other databases like ORACLE and DB2 are 'ANSI' by default.

If you are not using the ANSI mode with Informix, we suggest you keep the database as is, because turning an Informix database into ANSI mode can result in unexpected behavior of the programs.

Here are some ANSI mode issues extracted from the Informix books:

• Some actions, like CREATE INDEX will generate a warning but will not be forbidden.
• Buffered logging is not allowed to enforce data recovery. (Buffered logging provides better performance)
• The table-naming scheme allows different users to create tables without having to worry about name conflicts.
• Owner specification is required in database object names ( SELECT ... FROM "owner".table ). You must quote the owner name to prevent automatic translation of the owner name into uppercases : SELECT .. FROM owner.table -> SELECT .. FROM OWNER.table => table not found in database!
• Default privileges differ : When creating a table, the server grants privileges to the table owner and the DBA only. The same thing happens for the 'Execute' privilege when creating stored procedures.
• Default isolation level is REPEATABLE READ
• An error is generated if any character field is filled with a value that is longer than the field width.
• DECIMAL(p) (floating point decimals) are automatically converted to DECIMAL(p,0) (fixed point decimals).
• Closing a closed cursor generates an SQL error.

It will take more time to adapt the programs to the INFORMIX ANSI mode than using the database interface to simulate the native mode of INFORMIX.

Positioned Updates/Deletes

The "WHERE CURRENT OF cursor-name" clause in UPDATE and DELETE statements is not supported by all database engines.

Some database drivers can emulate WHERE CURRENT OF mechanisms by using rowids, but this requires additional processing. You should review the code to disable this option.

The standard SQL solution is to use primary keys in all tables and write UPDATE / DELETE statements with a WHERE clause based on the primary key:

01 DEFINE rec RECORD
02            id    INTEGER,
03            name  CHAR(100)
04      END RECORD
05 BEGIN WORK
06   SELECT CUSTID FROM CUSTOMER
07          WHERE CUSTID=rec.id FOR UPDATE
08   UPDATE CUSTOMER SET CUSTNAME = rec.name
09          WHERE CUSTID = rec.id
10 COMMIT WORK

However, with some database engines like Oracle, it may be faster to use the ROWID. That can be done specifically in parts of code that must execute rapidly. You must then adapt the update statement dynamically according to the database server type.

String literals in SQL statements

Some database servers like INFORMIX allow single and double quoted string literals in SQL statements, both are equivalent:

SELECT COUNT(*) FROM table
 WHERE col1 = "abc'def""ghi"
   AND col1 = 'abc''def"ghi'

Most database servers do not support this specific feature:

The ANSI SQL standards define double quotes as database object names delimiters, while single quotes are dedicated to string literals:

CREATE TABLE "my table" ( "column 1" CHAR(10) )
SELECT COUNT(*) FROM "my table" WHERE "column 1" = 'abc'

If you want to write a single quote character inside a string literal, you must write 2 single quotes:

... WHERE comment = 'John''s house'

Any double quoted string literals in Static SQL statements in your programs are converted to ANSI single quoted string literals by the fglcomp compiler. However, Dynamic SQL statements are not parsed by the compiler and therefore need to use single quoted string literals.

We recommend that you ALWAYS use single quotes for string literals and if needed, double quotes for database object names.

Naming database objects

Name syntax

Each type of database server has its own naming conventions for database objects (i.e. tables and columns):

Case sensitivity

Most database engines have case-sensitive object identifiers. In most cases, when you do not specify identifiers in double quotes, the SQL parser automatically converts names to uppercase or lowercase, so that the identifiers match if the objects are also created without double quoted identifiers.

CREATE TABLE Customer ( cust_ID INTEGER )

In Oracle, the above statement would create a table named "CUSTOMER" with a "CUST_ID" column.

The following table shows the case sensitivity features of each kind of database engine:

(1) If not ANSI database mode.
(2) Global parameter set at installation.

Warning: You must take care with database servers marked in red, because object identifiers are case sensitive and are not converted to uppercase or lowercase if not delimited by double quotes. This means that, by error,  you can create two tables with a similar name:

CREATE TABLE customer ( cust_id INTEGER )  -- first table
CREATE TABLE Customer ( cust_id INTEGER )  -- second table

Size of identifiers

The maximum size of a table or column name depends on the database server type. Some database engines allow very large names (256c), while others support only short names (30c max). Therefore, using short names is required for writing portable SQL. Short names also simplify SQL programs.

How to write SQL with portable object identifiers

We recommend that you use simple and short (<30c) database object names, without double quotes and without schema/owner prefix:

CREATE TABLE customer ( cust_id INTEGER )
SELECT customer.cust_id FROM table

You may need to set the database schema after connection, so that the current database user can see the application tables without specifying the owner/schema prefix each time.

Tip: Even if all database engines do not required unique column names for all tables, we recommend that you define column names with a small table prefix (for example, CUST_ID in CUSTOMER table).

Temporary tables

Not all database servers support temporary tables. The engines supporting this feature often provide it with a specific table creation statement:

Some database even have a different behavior when using temporary tables. For example, Oracle 9i supports a kind of temporary table, but it must be created as a permanent table. The table is not specific to a SQL session: it is shared by all processes - only the data is local to a database session.

You must review the programs using temporary tables, and adapt the code to use database specific temporary tables.

Outer joins

Old INFORMIX SQL outer joins specified with the OUTER keyword in the FROM part are not standard:

SELECT * FROM master, OUTER ( detail )
  WHERE master.mid = detail.mid
    AND master.cdate IS NOT NULL

Most recent database servers now support the standard ANSI outer join specification:

SELECT * FROM master LEFT OUTER JOIN detail ON (master.mid = detail.mid)
  WHERE master.cdate IS NOT NULL

You should use recent database servers and use ANSI outer joins only.

Sub-string expressions

Only INFORMIX supports sub-string specification with square brackets:

SELECT * FROM item WHERE item_code[1,4] = "XBFG"

However, most database servers support a function that extracts sub-strings from a character string:

Warning: INFORMIX allows you to update some parts of a [VAR]CHAR column by using the sub-string specification ( UPDATE tab SET col[1,2] ='ab' ). This is not possible with other databases.

You must review the SQL statements using sub-string expressions and use the database specific sub-string function.

You could also create your own SUBSTRING() user function in all databases that do not support this function, to have a common way to extract sub-strings. In Microsoft SQL Server, when you create a user function, you must specify the owner as prefix when using the function. Therefore, you should create a SUBSTRING() user function instead of SUBSTR().

Using rowids

Rowids are implicit primary keys generated by the database engine. Not all database servers support rowids:

Warning: INFORMIX fills the SQLCA.SQLERRD[3] register with the ROWID of the last updated row. This register is an INTEGER and cannot be filled with rowids having CHAR(*) type.

You must search for ROWID and SQLCA.SQLERRD[3] in your code and review the code to remove the usage of rowids.

MATCHES operator

The MATCHES operator allows you to scan a string expression:

SELECT * FROM customer WHERE customer_name MATCHES "A*[0-9]"

The MATCHES operator is specific to INFORMIX SQL. There is an equivalent standard operator: LIKE. MATCHES uses * and ? as wildcards. The equivalent wildcard in the LIKE operator are % and _. Character ranges [a-z] are not supported by the LIKE operator.

Search for MATCHES in your SQL statements and replace it by a standard LIKE expression.

Remark: The BDL language provides a MATCHES operator which is part of the runtime system. Do not confuse this with the SQL MATCHES operator, used in SQL statements. There is no problem in using the MATCHES operator of the BDL language.

GROUP BY clause

SELECT a, b, sum(c) FROM table GROUP BY 1,2

This is not possible with all database servers:

Search for GROUP BY in your SQL statements and use explicit column names.

LENGTH() function

Not all database servers support the LENGTH() function, and some have specific behavior:

Search for LENGTH in your SQL statements and review the code of the database specific function. You could also define your own LEN() user function to have a common function in all databases. In Microsoft SQL Server, when you create a user function, you must specify the owner as prefix when using the function. Therefore, you should create a LEN() user function instead of LENGTH().

Remark: The BDL language provides a LENGTH built-in function which is part of the runtime system. Do not confuse this with the SQL LENGTH() function, used in SQL statements. There is no problem in using the LENGTH() function of the BDL language. However, the LENGTH() function of the language returns zero when the string expression is NULL.