table_info('','','','%') cannot return any types
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.
Any comments?
BTW, all examples were done with DBD::ODBC.
Martin
Re: table_info('','','','%') cannot return any types
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.
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
DBDdb::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
Re: table_info('','','','%') cannot return any types
On Thu, Jun 25, 2015 at 11:08:14AM +0100, Martin J. Evans wrote:
> On 24/06/15 13:24, Tim Bunce wrote:
> >
> >The tables('', '', '', '%') call is a special case so it seems
> >reasonable to handle it as a special case in the code.
>
> diff --git a/DBI.pm b/DBI.pm
> [...]
> 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.
Great. Thanks Martin.
Tim.
