Re: the return of tables ()

On Mon, 1 Sep 2008 22:59:24 +0100, Tim Bunce <[email protected]>
wrote:

> On Mon, Sep 01, 2008 at 03:28:48PM +0200, H.Merijn Brand wrote:
> > > > > > 
> > > > > > 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)?
> > > > 
> > > > These are the patches I feel comfortable with, as they obviously will
> > > > not clash with *opinions*. Is the repo in git? in that case, I'll take
> > > > the bit. If it is in svn, I'll pass.
> > > 
> > > svn, pity.
> > 
> > OK, patch:
> 
> Thanks. Applied.
> 
> > > > > > 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;
> > > > > > »           }
> 
> > > > > > 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:
> > > > 
> > > > I do not have a quote_identifier () method. I rely on DBI doing the
> > > > right thing.
> > > 
> > > I believe the DBI's quote_identifier does the right thing.
> > > 
> > > I'd guess that the CATALOG value returned by table_info is an empty
> > > string but should be an undef.
> > 
> > Not defined at all, so I *guess* that is the same as undefined.
> 
> I wasn't very clear (and was actually partly misleading). Sorry.
> 
> What I meant was that the TABLE_CAT and/or TABLE_SCHEM columns in the
> data returned by your table_info () method are probably empty strings but
> should be undefs.
> 
> What does
>     Data::Dumper::Dumper($dbh->table_info(...)->fetchall_arrayref)
> show for an example table?

$ perl -Iblib/{lib,arch} -MPROCURA::DBD -MData::Dumper
-wle'$dbh=DBDlogon;print Dumper $dbh->table_info(undef,"PROLEP","parm")->fetchall_arrayref' $VAR1 = [
          [
            '',
            'PROLEP',
            'parm',
            'T',
            'W'
          ]
        ];

Correct, as that is the result of

sub table_info
{
    my $dbh = shift;
    my ($catalog, $schema, $table, $type, $attr);
    ref $_[0] or ($catalog, $schema, $table, $type) = splice @_, 0, 4;
    if ($attr = shift) {
	ref ($attr) eq "HASH" or
	    Carp::croak qq{usage: table_info ({ TABLE_NAME => "foo", ... })};
	exists $attr->{TABLE_SCHEM} and $schema = $attr->{TABLE_SCHEM};
	exists $attr->{TABLE_NAME}  and $table  = $attr->{TABLE_NAME};
	exists $attr->{TABLE_TYPE}  and $type   = $attr->{TABLE_TYPE};
	}
    if ($catalog) {
	$dbh->{Warn} and
	    Carp::carp "Unify does not support catalogs in table_info\n";
	return;
	}
    my @where;
    $schema and push @where, "OWNR       like '$schema'";
    $table  and push @where, "TABLE_NAME like '$table'";
    $type   and $type = uc substr $type, 0, 1;
    $type   and push @where, "TABLE_TYPE like '$type'";
    local $" = " and ";
    my $where = @where ? " where @where" : "";
    my $sth = $dbh->prepare (
	"select '', OWNR, TABLE_NAME, TABLE_TYPE, RDWRITE ".
	"from   SYS.ACCESSIBLE_TABLES ".
	$where);
    $sth or return;
    $sth->execute;
    $sth;
    } # table_info

select NULL, OWNR, ...

is not allowed. I don't see an easy way out

sql> select NULL, OWNR, TABLE_NAME
sql>    from SYS.ACCESSIBLE_TABLES
sql>    where TABLE_NAME like 'parm';
NULL is not allowed in a constant list (-10518).

-- 
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/