Delphi XE7 TFDTable View RowID error

Question

I'm converting a Delphi 5 / BDE application to Delphi XE7 / FireDAC. One of my forms has a TFDTable component that points to an Oracle view containing a group by clause in its create statement.

This used to work fine in the BDE application, but with FireDAC I'm getting this error.

ORA-01446: cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc.

I understand the error I'm getting from Oracle, but I'm not selecting ROWID, FireDAC is! Is there a property in the TFDTable that I can set to prevent it from adding ROWID to the query? If not, how am I supposed to use this view?

Answer 1

Include fiMeta in FetchOptions.Items.

TFDQuery, TFDTable, TFDMemTable, and TFDCommand automatically retrieve the unique identifying columns (mkPrimaryKeyFields) for the main (first) table in the SELECT ... FROM ... statements, when fiMeta is included in FetchOptions.Items. Note:

mkPrimaryKeyFields querying may be time consuming; the application may need to explicitly specify unique identifying columns, when FireDAC fails to determine them correctly.

To explicitly specify columns, exclude fiMeta from FetchOptions.Items, and use one of the following options:

set UpdateOptions.KeyFields to a ';' separated list of column names; include pfInKey into the corresponding TField.ProviderFlags property.

When the application creates persistent fields, then initially TField.ProviderFlags will be set correctly. After that, automatic field setup will not happen, when the DB structure or query is changed. You should manually update ProviderFlags to adjust the column list. Also, if the primary key consists of several fields, then all of them must be included into persistent fields. Row Identifying Columns

Alternatively, a row identifying column may be included into the SELECT list. When FireDAC founds such columns, it will not retrieve mkPrimaryKeyFields metadata and it will use this column. The supported DBMSs are the following:

DBMS Row identifying column

Firebird DB_KEY

Informix ROWID

Interbase DB_KEY / RDB$DB_KEY

Oracle ROWID

PostgreSQL OID. The table must be created with OIDs.

SQLite ROWID

Source : http://docwiki.embarcadero.com/RADStudio/XE8/en/Unique_Identifying_Fields_%28FireDAC%29

Answer 2

FireDAC fetches ROWID because it tries to identify tuples in the resultset for possible updates. For stopping that just enable the ReadOnly option which will properly make the grouped view resultset read only (properly as one cannot just identify particular tuples if they are grouped in a resultset for updating).

The SQL command is generated in TFDPhysCommandGenerator.GenerateSelectTable method, if you wanted to know the source of this problem. There is appended generic unique tuple identifier to the select list according to the ReadOnly property setting (which is ROWID for Oracle DBMS).