Re: thorny problem with table_info implementation

On 02/09/2013 20:27, Martin J. Evans wrote:
> Hi,
>
> Firstly, I've just come back from holidays, I'm distracted by other
> things right now and might not be back in the swing of things properly -
> so beware.
>
> table_info maps to the ODBC API SQLTables in DBD::ODBC. DBI seems to
> have picked some functionality for table_info from ODBC in that if '%'
> is passed for one of catalog, schema and table and the other 2 are empty
> strings the result only contains a list of catalogs, schemas or tables.
>
> Someone just reported to me that in DBD::ODBC if you call
> table_info('%','','') you get all catalogs, schemas and tables back but
> you should get only a list of catalogs. I tracked this down to the
> following code in DBD::ODBC (ANSI case here, unicode one is slightly
> more complex):
>
>      if (SvOK(catalog)) acatalog = SvPV_nolen(catalog);
>      if (SvOK(schema)) aschema = SvPV_nolen(schema);
>      if (SvOK(table)) atable = SvPV_nolen(table);
>      if (SvOK(table_type)) atype = SvPV_nolen(table_type);
>
>         rc = SQLTables(imp_sth->hstmt,
>                        (acatalog && *acatalog) ? acatalog : NULL,SQL_NTS,
>                        (aschema && *aschema) ? aschema : NULL, SQL_NTS,
>                        (atable && atable) ? atable : NULL, SQL_NTS,
>                        (atype && *atype) ? atype : NULL,
>                        SQL_NTS        /* type (view, table, etc) */
>                        );
>
> What is happening here is that whatever is passed to table_info, if it
> is defined and NOT the empty string we pass the string else NULL and for
> SQLTables NULL is very different from the empty string (see special
> cases above). However, if you call:
>
> $dbh->table_info('%', '', '') to just get catalogs what is passed to
> SQLTables is '%', NULL, NULL and this causes all tables to be returned
> instead of only catalogs.
>
> The fix seems obvious, remove the test for *acatalog etc which I did and
> it fixes the problem \o/
>
> except, DBD::ODBC test code calls table_info() (and for all I know other
> people do too) and that ends up in DBI calling table_info with 4 empty
> strings. Empty strings are not the same as undef/NULL and it results in
> SQLTables returning no tables at all when before it would return all
> tables.
>
> As far as I can see, at some point in the past, someone (maybe me)
> realised this and changed DBD::ODBC to workaround this issue instead of
> questioning what DBI passes.
>
> Now, table_info() with no arguments is not mentioned in the DBI pod so
> you could say that is undefined behaviour but it is a little late for
> that now as DBD::ODBC uses it in test code and as far as I know, so do
> others. However, to fix the initial bug I cannot know when empty strings
> are supposed to be undef/NULL and when they are supposed to be empty
> strings. So I can fix the bug as shown but only if I change DBD::ODBC
> test code from table_info() to table_info('%', '%', '%', '%') and I
> cannot change anyone elses code.
>
> Any suggestions?
>
> Martin

Correction, in that last paragraph (I did warn you), I should have said 
table_info() to table_info('%', '%', '%') i.e.,

table_info('%', '', '') returns catalogs only
table_info('', '%', '') returns schemas only
table_info('', '', '', '%') returns types only
table_info('%', '%', '%') returns everything
table_info() did return everything due to workaround in DBD::ODBC but 
fixing bug ends up in table_info() returning nothing.

Martin
-- 
Martin J. Evans
Wetherby, UK