In using a DBISAM Query, I've recently noticed that I cannot edit what
appears to be a live result set when including an 'Order By' clause in
the SQL. If I remove or don't use an 'Order By'clause, I do not
experience this problem. Since I prefer using Queries over tables and am
not keen on using Tables w/filters, I was just wondering if any of you
have experienced similar behavior in using DBISAMQuery with this SQL
quirk, and what you may have done to address this particular behavior.

Marty


This is from memory but if you want a live result set with an order by there must be an index matching the order by clause.

If you thing there is and its still not live can you post the query plan?

Roy Lambert [Team Elevate]

"Marty Potokar" <martinpotokar@sbcglobal.net> wrote in message
news:5DB673AC-D9DA-4716-9B55-30A36791AB8C@news.elevatesoft.com...
> In using a DBISAM Query, I've recently noticed that I cannot edit what
> appears to be a live result set when including an 'Order By' clause in the
> SQL. If I remove or don't use an 'Order By'clause, I do not experience
> this problem. Since I prefer using Queries over tables and am not keen on
> using Tables w/filters, I was just wondering if any of you have
> experienced similar behavior in using DBISAMQuery with this SQL quirk, and
> what you may have done to address this particular behavior.

A live query is basically a tTable. You need for the "order by" to match
exactly one of the existing table indexes.

Robert

Roy,

I am using a secondary index, customer_name, created in a DBISAM
Database Table named Clients. The SQL is very basic and reads as follows:

Select * From Clients Where Type = 1 Order by Name

Once again, if I remove the Order By clause, I can edit any record
produced by the Query's Live Result Set without problem. In contrast, if
I attempt to edit then post a record produced by the Query using the
Order By clause, the record fails to make the change(s. I should also
add that I am working in Delphi 2007 for Win32 Apps.

Roy Lambert wrote:
> Marty
>
>
> This is from memory but if you want a live result set with an order by there must be an index matching the order by clause.
>
> If you thing there is and its still not live can you post the query plan?
>
> Roy Lambert [Team Elevate]
>

Roy,

I am using a secondary index, customer_name, created in a DBISAM
Database Table named Clients. The SQL is very basic and reads as follows:

Select * From Clients Where Type = 1 Order by Name

Once again, if I remove the Order By clause, I can edit any record
produced by the Query's Live Result Set without problem. In contrast, if
I attempt to edit then post a record produced by the Query using the
Order By clause, the record fails to make the change(s. There is
definitely something wrong here. In fact, according to the DBISAM v4
manual, item no. 5 below, there shouldn't be any problem since Name is a
secondary index field in my DBISAM database table.

Single-table queries
Queries that retrieve data from a single table will generate a live
result set provided that:
1) The TDBISAMQuery RequestLive property is set to True.
2) There is no DISTINCT keyword in the SELECT SQL statement.
3) Everything in the SELECT clause is a simple column reference or a
calculated column, no aggregation is
allowed. Calculated columns remain read-only in the live result set.
4) There is no GROUP BY clause.
5) There is no ORDER BY clause, or there is an ORDER BY clause that
minimally matches an existing index in
the source table in terms of fields (from left to right) and
case-sensitivity.
6) There is no TOP N clause.I should also
add that I am working in Delphi 2007 for Win32 Apps.

Roy Lambert wrote:
> Marty
>
>
> This is from memory but if you want a live result set with an order by there must be an index matching the order by clause.
>
> If you thing there is and its still not live can you post the query plan?
>
> Roy Lambert [Team Elevate]
>

This message was cancelled from within Mozilla.

Problem resolved. Changed to using ADO components and all works as
intended using the Query with/without Order By clause.

Marty Potokar wrote:
> Roy,
>
> I am using a secondary index, customer_name, created in a DBISAM
> Database Table named Clients. The SQL is very basic and reads as follows:
>
> Select * From Clients Where Type = 1 Order by Name
>
> Once again, if I remove the Order By clause, I can edit any record
> produced by the Query's Live Result Set without problem. In contrast, if
> I attempt to edit then post a record produced by the Query using the
> Order By clause, the record fails to make the change(s. There is
> definitely something wrong here. In fact, according to the DBISAM v4
> manual, item no. 5 below, there shouldn't be any problem since Name is a
> secondary index field in my DBISAM database table.
>
> Single-table queries
> Queries that retrieve data from a single table will generate a live
> result set provided that:
> 1) The TDBISAMQuery RequestLive property is set to True.
> 2) There is no DISTINCT keyword in the SELECT SQL statement.
> 3) Everything in the SELECT clause is a simple column reference or a
> calculated column, no aggregation is
> allowed. Calculated columns remain read-only in the live result set.
> 4) There is no GROUP BY clause.
> 5) There is no ORDER BY clause, or there is an ORDER BY clause that
> minimally matches an existing index in
> the source table in terms of fields (from left to right) and
> case-sensitivity.
> 6) There is no TOP N clause.I should also
> add that I am working in Delphi 2007 for Win32 Apps.
>
> Roy Lambert wrote:
>> Marty
>>
>>
>> This is from memory but if you want a live result set with an order by
>> there must be an index matching the order by clause.
>>
>> If you thing there is and its still not live can you post the query plan?
>>
>> Roy Lambert [Team Elevate]
>>

Marty


Did you ask for a live resultset ie set RequestLive to True for teh query component?

Roy Lambert [Team Elevate]

ps I know you've solved it by switching to ADO but it would be good to solve this for DBISAM

Roy,

Yes. In fact, all of my Queries intended to produce a live result set
are set to true. That said, I would also like to solve this problem for
DBISAM. Although Delphi ADO components resolve the query 'Order By'
clause problem I was experiencing with DBISAM Query, I find that I can
no longer use EDatabaseError and EDBISAMEngineError to address
exceptions/errors in using ADO components to access data from DBISAM
database tables. What a bummer. I basically resolved one problem and
gained another.

I will gladly work with you on this in trying to resolve the former
issue with DBISAM query in using the 'Order By' clause but I first have
to change all my query components back to ADO.

Roy Lambert wrote:
> Marty
>
>
> Did you ask for a live resultset ie set RequestLive to True for teh query component?
>
> Roy Lambert [Team Elevate]
>
> ps I know you've solved it by switching to ADO but it would be good to solve this for DBISAM

Roy,

Yes. In fact, all of my Queries intended to produce a live result set
are set to true. That said, I would also like to solve this problem for
DBISAM. Although Delphi ADO components resolve the query 'Order By'
clause problem I was experiencing with DBISAM Query, I find that I can
no longer use EDatabaseError and EDBISAMEngineError to address
exceptions/errors in using ADO components to access data from DBISAM
database tables. What a bummer. I basically resolved one problem and
gained another.

I will gladly work with you on this in trying to resolve the former
issue with DBISAM query in using the 'Order By' clause but I first have
to change all my query components back to DBISAM.

Roy Lambert wrote:
> Marty
>
>
> Did you ask for a live resultset ie set RequestLive to True for teh query component?
>
> Roy Lambert [Team Elevate]
>
> ps I know you've solved it by switching to ADO but it would be good to solve this for DBISAM