Re: table_info('','','','%') cannot return any types

On 24/06/15 13:24, Tim Bunce wrote:
> On Tue, Jun 23, 2015 at 09:12:16AM +0100, Martin J. Evans wrote:
>> I was recently confirming table_info special cases and discovered the case for getting table_types cannot work.
>>
>> table_info('','','','%')
>>
>> should return a list of table types but it returns a list of empty strings instead:
>>
>> my @types = $h->tables('', '', '', '%');
>> print "all types:\n", join("xxx\n", @types), "\n";
>> # should output something like:
>> # "dbo"
>> # "INFORMATION_SCHEMA"
>> # "sys"
>> # and actually outputs:
>> xxx
>> xxx
>>
>> It seems to be down to the following in DBI.pm:
>>
>>      sub tables {
>> 	my ($dbh, @args) = @_;
>> 	my $sth    = $dbh->table_info(@args[0,1,2,3,4]) or return;
>> 	my $tables = $sth->fetchall_arrayref or return;
>> 	my @tables;
>> 	if ($dbh->get_info(29)) { # SQL_IDENTIFIER_QUOTE_CHAR
>>              # problem is missing 3 in the slice below
>> 	    @tables = map { $dbh->quote_identifier( @{$_}[0,1,2] ) } @$tables;
>> 	}
>>
>> My test case missed this because currently it is returning 3 values but they are all ''.
>>
>> Adding 3 to the slice fixes the issue but unfortunately changes the data returned from the deprecated tables method which now returns values like this:
>>
>> "master"."dbo"."DBD_ODBC_LOB_TEST"."TABLE"
>>
>> instead of (before)
>>
>> "master"."dbo"."DBD_ODBC_LOB_TEST"
>>
>> table_info is ok because it returns a result set and not a set of values pushed through quote_identifier.
>
> Thanks for the great analysis Martin.
>
>> Any comments?
>
> The tables('', '', '', '%') call is a special case so it seems
> reasonable to handle it as a special case in the code.
>
> Tim.
>

diff --git a/DBI.pm b/DBI.pm
index a23bed8..6e0e592 100644
--- a/DBI.pm
+++ b/DBI.pm
@@ -1761,7 +1761,10 @@ sub _new_sth {   # called by DBD::<drivername>::db::prepare)
         my $sth    = $dbh->table_info(@args[0,1,2,3,4]) or return;
         my $tables = $sth->fetchall_arrayref or return;
         my @tables;
-       if ($dbh->get_info(29)) { # SQL_IDENTIFIER_QUOTE_CHAR
+    if (defined($args[3]) && $args[3] eq '%' && # special case for tables('','','','%')
+            grep {defined($_) && $_ eq ''} @args[0,1,2]) {
+        @tables = map {$_->[3]} @$tables;
+    } elsif ($dbh->get_info(29)) { # SQL_IDENTIFIER_QUOTE_CHAR
             @tables = map { $dbh->quote_identifier( @{$_}[0,1,2] ) } @$tables;
         }
         else {          # temporary old style hack (yeach)

adds a special case, passes DBI tests for me and passes my testing. There is no need to quote the type as the DB should accept what it passed out.

tables('','','','%') now returns (for my ODBC DB):
SYSTEM TABLE
TABLE
VIEW

and no other calls to tables should be affected.

Martin