Page 1 of 1
Problem with TUniQuery/SmartFetch
Posted: Mon 25 May 2015 19:09
by bogdan
Hello,
I have a bit of a strange situation concerning a TUniQuery and SmartFetch. More exactly:
Code: Select all
...
UniQuery1.SQL:='select ss.id, types.name from ss inner join types on ss.idType=types .id';
uniquery1.SmartFetch.Enabled:=false; //works OK
uniquery1.SmartFetch.Enabled:=true; //-->ESmartFetchError: 'Fields of received result set do not correspond to fields of source dataset'
UniQuery1.Active:=true;
...
Is there anything I do wrong?
Thank you.
Re: Problem with TUniQuery/SmartFetch
Posted: Tue 26 May 2015 07:33
by AlexP
Hello,
Please specify the name of the DB you are using, the script for creating the tables, also specify the exact IDE and UniDAC versions.
Re: Problem with TUniQuery/SmartFetch
Posted: Tue 26 May 2015 13:03
by bogdan
Hello,
Sorry, my bad.
My dev env is: XE7, UniDAC 6.02, MySQL 5.5.17
Also please check the update below
The tables are created like this:
Code: Select all
CREATE TABLE `ss` (
`id` INT(10) NOT NULL DEFAULT '0',
`idType` INT(10) NOT NULL,
`idCustomer` INT(10) NOT NULL,
PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
Code: Select all
CREATE TABLE `types` (
`id` INT(10) NOT NULL DEFAULT '0',
`name` VARCHAR(50) NOT NULL,
`price` FLOAT NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
Code: Select all
select ss.id, types.name from ss inner join types on ss.idType=types.id; #no error
select ss.Id, types.Name from ss inner join types on ss.idType=types.id; #ESmartFetchError
Please do not take in consideration the phrase in my anterior post since the capitalization is not the same I used in production code.[/i]
Thank you very much.
Re: Problem with TUniQuery/SmartFetch
Posted: Wed 27 May 2015 03:13
by bogdan
Hello again,
In the same setup as above I encounter another situation that seems unrelated to the capitalization. Yet, it somehow looks related.
Code: Select all
select types.id, types.name as n from types order by n desc;
If I execute the phrase with SmartFetch:=
false it works.
If I set SmartFetch:=
true it raises EMySQLException '#42S22Unknown column 'n' in order clause'
Any ideas?
Re: Problem with TUniQuery/SmartFetch
Posted: Wed 27 May 2015 09:41
by ViktorV
bogdan wrote:Hello again,
In the same setup as above I encounter another situation that seems unrelated to the capitalization. Yet, it somehow looks related.
Code: Select all
select types.id, types.name as n from types order by n desc;
If I execute the phrase with SmartFetch:=
false it works.
If I set SmartFetch:=
true it raises EMySQLException '#42S22Unknown column 'n' in order clause'
Any ideas?
The error occurs due to the ORDER BY keyword in the SQL query.
When using SmartFetch, if an ORDER BY keyword is used in the SQL query, you should manually write a query for obtaining key field values and set it to the TUniQuery.SmartFetch.SQLGetKeyValues property.
Re: Problem with TUniQuery/SmartFetch
Posted: Wed 27 May 2015 13:37
by bogdan
Thank you very much.
Yet, it looks like it doesn't respect my ORDER BY clause in this case. More exactly:
Code: Select all
select ss.id, types.name as tn from ss inner join types on ss.idtype=types.id order by tn
I have tried:
Code: Select all
uniquery1.SmartFetch.SQLGetKeyValues.text:='select id from ss'; // --> the result set gets ordered by ss.id and not by tn
Code: Select all
uniquery1.SmartFetch.SQLGetKeyValues.text:='select types.name from types'; // -->Stack overflow
Code: Select all
uniquery1.SmartFetch.SQLGetKeyValues.text:='select types.id from types'; // -->Error: 'Record is not found'
How should my SQLGetKeyValues look like if I want the dataset sorted by `tn`?
Thank you.
Re: Problem with TUniQuery/SmartFetch
Posted: Wed 27 May 2015 15:40
by ViktorV
To sort your dataset by the name field, set SQLGetKeyValues to the following value:
Code: Select all
UniQuery.SmartFetch.SQLGetKeyValues.Text := 'select ss.id from ss inner join types on ss.idtype=types.id ORDER BY name';
Re: Problem with TUniQuery/SmartFetch
Posted: Wed 27 May 2015 16:55
by bogdan
Ok, thank you, it works now.
Yet, is there any way to sort by an alias and not by a column name while in SmartFetch mode please?
Thank you.
Re: Problem with TUniQuery/SmartFetch
Posted: Thu 28 May 2015 09:47
by ViktorV
In the TUniQuery.SQL property you can specify the original query with ordering by alias. But in SQLGetKeyValues you should specify the query with ordering by field name, since this field doesn't take part in selection. Please clarify, what is the fundamental difference between ordering by alias and field name.
Re: Problem with TUniQuery/SmartFetch
Posted: Thu 28 May 2015 12:46
by bogdan
Hello,
I want to allow the user to title-click a column in order to rerun the query with an ORDER BY clause. If the column is aliased and SmartFetch is enabled then I encounter '#42S22Unknown column '...' in order clause'.
Re: Problem with TUniQuery/SmartFetch
Posted: Thu 28 May 2015 14:20
by ViktorV
If complex queries are generated dynamically in your application, then using SmartFetch mode might not be an optimal solution.