FYI, now it's like this:
sub quote_identifier {
my $dbh = shift;
my $id = shift;
my $opt = shift; # optional, quote only if needed
# ignore null elements (ie catalog, schema)
my @id = (ref $id) ? grep { defined } @$id : ($id);
s/"/""/g foreach @id; # escape embedded quotes
foreach (@id) { # quote the elements
next if $opt && /^[a-z_]\w*$/i;
$_ = qq{"$_"};
}
return join '.', @id; # ... and join the dots
}
i.e.,
$quoted = $dbh->quote_identifier($table);
$quoted = $dbh->quote_identifier($table, 1);
$quoted = $dbh->quote_identifier([$catalog,$schema,$table]);
$quoted = $dbh->quote_identifier([$catalog,$schema,$table], 1);
Tim.
On Tue, Jan 01, 2002 at 10:46:37PM +0000, Tim Bunce wrote:
> FYI, here's what I'm playing with at the moment:
>
> sub quote_identifier {
> my $dbh = shift;
> my @id = grep { defined } @_; # ignore null elements (ie catalog, schema)
> s/"/""/g foreach @id; # escape embedded quotes
> $_=qq{"$_"} foreach @id; # quote the element
> return join '.', @id; # ... and join the dots
> }
>
> Any drivers that need different behaviour will need to define their
> own method to override this default.
>
> Tim.
>
> On Thu, Oct 25, 2001 at 01:25:54AM +0100, Tim Bunce wrote:
> > On Wed, Oct 24, 2001 at 04:26:18PM +0100, Simon Oliver wrote:
> > >
> > > > > 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?
> > >
> > > Don't know - I'll run some tests.
> >
> > Thanks.
> >
> > > I suppose as long as the DBD knows that quoted identifiers are not in
> > > use it would account for this in the overrided method.
> > >
> > > > > 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?
> > > Doubt it.
> > >
> > > Say I'm running Access and I want a list of tables (but not catalog
> > > names) I need a nifty regexp to parse out the table name from the
> > > quoted, dotted identifier returnd by $dbh->tables(). Instead I just set
> > > IdentifierColumns = [2].
> >
> > Or, since tables() is only a few lines or code, write your own.
> >
> > > > > 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.
> > >
> > > So would I. I can only speak from experience. The above example does
> > > cause an error with MS-Access.
> > >
> > > A few tests reveal:
> > > Access returns only "catalog" and "table" via ODBC / ADO-ODBC
> > > Access returns only "table" via ADO-OLEDB
> > > MS-SQL Server returns all three
> > > ASA returns only "schema" and "table" via ODBC / ADO-ODBC / ASAny
> >
> > Sure, but that wasn't my question. I'm not too surprised that
> > "myfile".""."mytable" fails, but what about "myfile".."mytable"?
> >
> > And what's the 'standard' way? Maybe the answer's in here:
> >
>http://www.jtc1sc32.org/sc32/jtc1sc32.nsf/Attachments/DF86E81BE70151D58525699800643F56/$FILE/32N0595T.PDF
> > but I've no time to look right now. Can you?
> >
> > Tim.
