Unique keyfields required on RefreshQuick, how to solve?

[brace]

Hello,

I am trying to implement TMSQuery.RefreshQuick in one application (where the rows are quite big), so RefreshQuick seems a good alternative to refresh.

I did a dummy prototype craeting an empty DB from scratch and all worked fine.

Then I tried to apply it in my application but i keep having this error when on timer i call refresh quick:

"Unique keyfields required"

I added a timestamp field in the main table and in the query, the table already has a Primary key (that is unique) and that i also select.i also creaetd a new field and populated it = Primary key and try to make a unique index over it, but same result.
Please help me understand the error message above since i am stuck.

[azyk]

To solve the problem, try to explicitly set a primary key for the dataset. For this, set the TMSQuery.KeyFields property to the primary key name. For example:

Code: Select all

MSQuery.KeyFields := 'ID';

If it doesn't help, then please try to compose a small sample to demonstrate the problem including a script to create test tables and send it to andreyz*devart*com .

[brace]

Thanks for the reply. I managed to make it work setting the PK in using the KeyFields.

Anyway i still need help: in the TMSQuery where i want to use RefreshQuick I have a UNION ALL statement like this:

Code: Select all

SELECT
  FIELD1,
  FIELD2,
  FIELD3
FROM TABLE (PLUS JOINS)
WHERE CONDITIONS1

UNION ALL

SELECT
  FIELD1,
  FIELD2,
  FIELD3
FROM TABLE (PLUS SAME JOINS AS ABOVE)
WHERE CONDITIONS2

now this is done in this way for legacy reasons.

Probably I could refactor all and remove the union all, but this is a huge work (because all the application logic relies on this union all query.

With UNION ALL I have on RefreshQuick

(translated from italian:)
Ambiguous column name 'FIELD1'.

Could you please suggest how to get rid of this error when a union all query is used?

[paweld]

try like this:

Code: Select all

 select s.field1, s.field2, s.field3 from 
(SELECT
  FIELD1,
  FIELD2,
  FIELD3
FROM TABLE (PLUS JOINS)
WHERE CONDITIONS1

UNION ALL

SELECT
  FIELD1,
  FIELD2,
  FIELD3
FROM TABLE (PLUS SAME JOINS AS ABOVE)
WHERE CONDITIONS2) s 

[brace]

Hi, i implemented your suggestion.

Now i have a different error "TImestamp field required"

But one of my fields is of datatype Timestamp.

Why is this happening (i am still usin UNION ALL)?

Thanks.

[azyk]

To generate a SQL query for the RefreshQuick method, we use meta-information about the database object, which possesses each field. For SQL queries with UNION ALL, SQL Server doesn't return the information about tables, that own each field, and we can't find Timestamp fields. Therefore the RefreshQuick method is not suitable to be used for SQL queries with UNION ALL.