On Thu, 10 Jan 2002, Simon Oliver wrote:
>Jonathan Leffler wrote:
>> I don't see how quote_identifier helps a user of DBI write more
>> portable code.
>
>Suppose I'm writing a cross DBMS system that needs to interact with
>various (unkown at compile time) database objects. Suppose I want to
>display a list of objects in the database, perhaps I call
>$dbh->table_info to get this, easy. But when I want to retieve the
>contents of one of those objects I need to issue a SELECT statement on
>a fully qualified object name - I need to build an identifier for the
>FROM clause.
I thought that table_info was supposed to return the data in a usable
format, so that you didn't need to do more than replace one of the items
from table_info (or maybe the dot-separated concatenation of some
sequence of non-empty items) into the FROM clause. If table_info
doesn't give you the data in a usable format, isn't that definition
broken? And table_info is where DBD::Informix does (or, rather, should
do) some filtering so that if Informix's DELIMIDENT is not set, then the
unusable names are not revealed to the unsuspecting DBI program. If
table_info does not already return a value that matches 'reference to
table valid for use in FROM clause' then maybe it should?
Are there any other portable methods for obtaining table (and column)
names? I've not looked sufficiently hard at the DBI specification for
sufficiently long that I could be all out of whack here (and the current
release of DBD::Informix dates back to March 2000).
>Now all I need to do is call quote_identifier with the appropriate
>identifiers and the method will return an appropriate complex
>identifier quoted according to the rules of the underlying DBD or DBMS.
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.
What concerns me is 'what should a DBD module do if it is asked to do
quote_identifier on an identifier that cannot be handled' -- what are
the error semantics? I have a database which I deliberately constructed
with some table names that cannot be accessed unless the DELIMIDENT
variable is set -- it makes all sorts of things break horribly. 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'
This is valid in Informix and is equivalent to WHERE 'column name with
blanks' > 'abc' and is not what the user intended if "column name with
blanks" is really the name of a column. So, leaving the thing unquoted
will cause a syntax error, in this case. But what about the table I
really do have in my database with the name '"utter bastard"' which
needs to be referenced as """utter bastard""". If I don't quote that, I
run into problems -- if I do quote it, we run into different problems.
Ugh!
I'm still not sure that if table_info and/or tables does not return the
correct syntax (and pointedly ignore anything that cannot be handled
correctly), then you will run into trouble. 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.
--
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!"
