On Mon, 7 Jan 2002, Tim Bunce wrote:
>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.)
Primarily on the client side; it could be set on the server side, but
that would be unusual as it would impact everybody (and, more
particularly, all the carelessly written programs that misuse double
quotes). On the other hand, it also makes sense to set it on the server
side; if the database needs delimited identifiers, then it makes sense
to ensure that everybody gets to use them.
>> 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?
Correct.
>> 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;
> }
I believe that would work.
>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.
I was looking at it from the other (another?) point of view - namely
that of an application written using identifiers that must be quoted,
either because they contain characters that are not ordinarily allowed
in identifiers (eg blanks and punctuation) or because they are identical
with keywords. Informix is pretty good about interpreting keywords as
identifiers when the keyword does not make sense in the context - you
can write apalling statements such as CREATE TABLE TABLE(NULL INT NOT
NULL, DECIMAL DATE NOT NULL, DATE DECIMAL(3), INT INTEGER, INTEGER INT).
What I can't do for an application is make a (valid SQL-92) statement
such as CREATE TABLE "TABLE"("NULL" INT NOT NULL, "More Than One Blank"
INT) work correctly unless DELIMIDENT is set when the connection is made
(and if it is set, then there is no work involved). So, a generic DBI
application cannot use double-quoted identifiers with Informix unless it
knows enough to set the Informix-specific DELIMIDENT environment
variable. Or, we could revise the DBI->connect method to allow the
application to indicate that it uses delimited identifiers:
$dbh = DBI->connect($dsn, $user, $pass, { EnableDelimitedIdentifiers => 1, RaiseError
=> 1, ...});
If this declarative option was available, DBD::Informix can do what is
necessary to make it work, and those drivers (the majority?) that do not
need to do anything to make delimited identifiers work can simply ignore
the attribute.
No, I'm not defending Informix's behaviour in this -- it irks me.
However, I am pointing out that there is a problem and that I'd prefer
to avoid breaking applications unnecessarily. However, if this is
sufficiently obscure that it won't wash as a general feature, then I
guess we live with it, albeit somewhat reluctantly.
--
Jonathan Leffler #include <disclaimer.h>
STSM, IBM Data Management Solutions. Phone: +1 650-926-6921
Email: [EMAIL PROTECTED], [EMAIL PROTECTED]
Guardian of DBD::Informix v1.00.PC1 -- http://dbi.perl.org
"I don't suffer from insanity; I enjoy every minute of it!"
