Bug #48179 Getting cryptical results when using driver 3.51.27 and select right(id, 5) ...
Submitted: 20 Oct 2009 10:02 Modified: 21 Oct 2009 8:11
Reporter: Andre Iselin Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / ODBC Severity:S1 (Critical)
Version:3.51.27 OS:Windows
Assigned to: CPU Architecture:Any

[20 Oct 2009 10:02] Andre Iselin
Description:
Hello,

after I'm used the statement 'SELECT right(id,5) as Typ_ID FROM ...' I get a cryptical results of the selected id like this '㈴㘲' and not the result '42265' that I'm expected!

I use the statement with Access and the MySQL Connector 3.51.27 to get the data from a MySQL-Server-Version 5.0.77 on a Windows-Server-System. Please, can you give me a feedback about this problem?

How to repeat:
Use the statement with Access and the MySQL Connector 3.51.27 to get the data from a MySQL-Server-Version 5.0.77 on a Windows-Server-System.
[20 Oct 2009 10:26] Tonci Grgin
Hi André and thanks for your report.

Please do check MySQL manual regarding string functions and notice they return the result with binary flag set. So you should either CAST your result as CHAR or check "Always handle binary function results as character data".
Actually, your result is returned in form of 0xyour_proper_string so might even want to parse it on client...

This issue has been discussed long and wide in BugsDB and it is not a bug.
[21 Oct 2009 8:11] Andre Iselin
Thanks for the fast responds, but if I try to get the data with the ODBC driver 3.51.21, I get no cryptical results!!! To parse the data at the client should be the LAST option that I will use to!!! I also take the drive ...21 as an easier way to get the correct data via ODBC ;-).
[21 Oct 2009 8:56] Tonci Grgin
Quoting my response again:
> Please do check MySQL manual regarding string functions and notice they
return the result with binary flag set. So you should either CAST your
result as CHAR or check "Always handle binary function results as
character data".

So, c/ODBC 5 actually behaves correctly while you're just exploiting a bug in 3.51. It's your choice which one you'll use.
[21 Oct 2009 8:57] Tonci Grgin
I should say "old 3.51".