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:

The table column must be of type SERIAL, SERIAL8 or BIGSERIAL.
To generate a new serial, no value or a zero value is specified in the INSERT statement:
```
INSERT INTO tab1 ( c ) VALUES ( 'aa' );
INSERT INTO tab1 ( k, c ) VALUES ( 0, 'aa' );
```
After INSERT, the new SERIAL value is provided in SQLCA.SQLERRD[2], while the new SERIAL8 and BIGSERIAL value must be fetched with a SELECT dbinfo('bigserial') query.

Informix allows you to insert rows with a value other than zero for a serial column. Using an explicit value automatically increments 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

Genero db supports serial types like Informix.

Solution

When using Genero db, the SERIAL data type works the same as in Informix. After an insert, SQLCA.SQLERRD[2] holds the last generated serial value.

CREATE [TEMP] TABLE with a SERIAL column works as in Informix.

For SQL portability, INSERT statements should 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