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 fgl_db_driver_type() built-in function and use the appropriate SQL statements to insert rows with serial generation.

This solution uses the native auto-increment feature of the target database and is fast at execution time, but is not very convenient as it requires different code for each database type to be written. Solutions for auto-incremented columns are vendor-specific. It is of course not realistic to use this solution in a large application with hundreds of tables.

Implementation

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

Example

DEFINE t1rec RECORD
          id    INTEGER,
          name  CHAR(50),
          cdate DATE
    END RECORD

CASE fgl_db_driver_type()
 WHEN "ifx"
   INSERT INTO t1 ( id, name, cdate )
          VALUES ( 0, t1rec.name, t1rec.cdate )
   LET t1rec.id = SQLCA.SQLERRD[2]
 WHEN "ora"
   INSERT INTO t1 ( id, name, cdate )
          VALUES ( t1seq.nextval, t1rec.name, t1rec.cdate )
   SELECT t1seq.currval INTO t1rec.id FROM dual
 WHEN "msv"
   INSERT INTO t1 ( name, cdate )
           VALUES ( t1rec.name, t1rec.cdate )
   PREPARE s FROM "SELECT @@IDENTITY"
   EXECUTE s INTO t1rec.id
END CASE
As you can see in this example, this solution requires database engine specific coding. Querying the last generated serial can be centralized in a function, but the insert statements would still need to be specific to the type of database.