Tim Bunce wrote: > > On Sun, Mar 03, 2002 at 12:41:54PM -0800, Jeff Zucker wrote: > > > > $dbh->do(q/ CREATE TABLE x ( OOg INT, "BOOg" CHAR ) /); > > > > $sth=$dbh->prepare(q/ SELECT boog FROM x /); #4 NO SUCH COLUMN > > $sth=$dbh->prepare(q/ SELECT BOOg FROM x /); #5 NO SUCH COLUMN > > > I think maybe #5 would work for a system where > SQL_IDENTIFIER_CASE = SQL_IC_MIXED > I'm not sure. And you could argue that #4 would work as well
Hmm, I just checked the SQL92 standard and if I'm reading it correctly the situation is ugly. Section 5.2.13 appears to say that a regular identifier and a delimited identifier are equivalent if the *upper case* version of the regular identifier compares *in a case sensitive manor* to the delimited identifier. So this would be the way it should work according to the standard $dbh->do(q/ CREATE TABLE x ( "BOOG" CHAR ) /); $sth=$dbh->prepare(q/ SELECT BooG FROM x /); # OK $sth=$dbh->prepare(q/ SELECT boog FROM x /); # OK $dbh->do(q/ CREATE TABLE x ( "BooG" CHAR ) /); $sth=$dbh->prepare(q/ SELECT BOOG FROM x /); # NO SUCH COLUMN $sth=$dbh->prepare(q/ SELECT BooG FROM x /); # NO SUCH COLUMN $sth=$dbh->prepare(q/ SELECT boog FROM x /); # NO SUCH COLUMN That's a little too wierd for me. It would mean that only delimited identifiers that are all upper case can be compared to regular identifiers. Unless it seems a gross violation, I think I will keep the behaviour that regular identifiers are never equivalent to delimited identifiers. -- if it's quoted then you must use quotes to refer to it. > So it defaults to lowercasing unquoted ids (SQL_IC_LOWER) like > Oracle defaults to uppercasing them (SQL_IC_UPPER). Personally I > think it makes sense to default to one or the other. Doing mixed > case matching always feels weird to me. But maybe I've not spent > enough time on Windows/DOS boxes :) I guess I am going to stick with mixed case for the simple reason that there is already a broad user base of DBD::CSV users who have numerous scripts which would break if I change that behavior. Though for portability, they'd be wise to not depend on that. > A driver that conforms strictly to SQL-92 should return: > SQL_IDENTIFIER_CASE = anything *except* SQL_IC_SENSITIVE > SQL_QUOTED_IDENTIFIER_CASE = SQL_IC_SENSITIVE Yep, I had my eye on that. > And don't forget that a key feature of quoting an identifier is that > it can then include whitespace and other 'special' characters. Yep, and "" to represent " inside "...". And can start with numbers or be the same as reserved word e.g. this is legal: q/ SELECT "SELECT" FROM "FROM","WHERE" WHERE ... / -- Jeff "Jeff"
