| Bug #39561 | SQLTables() doesn't properly handle empty strings to list catalogs and tables | ||
|---|---|---|---|
| Submitted: | 20 Sep 2008 18:21 | Modified: | 25 Jan 2012 0:37 |
| Reporter: | Farid Zidan (Candidate Quality Contributor) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S1 (Critical) |
| Version: | 5.1.9 | OS: | Windows (XP SP3) |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
| Tags: | qc, SQLTables table name NULL | ||
[2 Oct 2008 16:11]
Jess Balint
Branch pushed to launchpad: https://code.launchpad.net/~myodbc-developers/myodbc/bug39561
[9 Oct 2008 17:30]
Jess Balint
Merged to trunk. Will be released in 5.1.6.
[10 Oct 2008 12:54]
Tony Bedford
An entry was added to the 5.1.6 changelog: When the SQLTables method was called with NULL passed as the tablename parameter, only one row in the resultset, with table name of NULL was returned, instead of all tables for the given database.
[6 Oct 2011 22:49]
Farid Zidan
This stopped working in the new 5.1.9 version in the driver.
[20 Jan 2012 8:12]
Bogdan Degtyariov
It seems the revision 801 (bug39561) does not have the test case for empty strings, so the functionality has been broken and the test suite showed no errors.
[20 Jan 2012 10:11]
Bogdan Degtyariov
Thanks for bringing it back. The output from odbcte32 ----------------------------------------------------- Full Connect(Default) Env. Attr. SQL_ATTR_ODBC_VERSION set to SQL_OV_ODBC3 Successfully connected to DSN 'test'. SQLTables: In: StatementHandle = 0x00583028, CatalogName = "test", NameLength1 = 4, SchemaName = SQL_NULL_HANDLE, NameLength2 = 0, TableName = SQL_NULL_HANDLE, NameLength3 = 0, TableType = "TABLE", NameLength4 = 5 Return: SQL_SUCCESS=0 Get Data All: "TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "TABLE_TYPE", "REMARKS" "", "", "", "", "" "", "", "", "", "" "", "", "", "", "" 3 rows fetched from 5 columns.
[20 Jan 2012 10:47]
Bogdan Degtyariov
Sorry, pls ignore my previous comment. I used the DSN with charset=utf16. Obviously, ANSI version of odbcte32 could not show the unicode results correctly. After fixing it everything looked right: Get Data All: "TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "TABLE_TYPE", "REMARKS" "test", "", "proyecto", "TABLE", "" "test", "", "sleep_test", "TABLE", "" "test", "", "utf16tab", "TABLE", "" 3 rows fetched from 5 columns. Maybe the same happened to your test application? I tried not setting the default database and few other tricks such as "Don't use Information_Schema" option, but the driver always returned the correct result.
[21 Jan 2012 13:53]
Farid Zidan
My DSN has the Charset blank (default) and my app is 64-bit but ANSI. This still doe not work. odbcte32 may be masking this issue, but the issue is always reproduceable as described by executing ::SQLTables using the code fragment given in the issue description.
[23 Jan 2012 8:09]
Bogdan Degtyariov
Farid, thank you for the additional information. The important detail here is that parameters should not be NULL (my test case used NULL), they have to be empty strings. Setting the status to verified.
[24 Jan 2012 4:32]
Bogdan Degtyariov
Farid,
I had a very long discussion with the developers about the problem last night.
In the current context NULL and empty string arguments for SQLTables() should be treated in different ways:
"... Passing a null pointer to a search pattern argument does not constrain the search for that argument; that is, a null pointer and the search pattern % (any characters) are equivalent. However, a zero-length search pattern — that is, a valid pointer to a string of length zero — matches only the empty string ("")..."
More information can be found here:
http://msdn.microsoft.com/en-us/library/windows/desktop/ms710171%28v=vs.85%29.aspx
ODBC Reference for SQLTables function:
http://msdn.microsoft.com/en-us/library/ms711831%28v=vs.85%29.aspx
I performed several tests with MSSQL Server 2005/2008. The test results are:
- for szTableSchem=NULL and szTableName=NULL the result of SQLTables() is
the list of all tables in the current catalog.
- for szTableSchem="" and szTableName="" the result of SQLTables() is
empty because the table names compared to the empty strings.
Hence, our driver is not returning the correct result:
CURRENTLY: the catalog (database name and all other columns in the result set are NULL)
SHOULD BE: empty result set.
[25 Jan 2012 0:37]
Farid Zidan
Thanks Bogdan. Some example cases from MS doc which would be useful in testing the driver behavior. The first example noted below uses NULLs. http://msdn.microsoft.com/en-us/library/ms131341.aspx // Get a list of all tables in the current database. SQLTables(hstmt, NULL, 0, NULL, 0, NULL, 0, NULL,0);
Description: Execute SQLTables passing NULL as tablename. You should get all the tables for the given database, instead you get only one row in the resultset with table name of NULL. ODBC connect 3.5x.x correctly handles this call, but the 5.1.5 driver does not. How to repeat: // execute code fragment after replacing szTableCat with your own test database // name. Should return all tables for the database. Instead one row is returned // const TCHAR *szTableCat = "your database name"; ASSERT(FALSE); const TCHAR *szTableSchem = ""; const TCHAR *szTableName = ""; const TCHAR *szTableType = "TABLE"; size_t cbTableCat = strlen( szTableCat ); size_t cbTableSchem = strlen( szTableSchem ); size_t cbTableName = strlen( szTableName ); size_t cbTableType = m_sTableType.GetLength(); SQLRETURN rc = ::SQLTables( pSQL->Gethstmt(), ( SQLCHAR * ) ( *szTableCat ? szTableCat : NULL ), ( SQLSMALLINT ) cbTableCat, ( SQLCHAR * ) ( *szTableSchem ? szTableSchem : NULL ), ( SQLSMALLINT ) cbTableSchem, ( SQLCHAR * ) ( *szTableName ? szTableName : NULL ), ( SQLSMALLINT ) cbTableName , ( SQLCHAR * ) ( !m_sTableType.IsEmpty() ? (SQLCHAR *)(LPCTSTR) m_sTableType : NULL ), ( SQLSMALLINT ) cbTableType ); Suggested fix: Correctly handle NULL passed as catalog name, table name, or type type.