Bug #40354 MyODBC Version 5.1.5 Returns AsianCharacters on date_format() function
Submitted: 27 Oct 2008 18:39 Modified: 28 Oct 2008 14:14
Reporter: Rich Lobdell Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / ODBC Severity:S3 (Non-critical)
Version:5.1.5 OS:Windows (Vista)
Assigned to: CPU Architecture:Any
Tags: Asian Characters, date_format, ODBC, Unicode

[27 Oct 2008 18:39] Rich Lobdell
Description:
I have a MS Access application where I use an ODBC passthrough query to retrieve and sort Data.  I have been using database for years and have used ODBC 3.5.1 with no problems.  When I upgraded the ODBC client to 5.1.5 my grouping and sorting show asian characters even though I have English as my default language.

date_format(field.Date,"%Y%m") generates Asian characters inside MS Access 2007
during a SQLPass through query.

expected Results:
Date Formats show dates in format yyyymm i.e. 200810.  

Actual Results:
Date Formats show string of Asian Characters.

How to repeat:
Actual SQL statement as follows all date fields are Date
SELECT 
date_format(scrap.Date,"%M %Y") as MONTH,
date_format(scrap.Date,"%Y%m") as GroupBy,
SUM(IF (defects.ScrapItem <> "Total Inspected",defects.Amount,0)) AS SCRAP,
SUM(IF (defects.ScrapItem =  "Total Inspected",defects.Amount,0)) AS TOTAL,
CONCAT(scrap.Area) as LINK
FROM defects
LEFT OUTER JOIN scrap ON (defects.MainID = scrap.MainID)
RIGHT OUTER JOIN manufacturedproducts ON (scrap.PartNumber = manufacturedproducts.PartNumber)
WHERE (
(scrap.Date >= 081020) AND (scrap.Date <= 081027)
)
GROUP BY date_format(scrap.Date,"%Y%m"),LINK

Suggested fix:
Have it display format as per expected results
[28 Oct 2008 6:54] Tonci Grgin
Hi Rich and thanks for your report.

I do not think this is a bug. What I think is that:
  o You're using MySQL server prior to 5.0.36. Before 5.0.36, the return value of DATE_FORMAT was a binary string.
  o You do not have "Always handle binary function results as character data" option checked in DSN used (which was designed to bypass this server metadata problem). Please check it!

If I'm right you'll be able to retest quickly and confirm (or not ;)) my ruling.
[28 Oct 2008 14:14] Rich Lobdell
Screenshot of Asian Characters in SQLpassthrough Query in MS Access

Attachment: 2008-10-28 mySQL Asian Characters in query.pdf (application/pdf, text), 22.66 KiB.

[28 Oct 2008 14:14] Rich Lobdell
I have checked the option for 
"Always handle binary function results as character data" located under [Flags 1].
I am indeed running 5.0.22 on that particular server.
I still get the same results as before with that option checked.  Are there anymore flags I should check?  I have no others selected at this time.
RichL