Bug #32989 MyODBC Crystal Reports can't connect to a tbl if a field name has a single quote

• Submitted: 5 Dec 2007 5:27
• Modified: 14 Mar 19:43
• Reporter: Eric MaLossi
• Status: Closed
• Category: Connector/ODBC
• Severity: S2 (Serious)
• Version: 3.51.22
• OS: Microsoft Windows (xp2 sp2)
• Assigned to: Jess Balint
• Target Version: 3.51
• Tags: crystal reports developer XI revision 1
• Triage: D3 (Medium)

[5 Dec 2007 5:27] Eric MaLossi

Description:
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:
mysql>
use test;
create table foo (c1 int primary key, `foo's field` char(20));
insert into foo values (1, "test");

Steps:
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 5:28] Eric MaLossi

ODBC trace

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

[6 Dec 2007 22: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 20: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.

[9 Feb 0:52] Jess Balint

fix+test

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

[9 Feb 0: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 18:14] Lawrin Novitsky

patch approved

[11 Mar 15:00] Jess Balint

Committed as rev1057. Will be released in 3.51.24.

[14 Mar 19: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.