Unique keyfields required on RefreshQuick, how to solve?

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
brace
Posts: 213
Joined: Wed 14 Feb 2007 08:26

Unique keyfields required on RefreshQuick, how to solve?

Post by brace » Thu 29 Jan 2015 15:57

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
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Unique keyfields required on RefreshQuick, how to solve?

Post by azyk » Fri 30 Jan 2015 09:34

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
Posts: 213
Joined: Wed 14 Feb 2007 08:26

Re: Unique keyfields required on RefreshQuick, how to solve?

Post by brace » Fri 30 Jan 2015 10:22

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
Posts: 11
Joined: Mon 29 Sep 2014 08:56

Re: Unique keyfields required on RefreshQuick, how to solve?

Post by paweld » Sun 01 Feb 2015 21:15

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
Posts: 213
Joined: Wed 14 Feb 2007 08:26

Re: Unique keyfields required on RefreshQuick, how to solve?

Post by brace » Mon 02 Feb 2015 09:30

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
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Unique keyfields required on RefreshQuick, how to solve?

Post by azyk » Thu 05 Feb 2015 09:55

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.

Post Reply