On Mon, 29 Jun 2009 16:26:30 +0100, Tim Bunce <tim.bu...@pobox.com>
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/
