Solution 3: Use native SEQUENCE database objects

Principle

Most recent database engines support SEQUENCE database objects. If all database server types you want to use support sequences, it is recommended that you use this solution.

Implementation

  1. Create a SEQUENCE object for each table that previously used 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. Consider writing a function to retrieve a new sequence number, using dynamic SQL to pass the name of the sequence as parameter, and adapt to the target database specifics to retrieve a single row (see example below).

Example

MAIN
  DEFINE item_rec RECORD
           item_num BIGINT,
           item_name VARCHAR(40)
         END RECORD
  DATABASE test1
  CREATE TABLE item (
           item_num BIGINT NOT NULL PRIMARY KEY,
           item_name VARCHAR(50)
          )
  CALL sequence_create("item")
  LET item_rec.item_num = sequence_next("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

PRIVATE FUNCTION is_sql_server() RETURNS BOOLEAN
  RETURN (fgl_db_driver_type()=="esm"
       OR fgl_db_driver_type()=="ftm"
       OR fgl_db_driver_type()=="snc")
END FUNCTION

FUNCTION sequence_create(tabname STRING)
  DEFINE sql STRING
  IF is_sql_server() THEN
     LET sql = "CREATE SEQUENCE "||tabname||"_seq START WITH 1"
  ELSE
     LET sql = "CREATE SEQUENCE "||tabname||"_seq"
  END IF
  EXECUTE IMMEDIATE sql
END FUNCTION

FUNCTION sequence_next(tabname STRING) RETURNS BIGINT
  DEFINE sql STRING, newseq BIGINT
  CASE
     WHEN fgl_db_driver_type()=="pgs"
          LET sql = "SELECT nextval('"||tabname||"_seq')"||unique_row_condition()
     WHEN is_sql_server()
          LET sql = "SELECT NEXT VALUE FOR "||tabname||"_seq"
     OTHERWISE
          LET sql = "SELECT "||tabname||"_seq.nextval "||unique_row_condition()
  END CASE
  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() RETURNS STRING
    CASE fgl_db_driver_type()
        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 "hdb" RETURN " FROM dummy"
        OTHERWISE RETURN " "
    END CASE
END FUNCTION