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().
> > 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').
For DBD::Informix I think it _should_ blindly quote the name _if_
DELIMIDENT env var was set at connect time.
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.
> > 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?
I suspect Jonathan's simply misunderstanding something.
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.
