Informix® supports the SERIAL, SERIAL8 and BIGSERIAL data types to produce automatic integer sequences. SERIAL is based on INTEGER (32 bit), while SERIAL8 and BIGSERIAL can store 64 bit integers:
INSERT INTO tab1 ( c ) VALUES ( 'aa' ) INSERT INTO tab1 ( k, c ) VALUES ( 0, 'aa' )
Informix allows you to insert rows with a value different from zero for a serial column. Using an explicit value will automatically increment the internal serial counter, to avoid conflicts with future INSERTs that are using a zero value:
CREATE TABLE tab ( k SERIAL); -- internal counter = 0 INSERT INTO tab VALUES ( 0 ); -- internal counter = 1 INSERT INTO tab VALUES ( 10 ); -- internal counter = 10 INSERT INTO tab VALUES ( 0 ); -- internal counter = 11 DELETE FROM tab; -- internal counter = 11 INSERT INTO tab VALUES ( 0 ); -- internal counter = 12
Microsoft™ SQL SERVER IDENTITY columns:
CREATE TABLE tab1 ( k integer identity, c char(10) )
CREATE TABLE tab1 ( k integer identity(100,2), ...
INSERT INTO tab1 ( c ) VALUES ( 'aaa' )
SELECT SCOPE_IDENTITY()
The @@IDENTITY global T-SQL variable is not recommended, as it is scope-less.
SET IDENTITY_INSERT tab1 ON INSERT INTO tab1 ( k, c ) VALUES ( 100, 'aaa' ) SET IDENTITY_INSERT tab1 OFF
Informix SERIALs and MS SQL SERVER IDENTITY columns are quite similar; the main difference is that MS SQL SERVER does not allow you to use the zero value for the identity column when inserting a new row.
This problem cannot be resolved with triggers because Microsoft SQL SERVER does not support row-level triggers (INSERT Triggers are fired only once per INSERT statement).
To emulation Informix serials, you can use IDENTITY columns (1) or insert triggers based on the SERIALREG table (2). The first solution is faster, but does not allow explicit serial value specification in insert statements; the second solution is slower but allows explicit serial value specification.
With the following fglprofile entry, you define the technique to be used for SERIAL emulation:
dbi.database.dbname.ifxemul.datatype.serial.emulation = {"native"|"regtable"}
The ' native ' value defines the IDENTITY column technique and the ' regtable ' defines the trigger technique.
This entry must be used with:
dbi.database.dbname.ifxemul.datatype.serial = {true|false}
If this entry is set to false, the emulation method specification entry is ignored.
When no entry is specified, the default is SERIAL emulation enabled with 'native' method (IDENTITY-based).
In database creation scripts, all SERIAL[(n)] data types must be converted by hand to INTEGER IDENTITY[(n,1)] data types, while BIGSERIAL[(n)] data types must be converted by hand to BIGINT IDENTITY[(n,1)] data types.
Tables created from the BDL programs can use the SERIAL data type: When a BDL program executes a CREATE [TEMP] TABLE with a SERIAL column, the database interface automatically converts the "SERIAL[(n)]" data type to "INTEGER IDENTITY[(n,1)]".
In BDL, the new generated SERIAL value is available from the SQLCA.SQLERRD[2] variable. This is supported by the database interface which performs a "SELECT SCOPE_IDENTITY()". However, SQLCA.SQLERRD[2] is defined as an INTEGER, it cannot hold values from BIGINT identity columns. If you are using BIGINT IDENTITY columns, you must retrieve the last generated serial with the SCOPE_IDENTITY() SQL function.
By default (see SET IDENTITY_INSERT), MS SQL SERVER does not allow you to specify the IDENTITY column in INSERT statements; You must convert all INSERT statements to remove the identity column from the list.
For example, the following statement:
INSERT INTO tab (col1,col2) VALUES (0, p_value)
must be converted to:
INSERT INTO tab (col2) VALUES (p_value)
Static SQL INSERT using records defined from the schema file must also be reviewed:
DEFINE rec LIKE tab.* INSERT INTO tab VALUES ( rec.*) -- will use the serial column
must be converted to:
INSERT INTO tab VALUES rec.* -- without braces, serial column is removed
Since 2.10.06, SELECT * FROM table INTO TEMP with original table having an IDENTITY column is supported: The database driver converts the Informix SELECT INTO TEMP to the following sequence of statements:
See also temporary tables.
First, you must prepare the database and create the SERIALREG table as follows:
CREATE TABLE serialreg ( tablename VARCHAR(50) NOT NULL, lastserial BIGINT NOT NULL, PRIMARY KEY ( tablename ) )
The SERIALREG table and columns have to be created with lower case names, since the SQL SERVER database is created with case sensitive names, because triggers are using this table in lower case.
In database creation scripts, all SERIAL[(n)] data types must be converted to INTEGER data types, BIGSERIAL must be converted to BIGINT and you must create one trigger for each table. To know how to write those triggers, you can create a small Genero program that creates a table with a SERIAL column. Set the FGLSQLDEBUG environment variable and run the program. The debug output will show you the native trigger creation command.
Tables created from the BDL programs can use the SERIAL data type. When a BDL program executes a CREATE [TEMP] TABLE with a SERIAL column, the database interface automatically converts the "SERIAL[(n)]" data type to "INTEGER" and creates the insert triggers.
INSERT INTO tab (col1,col2) VALUES ( NULL,'data')
This behavior is mandatory in order to support INSERT statements which do not use the serial column:
INSERT INTO tab (col2) VALUES ('data')
Check if your application uses tables with a SERIAL column that can contain a NULL value.