Problem with TUniQuery/SmartFetch

[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.

[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.

[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;

Update: It looks in fact like it's a matter of capitalization.

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.

[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?

[ViktorV]

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.

[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.

[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';

[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.

[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.

[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'.

[ViktorV]

If complex queries are generated dynamically in your application, then using SmartFetch mode might not be an optimal solution.