Re: case sensitivity

On Mon, 29 Jun 2009 16:26:30 +0100, Tim Bunce <[email protected]>
wrote:

> 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.

I must have missed this piece when browsing the docs to find proof.
I *know* why DBD::Oracle fails, and I also understand, but from a user
point of view, this `match' should also be case-insensitive.

> 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).

Reason for our company to tell the customer MYSQL is a no-go
Another reason were reserved fields like 'show' and 'mod' that are no
problem in all other databases and are field and table names in most of
our schemas

-- 
H.Merijn Brand  http://tux.nl      Perl Monger  http://amsterdam.pm.org/
using & porting perl 5.6.2, 5.8.x, 5.10.x, 5.11.x on HP-UX 10.20, 11.00,
11.11, 11.23, and 11.31, OpenSuSE 10.3, 11.0, and 11.1, AIX 5.2 and 5.3.
http://mirrors.develooper.com/hpux/           http://www.test-smoke.org/
http://qa.perl.org      http://www.goldmark.org/jeff/stupid-disclaimers/