On Sun, Mar 03, 2002 at 12:41:54PM -0800, Jeff Zucker wrote:
> Tim Bunce wrote:
> >
> >
> > Does double-quoting work, and provide case sensitivity (as per the standard)?
>
> Let me just check wavelengths here. Delimited (double quoted)
> identifiers *must* be case sensitive and stored in mixed case.
To be SQL-92 conformant, yes. See below.
> Other
> (non-quoted) identifiers *must not* be case sensitive but can also be
> stored in mixed case (that's left to the implementation).
To be SQL-92 conformant, yes. See below.
> So going that route, this is how I have things working in DBD::CSV:
>
> $dbh->do(q/ CREATE TABLE x ( OOg INT, "BOOg" CHAR ) /);
>
> $sth=$dbh->prepare(q/ SELECT oog FROM x /); #1 OK
> $sth=$dbh->prepare(q/ SELECT "BOOg" FROM x /); #2 OK
> $sth=$dbh->prepare(q/ SELECT "boog" FROM x /); #3 NO SUCH COLUMN
> $sth=$dbh->prepare(q/ SELECT boog FROM x /); #4 NO SUCH COLUMN
> $sth=$dbh->prepare(q/ SELECT BOOg FROM x /); #5 NO SUCH COLUMN
Well that certainly matches what Oracle would do :)
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
on the basis that if the only diference is letter case and if
the identifiers are not case sensitive (ie for matching) then
lettercase only differences don't matter.
> my $sth=$dbh->prepare("SELECT * FROM x");
> print join ',' @{$sth->{NAME}} #6 prints OOg,BOOg
Oracle would print OOG,BOOg because it uppercases unquoted identifiers.
> $sth->fetchrow_hashref->{OOg} # OK
> $sth->fetchrow_hashref->{BOOg} # OK
> $sth->fetchrow_hashref->{oog} # NO SUCH HASH KEY
> $sth->fetchrow_hashref->{boog} # NO SUCH HASH KEY
> $sth->fetchrow_hashref->{q/"BOOg"/} # NO SUCH HASH KEY
>
> Is that how it should work assuming I want to store in mixed case? (all
> this leaving aside aliases).
Yes, "assuming I want to store in mixed case", ie:
SQL_IDENTIFIER_CASE = SQL_IC_MIXED
But of course people should use $h->{FetchHashKey} = 'NAME_lc' or
'NAME_uc' :)
> DBD::Pg differs from the above in that it uses oog and BOOg in the
> {NAME} and hashref, otherwise the same.
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 :)
> DBD::ODBC with Access uses OOg and BOOg in the {NAME} and hashref as
> above, but permits 3,4,5!
4 and 5 are probably okay. But 3 is stretching things a bit! :)
FYI, the relevants parts of the ODBC GetInfo spec are:
SQL_IDENTIFIER_CASE =
SQL_IC_UPPER not case sensitive, stored in uppercase
SQL_IC_LOWER not case sensitive, stored in lowercase
SQL_IC_SENSITIVE case sensitive, stored mixedcase
SQL_IC_MIXED not case sensitive, stored mixedcase
plus the same set again for SQL_QUOTED_IDENTIFIER_CASE.
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
And don't forget that a key feature of quoting an identifier is that
it can then include whitespace and other 'special' characters.
Tim.
