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.
