SmartFetch giving "Key Fields not Found" error ?

Discussion of open issues, suggestions and bugs regarding IBDAC (InterBase Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
luapfr
Posts: 37
Joined: Thu 11 Apr 2013 13:11

SmartFetch giving "Key Fields not Found" error ?

Post by luapfr » Sun 18 Mar 2018 11:19

Hello, I am trying to use SmartFetch as following SQL command

Code: Select all

SELECT Distinct
CM.Mercadoria, 
Cast(CM.Codigo as VarChar(40)) As Codigo_Original
FROM CADASTRO_MERCADORIAS CM 
ORDER BY 1
But I always get the error "Key Fields not Found" how I solve this
already tried to put the fields "Mercadoria;Codigo_Original" i n "PrefetchedFields"
but it does not solve

how should I proceed ?

ViktorV
Devart Team
Posts: 3168
Joined: Wed 30 Jul 2014 07:16

Re: SmartFetch giving "Key Fields not Found" error ?

Post by ViktorV » Mon 19 Mar 2018 10:52

This error is caused by the presence of the DISTINCT keyword in the SQL query.
When using SmartFetch, if the keyword DISTINCT is used in the SQL query, you should write the queries by yourself to get the key fields values ​​and set them to the value of the property https://devart.com/ibdac/docs/?devart.d ... values.htm. For example,

Code: Select all

IBCQuery.SmartFetch.SQLGetKeyValues.Text: = 'select Mercadoria, Codigo_Original from CADASTRO_MERCADORIAS';
If this does not help you in solving the issue, please compose and send us a small sample demonstrating the issue using: https://devart.com/company/contactform.html, including the scripts for creating and filling database objects.

luapfr
Posts: 37
Joined: Thu 11 Apr 2013 13:11

Re: SmartFetch giving "Key Fields not Found" error ?

Post by luapfr » Mon 19 Mar 2018 19:46

I currently use FireDac in Delphi.

I am thinking of buying a license for IBDac so I am doing some testing and asking some questions.

That way I do not have a valid login to send this question to you

so I put together a test project and you can download it from the link below.

https://mega.nz/#!sn4FQAJS!wP1xbsXH94Pv ... LowG9rZgaQ

ViktorV
Devart Team
Posts: 3168
Joined: Wed 30 Jul 2014 07:16

Re: SmartFetch giving "Key Fields not Found" error ?

Post by ViktorV » Tue 20 Mar 2018 13:52

The query for retrieving fields that will uniquely identify a record is specified in the TIBCQuery.SmartFetch.SQLGetKeyValues.Text property. You should compose such a query yourself when the SQL query is complex, and the query to read the key and pre-selected fields cannot be generated automatically.
To solve the "Key fields not found" error in SmartFetch mode in your sample, you can use the following query for both TIBCQuery:

Code: Select all

IBCQuery1.SmartFetch.SQLGetKeyValues.Text: = 'select Mercadoria from CADASTRO_MERCADORIAS';
maybe this query will be not full, you should compose it yourself based on the architecture and logic of your application
and

Code: Select all

IBCQuery2.SmartFetch.SQLGetKeyValues.Text: = 'select Mercadoria from CADASTRO_MERCADORIAS';
Also, the "Key fields not found" error in SmartFetch mode when opening a dataset occurs when you cannot get key fields in your table. To solve the issue, you can set the TIBCQuery.KeyFields property (TIBCTable.KeyFields) to a valid value other than empty.

luapfr
Posts: 37
Joined: Thu 11 Apr 2013 13:11

Re: SmartFetch giving "Key Fields not Found" error ?

Post by luapfr » Wed 21 Mar 2018 13:50

Now it worked out and I was able to understand
I am very grateful to you for your attention.

ViktorV
Devart Team
Posts: 3168
Joined: Wed 30 Jul 2014 07:16

Re: SmartFetch giving "Key Fields not Found" error ?

Post by ViktorV » Wed 21 Mar 2018 15:26

Glad to see that the issue was resolved.
Feel free to contact us if you have any further questions about our products.

Post Reply