Re: the return of tables ()

On Wed, Aug 20, 2008 at 07:38:36PM +0200, H.Merijn Brand wrote:
> In my quest of improving on DBD::Unify, I implemented - as per DBI
> documentation suggestion:
> 
> "Other database handle methods
> 
>  As with the driver package, other database handle methods may follow
>  here. In particular you should consider a (possibly empty) disconnect ()
>  method and possibly a quote () method if DBI's default isn't correct for
>  you. You may also need the type_info_all () and get_info () methods, as
>  described elsewhere in this document."
> 
> the methods get_info () and type_info_all (), which I generated on Windows
> with Strawberry perl as that is the only place where I got ODBC working
> in a somewhat reliable way, exactly like the docs show me. Note here that
> 
> http://search.cpan.org/~timb/DBI-1.607/lib/DBI/DBD.pm#Generating_the_get_info_method
> 
> shows the perl command without quotes and parens, so it is completely
> useless as an example
> 
> perl -MDBI::DBD::Metadata -we "write_getinfo_pm (qw{ dbi:ODBC:foo_db username password Driver })"
> 
> would be a portable solution to not mix up the quotes on WinShit

Patches welcome. Want a commit bit (if you don't have one already)?

> Anyway, I put the generated files in place and my tests started to fail.
> I did expect some fails, but not this one:
> 
> As get_info (29) now returns a TRUE value, the 'tables ()' method is
> using a different strategy to build the list it returns:
> 
>     sub tables
>     {
>         my ($dbh, @args) = @_;
>         my $sth    = $dbh->table_info (@args[0..4]) or return;
>         my $tables = $sth->fetchall_arrayref or return;
>         my @tables;
> »       if ($dbh->get_info (29)) { # SQL_IDENTIFIER_QUOTE_CHAR
> »           @tables = map { $dbh->quote_identifier (@{$_}[0,1,2]) } @$tables;
> »           }
>         else {          # temporary old style hack (yeach)
>             @tables = map {
>                 my $name = $_->[2];
>                 if ($_->[1]) {
>                     my $schema = $_->[1];
>                     # a sad hack (mostly for Informix I recall)
>                     my $quote = ($schema eq uc $schema) ? '' : '"';
>                     $name = "$quote$schema$quote.$name";
>                     }
>                 $name;
>                 } @$tables;
>             }
>         return @tables;
>         } # tables
> 
> With a true value for get_info (29), tables () uses the first block,
> where it used to use the second block.
> 
> Unify has no support for CATALOG's, so the values in info are not
> defined, but still used in the map, causing all my tables no showing up
> with and empty "". in front of it, which is illegal to the database :(

Seems like your quote_identifier() method is doing the wrong thing.
The docs for quote_identifier say:

    **Undefined names are ignored** and the remainder are quoted and then
    joined together, typically with a dot (".") character.

Tim.

> I think therefor that in this case, the catalog setting must also be
> checked, somewhat like this:
> 
>     sub tables
>     {
>         my ($dbh, @args) = @_;
>         my $sth    = $dbh->table_info (@args[0..4]) or return;
>         my $tables = $sth->fetchall_arrayref or return;
>         my @tables;
>         if ($dbh->get_info (29)) { # SQL_IDENTIFIER_QUOTE_CHAR
>             # Check SQL_CATALOG_USAGE
>             my @range = $dbh->get_info (92) ? (0..2) : (1..2);
>             @tables = map {
>                 $dbh->quote_identifier (@{$_}[@range])
>                 } @$tables;
>             }
>         else {          # temporary old style hack (yeach)
>             @tables = map {
>                 my $name = $_->[2];
>                 if ($_->[1]) {
>                     my $schema = $_->[1];
>                     # a sad hack (mostly for Informix I recall)
>                     my $quote = ($schema eq uc $schema) ? '' : '"';
>                     $name = "$quote$schema$quote.$name";
>                     }
>                 $name;
>                 } @$tables;
>             }
>         return @tables;
>         } # tables
> 
> 
> 
> 
> 
> -- 
> H.Merijn Brand          Amsterdam Perl Mongers  http://amsterdam.pm.org/
> using & porting perl 5.6.2, 5.8.x, 5.10.x, 5.11.x on HP-UX 10.20, 11.00,
> 11.11, 11.23, and 11.31, SuSE 10.1, 10.2, and 10.3, AIX 5.2, and Cygwin.
> http://mirrors.develooper.com/hpux/           http://www.test-smoke.org/
> http://qa.perl.org      http://www.goldmark.org/jeff/stupid-disclaimers/