Bug #32989 MyODBC Crystal Reports can't connect to a tbl if a field name has a single quote
Submitted: 5 Dec 2007 4:27 Modified: 14 Mar 2008 18:43
Reporter: Erica Moss Email Updates:
Status: Closed Impact on me:
Category:Connector / ODBC Severity:S2 (Serious)
Version:3.51.22 OS:Microsoft Windows (xp2 sp2)
Assigned to: Jess Balint CPU Architecture:Any
Tags: crystal reports developer XI revision 1

[5 Dec 2007 4:27] Erica Moss
When trying to use a MySQL table that has a field with a single quote in the name in Crystal Reports an error is received, and the table fails to open.

This is a snip from the attached ODBC trace of the steps below:

crw32           1658-1548	EXIT  SQLColumnsW  with return code -1 (SQL_ERROR)
		HSTMT               07E52A80
		WCHAR *             0x7FE55564 [      -3] "xtreme\ 0"
		SWORD                       -3 
		WCHAR *             0x00000000 [      -3] <empty string>
		SWORD                       -3 
		WCHAR *             0x7FE554FC [      -3] "foo\ 0"
		SWORD                       -3 
		WCHAR *             0x00000000 [      -3] <empty string>
		SWORD                       -3 

		DIAG [HY000] [MySQL][ODBC 3.51 Driver][mysqld-5.0.45-community-nt]Unknown column 'foo\'s field' in 'field list' (1054) 

How to repeat:
use test;
create table foo (c1 int primary key, `foo's field` char(20));
insert into foo values (1, "test");

0. Create a DSN using MyODBC 3.51 and database "test"
1. Open Crystal Reports and click the New Report button
2. When the wizard opens select New Connection / ODBC
3. Select the DSN name created above and click Finish
4. Select table "foo" from the new connection node, and click the right arrow to add it to the selected column.

Error is received
[5 Dec 2007 4:28] Erica Moss
ODBC trace

Attachment: SQL.LOG (application/octet-stream, text), 87.97 KiB.

[6 Dec 2007 21:40] Elena Koyfman
crystal report sql statement truncates column name that is more then 20 characters. Due to this truncation, report shows an error message "...Unknown column". Is any fix for it in MySQL odbc connector?
[1 Feb 2008 19:22] Tonci Grgin
Works in odbcte32 with 5.0.54BK and 3.51.23:
Get Data All:
"foo.c1", "foo.foo's field"
1, "test"
1 row fetched from 2 columns.
[8 Feb 2008 23:52] Jess Balint

Attachment: bug32989.diff (application/octet-stream, text), 7.38 KiB.

[8 Feb 2008 23:55] Jess Balint
Based on Jim's guidance, created a new method for escaping identifiers. When we send identifiers, they're always enclosed in backticks and thats the only character escaped. Fixed all occurrences where this was happening.
[29 Feb 2008 17:14] Lawrenty Novitsky
patch approved
[11 Mar 2008 14:00] Jess Balint
Committed as rev1057. Will be released in 3.51.24.
[14 Mar 2008 18:40] MC Brown
A note has been added to the 3.51.24 changelog: 

Using tables with a single quote or other non-standard characters in the table or column names through ODBC would fail.