On Mon, Jan 21, 2002 at 11:17:36AM -0800, Jonathan Leffler wrote:
> 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.
True. So you should be using the correct case. (Which table_info,
and thus tables, will give you.)
> 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.
Depends on the nature of the portable application and where the
table names came from.
> >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.
The only automatic quoting the DBI's currently going to do is just in
tables() where, if quoting wasn't used, the application would break if
it used a name that needed quoting.
> >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.
We're not talking about any rewriting of SQL here.
> 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.
The argument with quote_identifier is that something like this:
$sth = $dbh->table_info(undef, "%", "%", "TABLE");
while ( my ($c, $s, $t) = $sth->fetchrow_array ) {
my $table = $dbh->quote_identifier($c, $s, $t);
my $count = $dbh->selectrow_array("select count(*) from $table");
print "Table $_ has $count rows\n";
}
should work reliably. And, by extension, so should this:
foreach ($dbh->tables) {
my $count = $dbh->selectrow_array("select count(*) from $_");
print "Table $_ has $count rows\n";
}
> 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
I must be missing something here. Can you express the issue in terms
of the two examples I've given above?
> AutoCommit is the other prime example.
If there's a problem with the (recently added) $dbh->begin_work method
then please feel free to start up a new thread.
Tim.
> >> > 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!"
>
