> sub quote_identifier {
> shift;
> return join '.', map { qq{"$_"} } @_;
> }
>
> would be enough to both quote single names and add dots for multiple names.
> return join '.', map { qq{"$_"} } @_;
this looks nice but if one of the identifiers is empty will get a
warning.
This assumes that q{"} is the quotation mark and q{.} is used to join
identifiers, which is true for most systems but might not be. For
example Microsoft Access permits the use of [] to quote identifiers.
The QuotedIdentifiers(boolean) attribute is required for the situation
where the database might have quoted identifiers switched off (as in
JDBC and MS-SQL) and could cause a sytax error.
For DBD::ODBC we could use print $dbh->func( 29, GetInfo ) to determine
the correct IdentifierQuoteStart character to use and set it
automatically on connect. I'm sure there are simillar functions for
other DBMS's - otherwise they will use the default of q{"}.
IdentifierQuoteStart and QuotedIdentifiers could access tied scalars in
the DBD, which could then get/set directly from the underlying database
as appropriate.
The reason for IdentifierColumns is to allow the DBD or user the
override the default of columns 0,1,2 (catalog,schema,table) when
building the identifier.
I realise that there is no reason to make these functions object methods
(as I had previously defined them.
This all could be rewitten to a single function as follows:
sub quote_identifier {
my $qs = $dbh->{IdentifierQuoteStart};
my $qe = $dbh->{IdentifierQuoteEnd} || $dbh->{IdentifierQuoteStart};
return join $dbh->{IdentifierSeparator}, map( {
my $id = $_ || '';
$dbh->{QuotedIdentifiers} ? "$qs$id$qe" : $id;
} @_ );
}
Finally, since identifiers like "myfile".""."mytable" are likely to
cause an error, I modifed tables() to omit undefined columns. I suppose
this code could be moved into quote_identifier too:
sub quote_identifier {
my $qs = $dbh->{IdentifierQuoteStart};
my $qe = $dbh->{IdentifierQuoteEnd} || $dbh->{IdentifierQuoteStart};
my @ids; for (@_) { push @ids, $_ if defined $_ };
return join $dbh->{IdentifierSeparator}, map(
{ $dbh->{QuotedIdentifiers} ? "$qs$_$qe" : $_ } @ids );
}
and then fix tables() like so:
sub tables {
my ($dbh, @args) = @_;
my $sth = $dbh->table_info(@args);
return unless $sth;
my ($row, @tables);
while($row = $sth->fetch) {
push @tables, quote_identifier(@$row[@{$dbh->{IdentifierColumns}}]);
}
return @tables;
}
If you want to keep it simple then we could end up with:
sub quote_identifier {
my $q = $dbh->{IdentifierQuote};
my @ids; for (@_) { push @ids, $_ if defined $_ };
return join '.', map(
{ $dbh->{QuotedIdentifiers} ? "$q$_$q" : $_ } @ids );
}
and then fix tables() like so:
sub tables {
my ($dbh, @args) = @_;
my $sth = $dbh->table_info(@args);
return unless $sth;
my ($row, @tables);
while($row = $sth->fetch) {
push @tables, quote_identifier(@$row[0..2]);
}
return @tables;
}
--
Simon Oliver
