Devart Forums

Posted: **Fri 15 Feb 2008 16:01**

Hello,

I've encourted a problem with using group_concat() and adding fields to Query. In some situations the field is detected as TMemoField and in others as TStringField. The former works OK everytime but the latter doesn't work if 'SET group_concat_max_len...' was sent -> Error: Type mismatch for field 'sth', expecting: String actual: Memo.

Here are the steps to reproduce, create the following database:

Code: Select all

CREATE DATABASE `tests` /*!40100 DEFAULT CHARACTER SET cp1250 */;
USE `tests`;

CREATE TABLE `newtb_r` (
  `id` int(10) NOT NULL auto_increment,
  `somestr` varchar(255) character set cp1250 default NULL,
  PRIMARY KEY  (`id`)
) DEFAULT CHARSET=utf8;

CREATE TABLE `newtb_b` (
  `id` int(10) NOT NULL auto_increment,
  `somestr` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
) DEFAULT CHARSET=utf8;

Create a MyConnection and 2 Queries:
MyQuery1:

Code: Select all

select group_concat(S.somestr) as sth from
newtb_r S

MyQuery2:

Code: Select all

select group_concat(S.somestr) as sth from
newtb_b S

Use Fields Editor and Add Fields to both queries.
The sth in MyQuery1 is TMemoField.
The sth in MyQuery2 is TStringField.

Run the Application.

Issue:

Code: Select all

MyConnection1.Open;
MyConnection1.ExecSQL('SET group_concat_max_len = 100000;', []);
MyQuery1.Open;
MyQuery2.Open; -> exception

If you don't issue ExecSQL() then both queries open correctly, but if you do then the second one raises the Exception.

I use MyDAC 5.20.0.13 and MySQL 5.0.

Regards,
Crafty

Posted: **Tue 19 Feb 2008 13:12**

In the following query

Code: Select all

select group_concat(S.somestr) as sth from newtb_b S

for the 'sth' field MySQL returns field type equal to TStringField.
But when you set group_concat_max_len = 100000, MySQL server returns field type equal to TMemoField.

In order to solve this problem you should create the field of corresponding type manually.
Also you can set the group_concat_max_len value before starting MySQL server. For this set its value in the my.ini file.

Posted: **Wed 27 Feb 2008 11:42**

Dimon wrote:for the 'sth' field MySQL returns field type equal to TStringField. But when you set group_concat_max_len = 100000, MySQL server returns field type equal to TMemoField.

Hmm... in that case why do I get always get TMemoField for for sth in MyQuery1 (even without group_concat_max_len)?

Dimon wrote:In order to solve this problem you should create the field of corresponding type manually.
Also you can set the group_concat_max_len value before starting MySQL server. For this set its value in the my.ini file.

Unfortunately it's not my server so I can't edit my.ini.

Maybe in the future you could add an option to MyConnection where user could input commands run after each connect (even in the design mode)? This would be a solution to the problem.

Regards,
Crafty

Posted: **Thu 28 Feb 2008 10:01**

crafty wrote:
Dimon wrote:for the 'sth' field MySQL returns field type equal to TStringField. But when you set group_concat_max_len = 100000, MySQL server returns field type equal to TMemoField.
Hmm... in that case why do I get always get TMemoField for for sth in MyQuery1 (even without group_concat_max_len)?

It is a peculiarity of the way MySQL works with string fields. By default, for the first table MySQL returns a field type, equal to TMemoField with length of chars =1024.

Posted: **Thu 28 Feb 2008 10:02**

crafty wrote: Maybe in the future you could add an option to MyConnection where user could input commands run after each connect (even in the design mode)? This would be a solution to the problem.

In order to solve this problem you can add the code

Code: Select all

SET group_concat_max_len = 100000;

before SELECT statement in the TMyQuery.SQL property in the design-time mode. Then this command will be executed each time before SELECT query.

Devart Forums

group_concat_max_len -> Expecting: String actual: Memo

group_concat_max_len -> Expecting: String actual: Memo