ROWID columns
Informix®
When creating a table, Informix automatically adds a
ROWID integer column (applies to non-fragmented tables only).
The ROWID column is auto-filled with a unique number and can be used like a
primary key to access a given row.
ROWID usage was a common practice in the early days of Informix 4GL programming. Today it is
recommended to define all your database tables with a PRIMARY
KEY to uniquely identify rows.With Informix, the SQLCA.SQLERRD[6]
register contains the ROWID of the last modified row.
PostgreSQL
PostgreSQL tables can be created with an OID (Object Identifier) column, that is
similar to Informix ROWID columns.
The type of PostgreSQL OID columns is INTEGER.
OID columns are no
longer enabled by default. To have OID columns created for tables, you need to set
the default_with_oid parameter to "on" in the
postgresql.conf file.The PostgreSQL C API provides the PQoidValue() function, to get the
OID of the last inserted row. Unlike Informix SQLCA.SQLERRD[6], PostgreSQL does not return the
OID of the last row modified by an UPDATE, or removed by a
DELETE.
Solution
The database automatically converts ROWID keywords to OID for
PostgreSQL.
If PostgreSQL OID columns are enabled in the database, SQL statements such as
"SELECT ROWID FROM" and "UPDATE .. WHERE ROWID = ?" will work as
with Informix.
To enable OID columns, the default_with_oid PostgreSQL server
parameter must be set to "on". See Database configuration and design tasks for
more details.
SQLCA.SQLERRD[6] is partly supported: It works only for
INSERT statements. As PostgreSQL does not return the OID of the last row modified
by an UPDATE, or removed by a DELETE, this register will not
contain the OID of the last modified or deleted row. All references to
SQLCA.SQLERRD[6] should be reviewed.As a general programming pattern, consider using PRIMARY KEY columns to identify
database table rows.
ROWID keyword
translation can be controlled with the following FGLPROFILE
entry:dbi.database.dsname.ifxemul.rowid = { true | false }