Solution 3: Use native SEQUENCE database objects

Principle

More an more database engines support SEQUENCE database objects; If all database server types you want to use do support sequences, you should use this solution.

Implementation

  1. Create a SEQUENCE object for each table using previously a SERIAL column in the IBM® Informix® database.
  2. In database creation scripts (CREATE TABLE), replace all SERIAL types by INTEGER (or BIGINT if you need large integers).
  3. Adapt your programs to retrieve a new sequence before inserting a new row.
IMPORT FGL fgldbutl

DEFINE dbtype CHAR(3)

MAIN
  DEFINE item_rec RECORD
           item_num BIGINT,
           item_name VARCHAR(40)
         END RECORD
  DEFINE i INT
  DATABASE test1
  LET dbtype = db_get_database_type()
  CREATE TABLE item (
           item_num BIGINT NOT NULL PRIMARY KEY,
           item_name VARCHAR(50)
          )
  CREATE SEQUENCE item_seq
  LET item_rec.item_num = new_seq("item")
  DISPLAY "New sequence: ", item_rec.item_num
  LET item_rec.item_name = "Item#" || item_rec.item_num
  INSERT INTO item VALUES ( item_rec.* )
  DROP TABLE item
  DROP SEQUENCE item_seq
END MAIN

FUNCTION new_seq(tabname)
  DEFINE tabname STRING
  DEFINE sql STRING
  DEFINE newseq BIGINT
  IF dbtype=="PGS" THEN
    LET sql = "SELECT nextval('"||tabname||"_seq')"||unique_row_condition()
  ELSE
    LET sql = "SELECT "||tabname||"_seq.nextval "||unique_row_condition()
  END IF
  PREPARE seq FROM sql
  IF SQLCA.SQLCODE!=0 THEN RETURN -1 END IF
  EXECUTE seq INTO newseq
  IF SQLCA.SQLCODE!=0 THEN RETURN -1 END IF
  RETURN newseq
END FUNCTION

FUNCTION unique_row_condition()
    CASE dbtype
        WHEN "IFX" RETURN " FROM systables WHERE tabid=1"
        WHEN "DB2" RETURN " FROM sysibm.systables WHERE name='SYSTABLES'"
        WHEN "PGS" RETURN " FROM pg_class WHERE relname='pg_class'"
        WHEN "ORA" RETURN " FROM dual"
        WHEN "ADS" RETURN " FROM dual"
        OTHERWISE RETURN " "
    END CASE
END FUNCTION