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 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. 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
  DEFINE i INT
  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()
  RETURN (fgl_db_driver_type()=="esm" OR fgl_db_driver_type()=="snc")
END FUNCTION

FUNCTION sequence_create(tabname)
  DEFINE tabname STRING
  IF is_sql_server() THEN
     EXECUTE IMMEDIATE "CREATE SEQUENCE item_seq START WITH 1"
  ELSE
     CREATE SEQUENCE item_seq
  END IF
END FUNCTION

FUNCTION sequence_next(tabname)
  DEFINE tabname STRING
  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()
    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"
        OTHERWISE RETURN " "
    END CASE
END FUNCTION