Exception when calling the refreshrecord method !

swierzbicki · Post by **swierzbicki** » Sat 04 Nov 2006 20:02

Hi,

I have an situation when using the refreshrecord method on a TMyQuery having the FilterSQL filled, the UpdatingTable filled and the FullRefresh option set to true !

I'm getting this error :
"Refresh Failed. Found 620 records"

When setting the FullRefresh option to false, everything works.

The SQL Query is looking like this :

Code: Select all

SELECT
  table1.*,
  table2.*
FROM
  table1,
  table2,
  table3
WHERE
  (table1.Id = table2.id)

the FilterSQL is filled with that :

Code: Select all

(table2.ID2 like table3.ID3) AND ( table3.User = @User) AND (table1.Name like ' %name%')

The Updatingtable is filled with :

Code: Select all

table1

I'm using the latest mydac build and the lastest mysql 4.1 version

Antaeus · Post by **Antaeus** » Mon 06 Nov 2006 12:21

Unfortunately the information you have provided is not enough. Please supply us with script to create tables and fill them with some records so we can reproduce this problem on our side.

swierzbicki · Post by **swierzbicki** » Mon 06 Nov 2006 21:37

What is funny is that I'm always getting the "Refresh Failed. Found 620 records" ! (even when changing one criteria in the filstersql property)

I have also found that removing the Lookup persistent fields solves the issue.
I will try to make a project reproducing this bug.

swierzbicki · Post by **swierzbicki** » Tue 07 Nov 2006 10:12

I have sended you a project + script reproducing this issue.

Antaeus · Post by **Antaeus** » Wed 08 Nov 2006 14:04

We have analyzed the sample you have sent. The reason of the problem is that the information about tables keys on the client is not complete. MyDAC tries to use all fields from the query marked as the key. Only information about keys from the vrp table is found. So MyDAC adds following conditions to WHERE clause:

Code: Select all

  vrp.IdAgence = 'A01' AND vrp.IdDepot IS NULL AND vrp.IdRegion IS NULL AND vrp.IdSecteur IS NULL

So, this SELECT statement will return number of records that can be calculated in this way:

Code: Select all

  SELECT count(*)
  FROM
    clientssia,
    perimetressia 
  WHERE
    (clientssia.IdVRP = VRP.IdVRP)

You have two ways to solve this problem. The first way is to request all fields marked as primary keys from all tables in the query to allow MyDAC to generate correct refresh statement. If there is no key for some tables you should create them. The second way is to assign correct SELECT statement to the RefreshSQL property manually.

swierzbicki · Post by **swierzbicki** » Wed 08 Nov 2006 15:32

Thank you for the answer but all tables used in the SQL statement have more then one index !
I have added a primary on each table now. I've still the same issue.

More, the refreshrecord is working fine when removing all the DBLookUp fields from the Query object.

Do you have any other clue

(I don't want to 'hardcode' the RefreshSQL)

Antaeus · Post by **Antaeus** » Fri 10 Nov 2006 17:10

It is not enough to create primary keys for all tables. You should retrieve all of them to the client. If this does not help then send us (evgeniyD*crlab*com) changed application and changed scripts to create tables.