[ODBC-244] Microsoft Excel 365 ODBC Navigator can't find tables Created: 2019-04-18  Updated: 2021-07-01  Resolved: 2019-04-25

Status: Closed
Project: MariaDB Connector/ODBC
Component/s: General
Affects Version/s: 3.0.8
Fix Version/s: 3.0.9, 3.1.1

Type: Bug Priority: Major
Reporter: Geoff Montee (Inactive) Assignee: Lawrin Novitsky
Resolution: Duplicate Votes: 0
Labels: None

Issue Links:
Duplicate
duplicates ODBC-225 Excel+MariaDB driver not showing list... Closed
Relates
relates to ODBC-326 Connecting Excel with MariaDB through... Closed

 Description   

I'm using Microsoft Excel 365 version 1903 on Windows 10. When I try to import a table into an Excel spreadsheet with MariaDB Connector/ODBC 3.0.8, Excel can't actually seem to find any tables.

When I look at the general query log on the backend server, The ODBC connector seems to be executing the following query:

                  356 Prepare   SELECT SCHEMA_NAME AS TABLE_CAT, CONVERT(NULL,CHAR(64)) AS TABLE_SCHEM, CONVERT(NULL,CHAR(64)) AS TABLE_NAME, NULL AS TABLE_TYPE, NULL AS REMARKS FROM INFORMATION_SCHEMA.SCHEMATA GROUP BY SCHEMA_NAME ORDER BY SCHEMA_NAME
 
                  356 Execute   SELECT SCHEMA_NAME AS TABLE_CAT, CONVERT(NULL,CHAR(64)) AS TABLE_SCHEM, CONVERT(NULL,CHAR(64)) AS TABLE_NAME, NULL AS TABLE_TYPE, NULL AS REMARKS FROM INFORMATION_SCHEMA.SCHEMATA GROUP BY SCHEMA_NAME ORDER BY SCHEMA_NAME
 
                  356 Close stmt

It makes sense that Microsoft Excel can't find the tables because TABLE_NAME seems to be NULL in the output of this query.

It is strange that MariaDB Connector/ODBC is querying information_schema.SCHEMATA if Microsoft Excel wants the names of available tables. It should probably query information_schema.TABLES instead.

I enabled Tracing in Windows ODBC Data Source Administrator to see what function Microsoft Excel is calling, and it seems to be calling SQLTablesW:

Microsoft.Mashu d68-4d48	ENTER SQLTablesW 
		HSTMT               0x0263DD18
 
		WCHAR *             0x02EF809C [       1] "%"
 
		SWORD                        1 
		WCHAR *             0x00000000 <null pointer>
 
		SWORD                        0 
		WCHAR *             0x00000000 <null pointer>
 
		SWORD                        0 
		WCHAR *             0x030A01A4 [      10] "TABLE,VIEW"
 
		SWORD                       10 
 
 
Microsoft.Mashu d68-4d48	EXIT  SQLTablesW  with return code 0 (SQL_SUCCESS)
 
		HSTMT               0x0263DD18
 
		WCHAR *             0x02EF809C [       1] "%"
 
		SWORD                        1 
		WCHAR *             0x00000000 <null pointer>
 
		SWORD                        0 
		WCHAR *             0x00000000 <null pointer>
 
		SWORD                        0 
		WCHAR *             0x030A01A4 [      10] "TABLE,VIEW"
 
		SWORD                       10

Which seems to be a variant of SQLTables:

https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqltables-function?view=sql-server-2017

This function seems to be defined here:

https://github.com/MariaDB/mariadb-connector-odbc/blob/3.0.8/odbc_3_api.c#L3164

And it looks like it might indirectly call this function:

https://github.com/MariaDB/mariadb-connector-odbc/blob/3.0.8/ma_statement.c#L3332

It looks like the bug might be here:

https://github.com/MariaDB/mariadb-connector-odbc/blob/3.0.8/ma_statement.c#L3368

Shouldn't this actually be the following?:

if (CatalogName && NameLength1 && !NameLength3 && !strcmp(CatalogName, SQL_ALL_CATALOGS ))



 Comments   
Comment by Lawrin Novitsky [ 2019-04-22 ]

This is duplicate of ODBC-225. Technically it should be SQL_ALL_CATALOGS in that string, but both macro definitions have the same value. Since you see the select from SCHEMATA probably the bug there is that the condition in that line catches also if table name is NULL, but should only if it is empty string.
However in ODBC-225 there is (full) trace file, and it contains 2 SQLTables calls. One like yours, and one with SQL_ALL_CATALOGS and empty strings.

Comment by Geoff Montee (Inactive) [ 2019-04-22 ]

However in ODBC-225 there is (full) trace file, and it contains 2 SQLTables calls. One like yours, and one with SQL_ALL_CATALOGS and empty strings.

It looks like my trace also has both calls:

Microsoft.Mashu d68-4d48	ENTER SQLTablesW 
		HSTMT               0x0263DD18
 
		WCHAR *             0x02EF809C [       1] "%"
 
		SWORD                        1 
		WCHAR *             0x02B81230 <zero length>
 
		SWORD                        0 
		WCHAR *             0x02B81230 <zero length>
 
		SWORD                        0 
		WCHAR *             0x02B81230 <zero length>
 
		SWORD                        0 
 
 
Microsoft.Mashu d68-4d48	EXIT  SQLTablesW  with return code 0 (SQL_SUCCESS)
 
		HSTMT               0x0263DD18
 
		WCHAR *             0x02EF809C [       1] "%"
 
		SWORD                        1 
		WCHAR *             0x02B81230 <zero length>
 
		SWORD                        0 
		WCHAR *             0x02B81230 <zero length>
 
		SWORD                        0 
		WCHAR *             0x02B81230 <zero length>
 
		SWORD                        0 
 
 
...
 
 
 
Microsoft.Mashu d68-4d48	ENTER SQLTablesW 
		HSTMT               0x0263DD18
 
		WCHAR *             0x02EF809C [       1] "%"
 
		SWORD                        1 
		WCHAR *             0x00000000 <null pointer>
 
		SWORD                        0 
		WCHAR *             0x00000000 <null pointer>
 
		SWORD                        0 
		WCHAR *             0x030A01A4 [      10] "TABLE,VIEW"
 
		SWORD                       10 
 
 
Microsoft.Mashu d68-4d48	EXIT  SQLTablesW  with return code 0 (SQL_SUCCESS)
 
		HSTMT               0x0263DD18
 
		WCHAR *             0x02EF809C [       1] "%"
 
		SWORD                        1 
		WCHAR *             0x00000000 <null pointer>
 
		SWORD                        0 
		WCHAR *             0x00000000 <null pointer>
 
		SWORD                        0 
		WCHAR *             0x030A01A4 [      10] "TABLE,VIEW"
 
		SWORD                       10

Comment by Lawrin Novitsky [ 2019-04-23 ]

Geoff, just for future - it is always better to provide full trace. Well, if it is huge, then only related part. And this is first thing to ask. Not for this issue though - I already have it in 225. And thanks - I going to verify it now, but it looks like the bug in the code is really in that row.

Generated at Thu Feb 08 03:27:16 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.