TMyQuery truncates string fields

[docH]

I'm using TMyQuery to extract data from a MySQL database using Delphi. (using MyDac ver 8.5.14)

I have a field called member_number in my table defined as member_number varchar(15);
If this field is null my sql return a small message instead. The message is longer than the 15 characters defined for the field.

If I simply say...

Code: Select all

SELECT
IFNULL(member_number,
      'membership number missing please contact secretary' ) AS MemberNumber
FROM
mytable;

... then the query results show the message getting truncated at 15 characters, which is the same as the defined field size, and it shows just 'membership numb' .

I understand this is due to a bug/feature in MySQL that causes it to always return the defined field size along with the field data, which TMyQuery seems to be using instead of the actual data size.

Looking at this posting http://forums.devart.com/viewtopic.php? ... ted#p39701
It is suggested to cast the result to a larger field if necessary. They used an earlier version of MyDAC but the sql suggested is below (in their code the REPLACE replaces a short string with a bigger one which resulted in truncation)

Code: Select all

SELECT
   CAST(REPLACE(r.proc_path,"Reports.exe" # 11 chars
                           ,"Reports2.exe" # 12 chars
                       ) 
	  AS CHAR(100)
	 ) proc_path2
   ,r.proc_path
FROM 
   proc_run r 

However, when I tried doing the same thing...

Code: Select all

SELECT
IFNULL(member_number, 
      CAST('membership number missing please contact secretary'  AS CHAR(100)  ) 
      )  AS MemberNumber
FROM
mytable;

... the message was still truncated at 15 characters

How can I get the message correctly returned from TMyQuery withought having to change the defined field size in the table?

BTW No truncation happens using the database manager SQLYog to run the query - even when not using CAST, so its possible it is to do with the way TMyQuery deals with it.

[ViktorV]

To solve the issue, please change your SQL query with the following one and check whether the issue is reproduced:

Code: Select all

SELECT CAST(IFNULL(member_number, 'membership number missing please contact secretary') AS CHAR(100)) AS MemberNumber FROM mytable;

[docH]

Thank you, that worked.

But is there some way to tell TMyQuery not to use the underlying field length descriptor but to use the actual length of the data returned by the query and return all of the text?

I realise that using the underlying length might increase the performance of TMyQuery but I can see it producing lots of annoying errors if I have to do queries of this type again when I don't necessarily know the declared size or forget that I need to take it into account. - Especially when I test queries of this type first using SQLYog (and other database managers) which do not need the CAST, so I assume if it works there then it will work TMyQuery.

[ViktorV]

Please specify the MySQL server version you are connecting to, so we will be able to investigate the issue.

[docH]

I used the sql command SELECT VERSION() to get this information for you.

The main, live, version of the remote database I connect to reports version 4.1.22-standard.
But I also have two backups.
One on another remote server hosted at a different ISP with version 5.5.34-32.0-log
and a local copy on my desktop using WAMP server that reports version 5.6.12-log.

Regards

[ViktorV]

Unfortunately, we couldn't reproduce the issue: when executing the specified queries, the message wasn't cut. To investigate the problem, please give us test access to your server.