On Wed, Oct 24, 2001 at 12:23:22PM +0100, Simon Oliver wrote:
> > 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.
Empty? I presume you mean undef, so
return join '.', map { defined $_ ? qq{"$_"} : () } @_;
but see below.
> 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.
But does it still allow standard double quotes?
And I was just refering to the default implementation, drivers
would be free to define their own.
> 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.
Is there an ODBC equivalent to that, or would ODBC just set
SQL_IDENTIFIER_QUOTE_CHAR to blank in that case?
> 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{"}.
> 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.
Is there an ODBC equivalent to that?
> Finally, since identifiers like "myfile".""."mytable" are likely to
> cause an error, I modifed tables() to omit undefined columns.
I'd like to see documented somewhere what the correct syntax would
be for a table with a catalog name but no schema name.
> 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;
> }
Something along those lines, yes. Subject to the above issues.
Tim.
