Can't edit/update local query

[P.S.Bell patbell.co.uk>]

I have a local Paradox database with a master and a detail
table.

Two problems - first, I set the master datasource for the detail
table but when at runtime I try to sort it setting the
Indexfieldname property to a secondary index but then the
connection with the master detail is lost.

To get around this I used a query (sql something like select *
from detail where masterID=DetailID)

Fine - until I add 'order by secondaryfieldname' The query is
readonly.

According to the help the query becomes read only because of
"ORDER BY clauses not based on an index" - the order by field
_is_ indexed..

RequestLive is true.

To make it simpler I find even in a simple query with no 'where'
at all - adding an order by for an indexed field still makes it
read only.


Pat Bell

[Wayne Niddery (TeamB)]

Post by P.S.Bell patbell.co.uk>
I have a local Paradox database with a master and a detail
table.
Two problems - first, I set the master datasource for the detail
table but when at runtime I try to sort it setting the
Indexfieldname property to a secondary index but then the
connection with the master detail is lost.
To get around this I used a query (sql something like select *
from detail where masterID=DetailID)
Fine - until I add 'order by secondaryfieldname' The query is
readonly.
According to the help the query becomes read only because of
"ORDER BY clauses not based on an index" - the order by field
_is_ indexed..
RequestLive is true.
To make it simpler I find even in a simple query with no 'where'
at all - adding an order by for an indexed field still makes it
read only.

This has always been one of the major limitations with Paradox (and similar)
databases. You can only have one index "live" at any time, and in the case
of master-detail link, it *must* be the index that allows lookups for that
link.

What I always did (when I still used Paradox/BDE) was to *not* create
master-detail links at design time, but to handle it in code. It's really
not that hard and allows the detail to be handled (e.g. sorted) separately
from the master. You can use TTable or TQuery, I generally used TQuerys.

Assuming a TQuery for the detail dataset, let's it's customer orders, I
would set it to:
select * from orders
where customerid = :customerid
order by [desired fields here]

Now in the master dataset's AfterScroll event, it is easy to close the
detail query, assign the parameter to the master's customer id, and open the
query again.

[P.S.Bell patbell.co.uk>]

Thats what I did - handle master detail in the code - but then I got stuck with
a readonly query when I use order by - even though help says I can do this if
the order by is indexed.

Post by Wayne Niddery (TeamB)

Post by P.S.Bell patbell.co.uk>
I have a local Paradox database with a master and a detail
table.
Two problems - first, I set the master datasource for the detail
table but when at runtime I try to sort it setting the
Indexfieldname property to a secondary index but then the
connection with the master detail is lost.
To get around this I used a query (sql something like select *
from detail where masterID=DetailID)
Fine - until I add 'order by secondaryfieldname' The query is
readonly.
According to the help the query becomes read only because of
"ORDER BY clauses not based on an index" - the order by field
_is_ indexed..
RequestLive is true.
To make it simpler I find even in a simple query with no 'where'
at all - adding an order by for an indexed field still makes it
read only.

This has always been one of the major limitations with Paradox (and similar)
databases. You can only have one index "live" at any time, and in the case
of master-detail link, it *must* be the index that allows lookups for that
link.
What I always did (when I still used Paradox/BDE) was to *not* create
master-detail links at design time, but to handle it in code. It's really
not that hard and allows the detail to be handled (e.g. sorted) separately
from the master. You can use TTable or TQuery, I generally used TQuerys.
Assuming a TQuery for the detail dataset, let's it's customer orders, I
select * from orders
where customerid = :customerid
order by [desired fields here]
Now in the master dataset's AfterScroll event, it is easy to close the
detail query, assign the parameter to the master's customer id, and open the
query again.

Pat Bell

[Franz-Leo Chomse]

On Wed, 21 May 2008 17:13:28 +0100, P.S.Bell

Post by P.S.Bell patbell.co.uk>
Thats what I did - handle master detail in the code - but then I got stuck with
a readonly query when I use order by - even though help says I can do this if
the order by is indexed.

It doesn't matter whether there is an index suitable for the order by
clause, if the SQL engine decides to use another one to resolve the
query. You can't force an SQL engine to use the index you want.

Regards from Germany

Franz-Leo

[Wayne Niddery (TeamB)]

Post by P.S.Bell patbell.co.uk>
Thats what I did - handle master detail in the code - but then I got stuck with
a readonly query when I use order by - even though help says I can do this if
the order by is indexed.

If the order by fields are indexed, make sure you set that as the active
index for the TQuery.

If that doesn't work, try Bill's suggestion - that makes one index server
the master lookup and the order by.

If that doesn't work, then don't try to use this query for updating, let it
stay read-only (presumably it is displaying results in a grid?) and use
another query that selects just the single record to be updated and provide
a form for the user to edit that one record.

[Bill Todd [TeamB]]

There are only two solutions that I know of. The first is to create an
index on the detail table on LindField, SortField1, SortField2 and use
that index for the master detail link between the TTables. The other
alternative is to use TClientDataSets.