MySQL Bugs: #21802: SQLForeignKeys() function return column names with quotes

Bug #21802	SQLForeignKeys() function return column names with quotes
Submitted:	24 Aug 2006 1:33	Modified:	21 Mar 2007 17:14
Reporter:	Derek Lai	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	3.51.12	OS:	Windows (Windows)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
There is basically a problem with the information returned by SQLForeignKeys() function.  The column names returned have quotes around them.  

I notice that there was a similar issue with bug number #8100 which claims to be resolved, but I am still seeing the problem here with version MyODBC 3.51.12 on Windows.   See the reproducing steps below.

How to repeat:

In MySQL server, create a database with name 'acTestDB'.  Notice the
use of mixed-case within the database name.

Then run the following 2 create statements:

CREATE TABLE `tab1` (
  `supp_no` int(11) NOT NULL default '0',
  `item_no` int(11) NOT NULL default '0',
  `qty` int(11) NOT NULL default '0',
  PRIMARY KEY  (`supp_no`,`item_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 

CREATE TABLE `tab2` (
  `ord_no` int(11) NOT NULL default '0',
  `supp_no` int(11) NOT NULL default '0',
  `item_no` int(11) NOT NULL default '0',
  KEY `supp_no` (`supp_no`,`item_no`),
  CONSTRAINT `tab1_ibfk_1` FOREIGN KEY (`supp_no`, `item_no`) REFERENCES `tab1`
(`supp_no`, `item_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Then, issue the following command in ODBCTE32W.exe (Microsoft's ODBC Test
tool):
SQLForeignKeys:
	In:	
	StatementHandle = 0x008418C0, 
	PkCatalogName = SQL_NULL_HANDLE, 
	NameLength1 = 0, 
	PkSchemaName = SQL_NULL_HANDLE, 
	NameLength2 = 0, 
	PkTableName = SQL_NULL_HANDLE, 
	NameLength3 = 0, 
	FkCatalogName = SQL_NULL_HANDLE, 
	NameLength4 = 0, 
	FkSchemaName = "acTestDB", 
	NameLength5 = 8, 
	FkTableName = "tab2", 
	NameLength6 = 4
				
Return:	SQL_SUCCESS=0

Get Data All:
"PKTABLE_CAT", "PKTABLE_SCHEM", "PKTABLE_NAME", "PKCOLUMN_NAME", "FKTABLE_CAT",
"FKTABLE_SCHEM", "FKTABLE_NAME", "FKCOLUMN_NAME", "KEY_SEQ", "UPDATE_RULE",
"DELETE_RULE", "FK_NAME", "PK_NAME", "DEFERRABILITY"
"actestdb", "", "tab1", ""supp_no"", "acTestDB", "", "tab2",
""supp_no"", 1, 1, 1, "NULL", "NULL", 7

Notice that there are double quotes around the column name "supp_no".

I observed that the quote character is the backquote (`) when I am issuing the SQLForeignKeys() call within a C++ program using the MyODBC driver.

Derek, thanks for your report. It seems to be extension of bug report #8100. Will turn Bogdan's attention to this promptly since he asked for table structure and never got it.

Derek, I'm unable to locate ODBCTE32W.exe in VS 2005 and on MS pages. For start, can you please try the same test with ODBCTE32.exe (non-unicode version) since MyODBC 3.51.12 does not support W functions (map's them to non unicode ones) and inform me of result?

Thanks for the quick reply.

I tried with odbcte32.exe but the SQLForeignKeys() function seems to have some problem.  The arguments ordering seems to be messed up.  For example, if I pass "acTestDB" as the fkSchemaName, it ends up going to pkTableName.  (The logging showed up that way.)  It does not retrieve the right results.

Ok, I was able to generate results with odbcte32.exe.  Notice the back quotes around the column names.

SQLForeignKeys:
				In:				StatementHandle = 0x008419B0, 
										PkCatalogName = SQL_NULL_HANDLE, NameLength1 = 0, PkSchemaName = "acTestDB", NameLength2 = 8, 
										PkTableName = SQL_NULL_HANDLE, NameLength3 = 0, FkCatalogName = SQL_NULL_HANDLE, NameLength4 = 0, 
										FkSchemaName = SQL_NULL_HANDLE, NameLength5 = 0, FkTableName = "order_itemtbl", NameLength6 = 13
				Return:	SQL_SUCCESS=0

Get Data All:
"PKTABLE_CAT", "PKTABLE_SCHEM", "PKTABLE_NAME", "PKCOLUMN_NAME", "FKTABLE_CAT", "FKTABLE_SCHEM", "FKTABLE_NAME", "FKCOLUMN_NAME", "KEY_SEQ", "UPDATE_RULE", "DELETE_RULE", "FK_NAME", "PK_NAME", "DEFERRABILITY"
"actestdb", "", "supplier_itemtbl", "`supp_no`", "acTestDB", "", "order_itemtbl", "`supp_no`", 1, 1, 1, "NULL", "NULL", 7
"actestdb", "", "supplier_itemtbl", "`item_no`", "acTestDB", "", "order_itemtbl", "`item_no`", 2, 1, 1, "NULL", "NULL", 7
2 rows fetched from 14 columns.

Derek, there's been some problems with this test case but now patch is being built. I will inform you of the progress here. Thanks again for your bug report.

I'd be glad to see a solution too, i posted my tables on the bug 8100 thread before realizing it had ben CLOSED... :)

since Bogdan told me in bug report 8100 that the problem was that the "comment" field in show table status was varchar(80) and therefore the foreign key string was truncated, I tried the following table structure eliminating the ON CASCADE contraint.
Now the "show table status" gives the right string ( < 80 chars) but i think that's not the point, since i still can't get this single foreign key imported in Visio.

----------------------------------------

CREATE TABLE `mod_news` (
  `id` int(11) NOT NULL auto_increment,
  `nome` text NOT NULL,
  `stato` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) TYPE=InnoDB AUTO_INCREMENT=1 ;

CREATE TABLE `mod_news_desc` (
  `id_news` int(11) NOT NULL default '0',
  `id_lingua` int(11) NOT NULL default '0',
  `titolo` varchar(255) default NULL,
  `testo` mediumtext,
  `attiva` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id_news`,`id_lingua`),
  KEY `id_lingua` (`id_lingua`)
) TYPE=InnoDB;

 
ALTER TABLE `mod_news_desc`
  ADD CONSTRAINT `mod_news_desc_ibfk_1` FOREIGN KEY (`id_news`) REFERENCES `mod_news` (`id`);

----------------------------------------
Maybe the problem is the one pointed out by Derek Lai up here.
Any news about the patch?

I'll inform Bogdan of news.

The quoting problem is fixed by the patch for Bug #4518 (which is not committed  or released yet, but should be in 3.51.15). The problem with foreign keys that can't be gleaned using the limited information from SHOW TABLE STATUS will be fixed as part of Bug #19923 (which I also hope to have fixed for 3.51.15).

Because this bug report doesn't report a problem above and beyond those, I'm going to go ahead and close it as a duplicate.

Thanks for the report.