Delphi - Oracle RowID ignored with dbGO/ADO

user_1009073 0
Viewed 106

Delphi Rio - I have dbGO/ADO configured with Oracle (using Oracle's connector, not Microsoft's). I am working with a simple query.

mySQL := 'select REGISTRY_ID, TERRITORY_ID, ACCOUNT_NAME from ACCOUNTS_SI';

ADO_Q1.Close;
ADO_Q1.SQL.Clear;
ADO_Q1.SQL.Add(mySQL);
ADO_Q1.Open;

ShowMessage(IntToStr(ADO_Q1.FieldList.Count));

This works exactly as expected, and the Message popup shows 3. If I add the ROWID as a column, it appears to be totally ignored. The query below runs, but still shows 3 columns.

mySQL := 'select ROWID, REGISTRY_ID, TERRITORY_ID, ACCOUNT_NAME from ACCOUNTS_SI';

I have even tried renaming it.

mySQL := 'select ROWID as MYKEY_ID, REGISTRY_ID, TERRITORY_ID, ACCOUNT_NAME from ACCOUNTS_SI';

ROWID as a column, while completely legal, is being filtered out.

When I look at Oracle's documentation, it seems to show that RowID is perfectly valid.

In order for the OleDbDataAdapter.Update() method to properly update Oracle with changes made in the DataTable, the DataTable must contain a primary key of a database table. If the database table does not contain a primary key, the ROWID must be selected explicitly when populating the DataTable, so that the ROWID can be used to uniquely identify a row when updating a row in the database.

Is there a way around this? How can I use RowID with dbGo? (Yes, I am ware of issues using RowID...but this is a single user, local DB.)

ado delphi oracle
1 Answers

Looks like it uses ROWID for internal purposes like cursor navigating, so try to add 2 ROWID columns and wrap one in the nested subquery:

mySQL := 'select RID, REGISTRY_ID, TERRITORY_ID, ACCOUNT_NAME from (select ROWID, ROWID as RID, s.* from ACCOUNTS_SI s';

Update (since previous one didn't help) Another variant:

mySQL := 'select RowIDtoChar(ROWID) RID, REGISTRY_ID, TERRITORY_ID, ACCOUNT_NAME from ACCOUNTS_SI';
user_4291000
Related