Get index field(s) from TZAbstractRODataSet

[aehimself]

Hello,

I had this need already but always could get around it with bookmarks instead. I have a query (which can be a TZReadOnlyQuery or TZQuery) with an "unknown" select query. I need to find which fields are index fields on a database level, based on which values I can find the exact same record in an other query.
There are a couple of properties under TField/TZField but they seem to be unused (always return the same value).

Any ideas? :)

Cheers!

[marsupilami]

My first Idea was to take a look at TZColumnInfo and see where that leads. But TZColumnInfo doesn't store that kind of information.

My second guess was to take a look what the generic resolver (TZGenerateSQLCachedResolver in ZDbcGenericResolver.pas) does. The Generic Resolver doesn't look at any inernal information at all. It just searches the dataset for the first column that has a table name attached. Then it feeds the Catalog, Schema and TableName information into IZDatabaseMetadata.GetPrimaryKeys.

So - what you could do is:
- Check if all columns are from the same table
- Use GetPrimaryKeys to get the primary key of that table
- Check if all the fields in GetPrimaryKeys are available
- if not all fields of the primary key are available, you might want to check for unique indices using GetIndexInfo

[aehimself]

I solved my issue by imported and exported keys in the mean time which seem to be doing the trick for my purpose.
However it would be really nice if we could get TField.IsIndexField working under Zeos :)

[Fr0sT]

I solved my issue by imported and exported keys in the mean time which seem to be doing the trick for my purpose.
However it would be really nice if we could get TField.IsIndexField working under Zeos

Not reliable in general... A field in a query could be taken from any table, aliased, taken from a view and so on.

[marsupilami]

However it would be really nice if we could get TField.IsIndexField working under Zeos

I am not sure. Embarcadero says the following about the IsIndexField property:

Inspect the IsIndexField to determine if a field is used in the dataset's current index.

Soo - translated to Zeos the IsIndexField property should be yes if the field is listed in TZQuery.SortedFields or in IndexFieldName? But then this doesn't reflect the primary key in any way?

I solved my issue by imported and exported keys

How do you do that if the primary key of a table doesn't get used in any foreign key? Does it work then?

Not reliable in general... A field in a query could be taken from any table, aliased, taken from a view and so on.

On the DBC layer the TZGenericResolver does exactly that. It looks for a table name and then queries for the primary key.

[aehimself]

How do you do that if the primary key of a table doesn't get used in any foreign key? Does it work then?

No, not at all. In that case repositioning won't be possible (or inaccurate) but for my purpose it's not a huge deal. I'll take a look at the advices above once my new feature is completely error-free :)

I'm working on a "database browser" in my tool for people who don't have knowledge about databases but still need to interact with data. You select a starting table and then "walk through" it's relations:

Capture1.PNG

Repositioning is needed when in-line edit is toggled as in this case I'm replacing the TZQuery object with a TZReadOnlyQuery for performance improvement. And it's nice if they end up with the same selection once I did that.
However I just recognized an other bug - I'm only repositioning to ONE record and since multi-select is allowed...

See, long way to go :D

[Fr0sT]

On the DBC layer the TZGenericResolver does exactly that. It looks for a table name and then queries for the primary key.

But how it looks for table name? And how it could know when key field is aliased?
Anyway there are VIEWS that could contain literally everything