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 INSERT statements 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
SQLite supports the AUTOINCREMENT attribute for columns:
SELECT seq FROM sqlite_sequence WHERE name='table_name';
When using SQLite, the SERIAL data type is converted to INTEGER PRIMARY KEY AUTOINCREMENT.
The SQLCA.SQLERRD[2] register is filled automatically after each INSERT with the last generated number, by fetching the value from the sqlite_sequence table.
SQLite (V 3.6) does not support auto-incremented BIGINTs. Therefore, BIGSERIAL or SERIAL8 cannot be converted.
Because SQLite does not behave like Informix regarding zero and NULL value specification for auto-incremented columns, all INSERT statements must be reviewed to remove the SERIAL column from the list.
For example, the following statement:
INSERT INTO tab (col1,col2) VALUES ( 0 , p_value)
Can 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
Can be converted to:
INSERT INTO tab VALUES rec.* -- without braces, serial column is removed