Bug #21443 UDF functon with string result
Submitted: 4 Aug 2006 10:02 Modified: 5 Aug 2006 15:02
Reporter: lejvoda lejvoda Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.0.22 OS:Microsoft Windows (Windows XP)
Assigned to: CPU Architecture:Any

[4 Aug 2006 10:02] lejvoda lejvoda
Description:
I use UDF function in MySQL written in Delphi.
Function returns country name from given IP address.
Now after command "create function GetHostCountryString returns string soname 'GetHostCountryDLL.dll';" i can use this function.
Select works perfect. But! If I watch field result in HEX viewer of query browser - there is much bytes after ZERO byte indicating end of string! Why there are so many useless characters? I use this function to correct this:

create function GetHostCountry(ip text) returns char(255)
begin
  return substr(ip,1,locate(char(0),ip)-1);
end;

With this function All is OK. But now there is another problem:

select substr(GetHostCountryString(ip),1,locate(char(0),GetHostCountryString(ip))-1)
from free_clients;
returns correct values, but first row contains NULL??? Why???

Lets try another querry:

select GetHostCountryString(ip)
from free_clients;

All rows are OK!

Now query:

select GetHostCountry(ip)
from free_clients

All is OK - and without any usefull characters!

Now another problem:

select GetHostCountryString(ip),Length(GetHostCountryString(ip))
from free_clients

Retrns all ok, but first row is ['Latvia' | '0'] ! Why? There is the text!

So I have 3 questions:
1) Is it normal that MySQL returns characters after NULL symbol (maybe this problem must be for developer to extract usefull part...)
2) Why first row result is NULL?
3) Near same to 2nd question - Why Length() returns 0 when text length is same for all rows?

How to repeat:
In private comment...
[5 Aug 2006 15:02] Hartmut Holzgraefe
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

For UDF related issues the forum of choice is

  http://forums.mysql.com/list.php?118

Thank you for your interest in MySQL.