Re: case sensitivity

On Mon, Jun 29, 2009 at 04:06:23PM +0200, H.Merijn Brand wrote:
> No database is perfect, but Oracle keeps causing massive hate
> 
> $ cat test.pl
> #!/pro/bin/perl
> 
> use strict;
> use warnings;
> 
> use DBI;
> use Data::Peek;
> 
> my $dbh = DBI->connect ("dbi:Oracle:", (split m{/} => $ENV{DBUSER}), {
>     AutoCommit          => 1,
>     RaiseError          => 1,
>     PrintError          => 1,
>     ChopBlanks          => 1,
>     ShowErrorStatement  => 1,
>     FetchHashKeyName    => "NAME_lc",
>     });
> 
> $dbh->do ("create table foo (c_foo numeric (4) not null primary key)");
> $dbh->do ("insert into foo values (1)");
> 
> DDumper [ "foo", $dbh->primary_key (undef, "PROLEP", "foo") ];
> DDumper [ "FOO", $dbh->primary_key (undef, "PROLEP", "FOO") ];
> 
> $dbh->do ("drop table foo");
> $ perl test.pl
> $VAR1 = [
>     'foo'
>     ];

i.e. it didn't find a table called 'foo'

> $VAR1 = [
>     'FOO',
>     'C_FOO'
>     ];
> $

but it did find a table called 'FOO'.

I suspect that's the right behaviour. The 'Catalog Methods' section of
the DBI docs says:

    Most arguments in the catalog methods accept only ordinary values, e.g.
    the arguments of "primary_key_info()".  Such arguments are treated as a
    literal string, i.e. the *case is significant* and quote characters are
    taken literally.

Oracle is one of those databases that uppercases (unquoted) names.
That's perfectly valid behaviour - though it can be a major pain.

> I found out last week that MySQL is even worse, as it prohibits the use
> of a space before a paren in aggregate functions. But that is not on
> topic here.

True, though I believe there's a config option to control that
(a trade-off with some other feature that I don't now recall).

Tim.