On Sun, Jan 06, 2002 at 09:21:50PM -0800, Jonathan Leffler wrote:
> Simon Oliver wrote:
>
> >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.
> >>
> >
> >Makes sense. DBD coders should be *encouraged* to override this method if the
> >undelying DBMS is capable of changes to the quoting behaviour. This would
> >include DBD::ODBC, DBD::ADO, DBD::Sybase, DBD::ASAny to name a few that I know
> >this applies to.
> >
> >As an example, if quoted identifiers is turned off as a database option then
> >then the DBD's overriding method should realise this and return a non-quoted
> >identifier.
> >
> >And any method that returns an identifier should pass it through
> >$dbh->quote_identifier as matter of course. Currently I think this only
> >applied to $dbh->tables, but I forsee the list growing with the inclusion of
> >AnyDBD interface, new metadata methods and SQL rewriting stuff.
>
> Hmmmm, basically, I see what's going on. Informix only supports
> delimited identifiers if the environment variable DELIMIDENT is set when
> the connection is initiated, which will make this very difficult to handle.
Set on the client side or server side? (sounds like client side
from what you say below.)
> In the absence of DELIMIDENT, Informix allows both single
> quotes and double quotes to identify strings (an issue predating the SQL
> standards on the subject).
And provides no way to quote identifiers in that case?
> So, although the method outlined by Tim is
> correct, it won't work usefully unless DELIMIDENT is set, and DELIMIDENT
> needs to be set prior to making the connection so it can't be handled
> when the quote_identifier method is called. So, it would be useful if
> there was a connection time attribute that could be set indicating that
> delimited identifiers will be used -- if that's available, DBD::Informix
> can set DELIMIDENT before creating the connection and then all will be
> fine. Without that, Informix won't be able to do much -- you'll simply
> get (at best) syntax errors and (at worst) misunderstood SQL.
Couldn't you just record if DELIMIDENT is set at the time you connect...
Then your quote_identifier method can just be:
sub quote_identifier {
my $dbh = shift;
return $dbh->SUPER::quote_identifier(@_) if $dbh->{ix_delimident};
@id = grep { defined } @_;
join '.', @id;
}
The assumption being that if users don't have DELIMIDENT set then
the database isn't going to have any id's that _need_ quoting.
Or, if it does have id's that need quoting then users should set DELIMIDENT.
In other words, "go with the flow" that Informix uses already.
Tim.
