SQL portability / Auto-incremented columns (serials) |
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.
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