SQL portability / Auto-incremented columns (serials) |
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, but is not very convenient as it requires to write different code for each database type. However, it is covered here to make you understand that each database vendor has it's own specific solution for auto-incremented columns. It is of course not realistic to use this solution in a large application with hundreds of tables.
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 CASEAs 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.