MySQL Bugs: #36241: Can't connect to hypen-table in CR2008-SP0

Bug #36241	Can't connect to hypen-table in CR2008-SP0
Submitted:	22 Apr 2008 6:25	Modified:	2 Mar 2009 23:24
Reporter:	Jared S (Silver Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	5.1.4	OS:	Windows (Vista)
Assigned to:		CPU Architecture:	Any
Tags:	character, flag, grave, hyphen, ODBC

Description:
Hi,

I am running Crystal Reports 2008-SP0 and trying to connect to DB with hyphen table in, but it wont work since CR thinks that the table name is "-rooms" when in fact the fully qualified table name is "`flat-rooms`".  I have tried tweaking a flag called SQLDescrbeCols but this has no impact on the table name.  I am looking into solutions via Business Objects.

How to repeat:
1.  Create DB with tablle called "my-table"
2.  Create new report on CR2008+SP0

SP0 is downloadable from here...
http://support.businessobjects.com/downloads/service_packs/crystal_reports_en.asp

Suggested fix:
Would you speak to Jim or Jess and see if it would be possible to implement something like SQLDescrbeTables which basically returns fully qualified table names.  So long as this new *ODBC flag setting* was not enabled by default then I could set it so on my dev. machine and this would not interfere if at all possible with any other reports/connections.

Con wizard error

Attachment: grave-err.jpg (image/pjpeg, text), 21.53 KiB.

Hi Jared and thanks for your report. In my opinion, this has nothing to do with "describe tables" (there is no such function in ODBC API to my knowledge). In any case, I made small test using native MS ODBC client and found no problems:

mysql> create table `bug36241-my` (ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMA
RY KEY, CharVal VARCHAR(50));
Query OK, 0 rows affected (0.09 sec)

SQLTables:
	In:	StatementHandle = 0x00851FC0, CatalogName = "test", NameLength1 = 4, SchemaName = "test", NameLength2 = 4, TableName = SQL_NULL_HANDLE, 	NameLength3 = 0, TableType = SQL_NULL_HANDLE, NameLength4 = 0
	Return:	SQL_SUCCESS=0

Get Data All:
"TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "TABLE_TYPE", "REMARKS"
"test", "", "a", "TABLE", ""
"test", "", "ado_test", "TABLE", ""
"test", "", "atablewithveryverylongnametotestcrystalreportstrunc", "TABLE", ""
"test", "", "blackhole", "TABLE", ""
"test", "", "bug29877", "TABLE", "InnoDB free: 10240 kB"
"test", "", "bug36241-my", "TABLE", ""  <<<<
"test", "", "bug6157", "TABLE", ""

This could be related to SQL_MODE you're using on your server (more closer, ANSI_QUOTES). Please provide more info on server config and attach ODBC trace (image is of no use, I believe you get the error).

ANSI_QUOTES replace the grave symbol '`' with a quote symbol '"'.  Has no bearing on the bug since crystal is trying to use naked tabled names.

   - please analyze this bug as I am slightly out of my depth here.
   - trace log from yesterday attached.

Trace log from windows ODBC

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

MySQL ODBC\connector SQL trace

Attachment: myodbc.sql (application/octet-stream, text), 792 bytes.

Hey Tonci, have found the cause the problem exists when CR2008SP0 executes a SQLFetch when adding a table to the new report using the wizard, so this rules you guys out as the cause, but given the severity are you able to tell me how CR is building its SQLFetch statement and see if it is possible have the ODBC driver *pre-qualify* table names based upon ODBC settings..

crw32           f04-fa8	EXIT  SQLFetch  with return code 100 (SQL_NO_DATA_FOUND)
		HSTMT               031FE1E0

I think it is trying to do something like "SELECT * from flat-rooms" when it should read "SELECT * from `flat-rooms`" or "SELECT * from "flat-rooms"".

Thanks mate.

Jared, I will pass your request to others. Maybe I can install CR2008 finally so we can check it better.

Leaving the report in "Open" status on purpose.

Jared, I have reconsidered with my colleagues your feature request:
"how CR is building its SQLFetch statement and see if it is possible have the ODBC driver *pre-qualify* table names based upon ODBC settings" and prevailing opinion is not to "spoil" driver for one client when easy and convenient workaround exists, remove hyphens from table names. In any case, I expect CR to do something about this if you inform them.

Would you be able to communicate with SAP regarding this bug.  Maybe create account if necessary and register a valid product key and deep link this bug report.  Have had excellent results when dealiong with Microsoft regarding crystal issues.

Let them know MySQL compatability has been *broken*.

CR2008-ServicePack0 \ MySQL General Query Log is like so..

1 Query	SELECT `id`,`idtype`,`linen`,`location`,`text`,`clean`,`keys`,`isactive`,`people` FROM `palms`.`flat-rooms` LIMIT 0

1 Query	select * from palms.flat-rooms where 0=1

Appears as CR is doing full table hit while trying to return 0 rows, twice. 1st time correctly second time fails.  I don't even see the point in the query since they do full column show before.

SAP procedure code typo.

Informing SAP people now.

SAP have released a hotfix (Fix Pack 1.1) that resolves this bug..

▪ Crystal Reports 2008 - SP0 - CD Install
▪ Crystal Reports 2008 - SP1 - Incremental
▪ Crystal Reports 2008 - SP1 - Fix Pack 1.1*
▪ Crystal Reports 2008 - SP1 - Fix Pack 1.2

http://service.sap.com/sap/bc/bsp/spn/bobj_download/main.htm