SERIAL data types

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:

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:

Solution

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