Mixed-case column names breaking under Class::DBI

sintadil has asked for the wisdom of the Perl Monks concerning the following question:

So, I've been investigating Class::DBI as one possible tool that I'd like to use as part of a project that I'm developing. In order to try to learn it, I've been trying to let it work on a very simple existing PostgreSQL database that I have -- in this case, it holds authentication information for my PureFTPd FTP daemon. Now, all of my columns in this database contain an initial capital letter. I set this up long ago when I was a wee newbie with databases (well, okay -- more of a newbie than I am now) and I see that The Real World (not to be confused with the TV show by the same name) uses column names which are all lower-case.

This'd be fine if C::D didn't choke on these input fields. I've tried quoting the fields in every single way possible -- '' surrounded by "", "" surrounded by '', `` surorunded by '', and `` surrounded by ''. Obviously, I can't do `` surrounding anything (well, I could, but it'd be ugly, and gives the errors given below). With every quoting method, I get a different error. Here's some sample code ¹ and the error that it generates:

#!/usr/local/bin/perl
use strict;
use warnings;

use base qw/ Class::DBI /;

my $dbname = "pure-ftpd"; my $user = "pure-ftpd";
my $pass = q(elided);
my $host = "adjunct"; my $port = "5432";
main->connection("dbi:Pg:dbname=$dbname;host=$host;port=$port", 
$user, $pass, {FetchHashKeyName => 'NAME'});
# As pointed out by perrin, I tried with and 
# without the FetchHashKeyName flag.
main->table('users');
main->columns(Primary => qw/ "User" /);
main->columns(Others => qw/ "Password" "Uid" "Gid" "Dir"
"ULBandwidth" "DLBandwidth" /);
my $new = main->create
(
 {
  '"User"' => 'test',
  '"Password"' => 'foobarbaz',
  '"Uid"' => 5000,
  '"Gid"' => 5000,
  '"Dir"' => '/nonexistent',
  '"ULBandwidth"' => 5,
  '"DLBandwidth"' => 10
 }
);
[download]

This returns the following when run:

Can't insert new main: DBD::Pg::st execute failed: ERROR:  column "gid
+" of relation "users" does not exist [for Statement "INSERT INTO user
+s ("gid", "dir", "ulbandwidth", "dlbandwidth", "uid", "user", "passwo
+rd")
VALUES (?, ?, ?, ?, ?, ?, ?)
"] at /usr/local/lib/perl5/site_perl/5.8.5/DBIx/ContextualFetch.pm lin
+e 51.
 at /usr/local/lib/perl5/site_perl/5.8.5/Class/DBI.pm line 233
        Class::DBI::_croak('main=HASH(0x82edf90)', 'Can\'t insert new 
+main: DBD::Pg::st execute failed: ERROR:  c...', 'err', 'DBD::Pg::st 
+execute failed: ERROR:  column "gid" of relation ...', 'method', 'cre
+ate') called at /usr/local/lib/perl5/site_perl/5.8.5/Class/DBI.pm lin
+e 644
        Class::DBI::_insert_row('main=HASH(0x82edf90)', 'HASH(0x82f38a
+8)') called at /usr/local/lib/perl5/site_perl/5.8.5/Class/DBI.pm line
+ 590
        Class::DBI::_create('main', 'HASH(0x82e6b6c)') called at /usr/
+local/lib/perl5/site_perl/5.8.5/Class/DBI.pm line 470
        Class::DBI::create('main', 'HASH(0x82e6998)') called at code/p
+erl/playground/dbi/class1 line 13
[download]

Removing the double quotes around the column names yields this:

Can't insert new main: DBD::Pg::st execute failed: ERROR:  syntax erro
+r at or near "user" at character 61 [for Statement "INSERT INTO users
+ (ulbandwidth, password, dlbandwidth, uid, user, gid, dir)
VALUES (?, ?, ?, ?, ?, ?, ?)
"] at /usr/local/lib/perl5/site_perl/5.8.5/DBIx/ContextualFetch.pm lin
+e 51.
 at /usr/local/lib/perl5/site_perl/5.8.5/Class/DBI.pm line 233
        Class::DBI::_croak('main=HASH(0x82edf90)', 'Can\'t insert new 
+main: DBD::Pg::st execute failed: ERROR:  s...', 'err', 'DBD::Pg::st 
+execute failed: ERROR:  syntax error at or near "...', 'method', 'cre
+ate') called at /usr/local/lib/perl5/site_perl/5.8.5/Class/DBI.pm lin
+e 644
        Class::DBI::_insert_row('main=HASH(0x82edf90)', 'HASH(0x82f38a
+8)') called at /usr/local/lib/perl5/site_perl/5.8.5/Class/DBI.pm line
+ 590
        Class::DBI::_create('main', 'HASH(0x82e6b6c)') called at /usr/
+local/lib/perl5/site_perl/5.8.5/Class/DBI.pm line 470
        Class::DBI::create('main', 'HASH(0x82e6998)') called at code/p
+erl/playground/dbi/class1 line 13
[download]

Quoting User gives a column not found error for ulbandwidth as seen above. I'm really quite confused about what the issue could be. Note that, with lowercase column names, it all works nicely. I'd like to be able to use C::D in my work, but I don't want to have to limit myself or change my existing configuration just to appease an API which is supposed to make my work easier. Perl's about making things easier, not harder. :)

As a side note, I think that it's interesting that this is a known problem at least for Sybase databases. I wonder if what's breaking it under Sybase is also at work here.

----

1: I've just shoved everything in package main for the purpose of brevity. I don't see how this could affect it in such a way that it fails to find a column name because of a missed capital letter.

Comment on Mixed-case column names breaking under Class::DBI Select or Download Code

Replies are listed 'Best First'.
Re: Mixed-case column names breaking under Class::DBI by perrin (Chancellor) on Sep 20, 2004 at 20:19 UTC
The problem is that you are changing a DBI parameter that Class::DBI depends on, namely `FetchHashKeyName => 'NAME_lc'`. Remove that, and it should work.	[reply] [d/l]
Re^2: Mixed-case column names breaking under Class::DBI by sintadil (Pilgrim) on Sep 20, 2004 at 20:25 UTC
The problem is that you are changing a DBI parameter that Class::DBI depends on, namely `FetchHashKeyName => 'NAME_lc'`. Remove that, and it should work. Doh. I knew that I forgot something in my original post. I forgot to mention that I tried all of the quoting methods before adding that, though I'm off to try them again. Thanks for reminding me.	[reply] [d/l]
Re: Mixed-case column names breaking under Class::DBI by jZed (Prior) on Sep 20, 2004 at 21:26 UTC
As far as I can see, the only problem is that your column name "user" is a SQL reserved keyword. Change the name of that column (in the database and in the code), remove all of the quotes around all of the column names, and everything should work, it does for me.	[reply]
Re^2: Mixed-case column names breaking under Class::DBI by sintadil (Pilgrim) on Sep 20, 2004 at 21:38 UTC
As far as I can see, the only problem is that your column name "user" is a SQL reserved keyword. Change the name of that column (in the database and in the code), remove all of the quotes around all of the column names, and everything should work, it does for me. Okay, bad column choice on my part. However, my FTP server doesn't have a problem with it because it quotes the column name. That functionality is what I'm trying to accomplish with the quoting.	[reply]
Re^3: Mixed-case column names breaking under Class::DBI by Aristotle (Chancellor) on Sep 20, 2004 at 21:42 UTC
Sounds like you should file a bug asking for Class::DBI to quote column names. Makeshifts last the longest.	[reply]
Re^4: Mixed-case column names breaking under Class::DBI by sintadil (Pilgrim) on Sep 20, 2004 at 21:45 UTC
Re: Mixed-case column names breaking under Class::DBI by Your Mother (Archbishop) on Sep 21, 2004 at 02:20 UTC
Are you using a sequence on it? Alla `__PACKAGE__->sequence('cd_id_seq')`. I think you need to do that for Pg but I could be wrong. You also might give Class::DBI::Pg (has sample code for non-CDBI::Pg use too) a try as the base instead, just to see if it makes any difference.	[reply] [d/l]
Re^2: Mixed-case column names breaking under Class::DBI by sintadil (Pilgrim) on Sep 21, 2004 at 02:31 UTC
Are you using a sequence on it? Alla `__PACKAGE__->sequence('cd_id_seq')`. I think you need to do that for Pg but I could be wrong. Good observation, but I'm not using any such data type in the particular table that I tried testing. You also might give Class::DBI::Pg (has sample code for non-CDBI::Pg use too) a try as the base instead, just to see if it makes any difference. Yet another good suggestion, but it results in the same syntax error as before because it's not properly quoting the User column.	[reply] [d/l]
Re: Mixed-case column names breaking under Class::DBI by sintadil (Pilgrim) on Sep 20, 2004 at 21:47 UTC
I'd like to thank perrin, jZed, and Aristotle for their advice on this issue. As per Aristotle's suggestion, I'm off to file a bug.	[reply]
Re^2: Mixed-case column names breaking under Class::DBI by perrin (Chancellor) on Sep 21, 2004 at 02:42 UTC
Rather than file a bug, I'd suggest you post it to the Class::DBI mailing list. Most people get their problems solved that way. Also, the complaint about not finding column "gid" makes me think that you might need to tell Postgres which tablespace or schema you are working in. It may be trying to use an internal table called "users" in the main Postgres tablespace.	[reply]
Re^3: Mixed-case column names breaking under Class::DBI by sintadil (Pilgrim) on Sep 21, 2004 at 03:18 UTC
Rather than file a bug, I'd suggest you post it to the Class::DBI mailing list. Most people get their problems solved that way. I'll keep the mailing list vs. bug report idea in mind. Speaking of the list, I just recently found a relevant thread covering my issue. It seems to be a case of a lack of tuits. I'll take another spin in the debugger to see if I can't find exactly where the problem is. If I can, I'll be submitting a patch. Also, the complaint about not finding column "gid" makes me think that you might need to tell Postgres which tablespace or schema you are working in. It may be trying to use an internal table called "users" in the main Postgres tablespace. That -- as was determined in another part of this thread -- was caused by incorrect quoting issues.	[reply]


Come for the quick hacks, stay for the epiphanies.
	PerlMonks