Please help: Question about Oracle-RowId

invent · Post by **invent** » Tue 15 Jun 2010 17:23

Hi,

I have a big problem using Delphi 7, Oracle 8 and UniDAC 2.70.09:

On 2 different Oracle-Servers with the same Tables I get different cols for my tables. For example:

Server 1: MyUniTable.Fields[0].FieldName is 'CustomerNr'

Server 2: MyUniTable.Fields[0].FieldName is 'ROWID'
and MyUniTable.Fields[1].FieldName is 'CustomerNr'

Now I can change the Code to "MyUniTable.FieldByName ('CustomerNr')", but i have to change hundreds of lines...

Is there any way in TUniTable, TQuery to make ROWID on Server 2 unvisible? Or is it a Config-Problem of the Oracle-Server?

Thanks in advance for any information.

Kind regards,
Gerd Brinkmann
invent GmbH

bork · Post by **bork** » Wed 16 Jun 2010 13:54

Hello

Please check the KeyFields property of your TUniTable. If the KeyFields property is set to "RowID" then the "RowID" field will be added before all fields in the table. Also it can occur if the identical tables have primary key in the first database and in the other database primary key is not created. In case when primary key is not created the "RowID" field will be added before all fields to make this table updatable.

To resolve this issue:
- check the KeyFields property in the run-time: if it has the same value for both databases
- check primary key for identical tables in both databases: identical tables should have identical primary keys

invent · Post by **invent** » Wed 16 Jun 2010 17:01

Hello bork,

thank you for the fast answer. I checkd both servers, all primary keys are well defined. The property KeyFields is not set manually in all TUniTables.

1. My first test was ShowMessage (MyUniTable.KeyFields).

Result on Server 1: 'CUSTOMERNR'
Result on Server 2: '' (empty string)

2. Than I started DBMonitor to watch TUniTable and there are two different SQL-Statements :

On Server 1 TUniTable makes:
SELECT T.* FROM CUSTOMERS T WHERE CUSTOMERNR = '10008'

On Server 2 TUniTable makes
SELECT T.RowId, T.* CUSTOMERS T WHERE CUSTOMERNR = '10008'

I think, you are right, the TUniTable cannot find the Primary Keys on server 2. But why ???

For your information: On both servers are 298 tables, all created in the same way. All 298 have a primary key and TUniTable.KeyFields gives an empty string on Server 2.

I hope, you have one or two more ideas what I can do.

Thanks in advance for any information.

Kind regards,
Gerd Brinkmann
invent GmbH

bork · Post by **bork** » Thu 17 Jun 2010 16:38

Hello

Please check the result of the following query:

Code: Select all

SELECT '' TABLE_CATALOG, IC.TABLE_OWNER TABLE_SCHEMA, IC.TABLE_NAME, '' INDEX_CATALOG, IC.INDEX_OWNER INDEX_SCHEMA, IC.INDEX_NAME, IC.COLUMN_NAME, IC.COLUMN_POSITION, DECODE(IC.DESCEND, 'ASC', 0, 1) DESCENDING 
FROM SYS.ALL_IND_COLUMNS IC, SYS.ALL_INDEXES I 
WHERE IC.TABLE_OWNER = '...' AND -- your username in the upper case 
      IC.TABLE_NAME = '...' AND -- your table name in the upper case 
      I.UNIQUENESS = 'UNIQUE' AND 
      I.OWNER = IC.INDEX_OWNER AND 
      I.INDEX_NAME = IC.INDEX_NAME 
ORDER BY IC.INDEX_OWNER, IC.TABLE_OWNER, IC.TABLE_NAME, IC.INDEX_NAME, IC.COLUMN_POSITION

This query should return the same result for both databases.

Also please check that your primary keys are not disabled.

invent · Post by **invent** » Tue 06 Jul 2010 17:07

Hi bork,

thanks for your support. I checked your query, but it doesn't really works. The result is in every case empty.

So i sat down and removed every TUniTable.Fields[0] from my code. Now the software works fine again.

Kind regards,
Gerd Brinkmann
invent GmbH

bork · Post by **bork** » Wed 07 Jul 2010 14:13

Hi

This query may return no results if TABLE_OWNER or TABLE_NAME is defined incorrectly (they are case sensitive).

It is good to see that this problem was solved. If any other questions come up, please contact me.