On Sun, 20 Jan 2002, Tim Bunce wrote:
>On Mon, Jan 14, 2002 at 09:58:51AM +0000, Simon Oliver wrote:
>> > If table_info doesn't return the string you need to use, then you do
>> > need a method to convert what table_info does supply into a format that
>> > you can use.
>>
>> table_info, like most (if not all) meta-fata methods returns returls as a
>> recordset, with the seperate portions of the identifier represneted as
>> distinct columns, hence no need to delimit the identifier. This is standard
>> across every DBMS and API I have come across.
>
>And the data fetched from the $sth returned by table_info has the
>'raw' schema (table, field, etc) names exactly as the database knows them.
>
>It's the $dbh->tables() method that needs to use quote_identifier, not table_info().
Correction accepted - it is too long since I looked at the specs of either to
be sure.
>> > If DBD::Informix is told to quote one of those names, however it was
>> > obtained, and the DELIMIDENT variable is not set (was not set when the
>> > connection was created), then blindly quoting the name can lead to the
>> > wrong results:
>> >
>> > WHERE "column name with blanks" > 'abc'
>>
>> But the quote_identifiers method should not blindly quote the name. The DBD
>> should take account of the current state for the underlying DBMS's DELIMITED
>> variabel (or equivalent) and take appropriate action.
>
>The quote_identifiers method _should_ blindly quote the name for
>_most_ drivers since the name may be a reserved word and the quoting
>should be harmless (albeit a little 'noisy').
I'm not convinced of this hypothesis.
If I ask for "SELECT * FROM absinthe" and "select * from ABSINTHE" and
"Select * From Absinthe", in the absence of quote_identifier, I access the
same table. In the presence of quote_identifier, I access three different
tables. Further, for a portable application, it is not clear which spelling
is the correct one to use - the plausible candidates are ABSINTHE and absinthe,
but one of those is going to be wrong. So, using quote_identifier places a
far bigger burden on the application than not using it.
>For DBD::Informix I think it _should_ blindly quote the name _if_
>DELIMIDENT env var was set at connect time.
Only if that's what DBI thinks is correct. I don't think quoting should
be done at all, but I'm in a minority of one.
>If DELIMIDENT is not set then I think DBD::Informix _could_ resonably
>default to the "quote only if it obviously needs it" approach since
>either quoting or not quoting something that needs quoting will generate
>an error either way.
I don't think DBD::Informix should be having to think that hard. In my
view (what I undertook to provide, 5-plus years ago), DBD::Informix is
supposed to be a way to get SQL requests from the user to the database.
It is not supposed to rewrite SQL for the user.
I guess the argument with quote_identifier is that we are supposed to
help the user rewrite their SQL. I'm not convinced that it is going to
do that.
However, DBD::Informix will go along with whatever DBI decides. Most
probably, it simply let DBI do its stuff and not override the default
method. If it doesn't work, it is the application's problem - with or
without use of quoted_identifier. Just the same as it is now, without
the quoted_identifier method. The default quoted_identifier will make
the correct transformation for Informix; the result may not be usable,
or it may be usable but yield the wrong result, but both those can
happen whether or not quoted_identifier is called.
AFAIAC, this is another case of DBI trying to do too much - AutoCommit
is the other prime example.
>> > Another nasty - what about a regular table name that has column
>> > names that must be quoted. A simple perusal of the tables part of
>> > the system catalog is not sufficient.
>> I don't understand this. How can a table name have column names?
It's a bad explanation - sorry.
A table name per se does not have column names, of course. However, one
possibility that I was considering was having the table_info functions
not report names that cannot be accessed under the current regime (ie,
if DELIMIDENT is not set). However, it is possible to do:
CREATE TABLE nondescript
(
"trouble maker" INT NOT NULL
)
Although the table name does not cause any trouble, using any notation
other than '*' to identify the column name would. So the table_info
functions would have to scrutinize the column information before
deciding whether the table is usable -- even though the table name
itself is OK, the columns that are part of the table would render it
unusable.
>I suspect Jonathan's simply misunderstanding something.
That's another distinct possibility.
>Given $sth->column_info( '%', '%', '%', '%' ); a fetchall_arrayref
>might return something like this (in DataDumper/perl eval format)
>
> [
> [ undef, undef, 'table1name', 'column1name', ... ],
> [ undef, undef, 'table2name', 'col bad nm1', ... ],
> [ undef, undef, 'tbl bad n3', 'column1name', ... ],
> [ undef, undef, 'tbl bad n3', 'col"bad"nm2', ... ],
> ]
>
>running each of those through quote_identifiers (on most DBD's and
>DBD::Informix with DELIMIDENT set) would produce this (in DataDumper/perl
>eval format):
>
> '"table1name"."column1name"'
> '"table2name"."col bad nm1"'
> '"tbl bad n3"."column1name"'
> '"tbl bad n3"."col""bad""nm2"'
>
>Doing the same but either setting $optional param true, or for DBD::Informix
>having DELIMIDENT not set, would produce this:
>
> 'table1name.column1name'
> 'table2name."col bad nm1"'
> '"tbl bad n3".column1name'
> '"tbl bad n3"."col""bad""nm2"'
>
>I hope that helps.
>
>Tim.
>
--
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!"