Double Quoting in Table names causing dynamic method access failures
am 27.07.2007 18:47:01 von CurtDBI users (second send attempt; sorry if this is a repeat),
For some time now I=92ve been working to transition a =
service from an old
server to a new server.=A0 However, I=92ve been plagued by an error:
[Thu Jul 26 16:16:20 2007] index.cgi: "access" has no primary key at
/usr/lib/perl5/site_perl/5.8.8/Class/DBI/Loader/Generic.pm line 169
I=92m fairly certain the table in question (=93access=94) has a primary =
key, and
to see why, please read the section below titled =93Appendix A=94. =
=A0Finally I've
seen some progress. I've realized that the tables in question have names
that are apparently reserved words in SQL.=A0 The table names that I'm =
having
problems with are named "access", "location", "object", "role", and
"session" (all without the double quotes).=A0 When I call up the PERL =
debugger
and print the array holding the table names I get the following:
..
..
..
73=A0 'public.a_case'
74=A0 'public."access"'
75=A0 'public.additional_allegation'
76=A0 'public.additional_allegation_type'
..
..
..
110=A0 'public.http_user_agent_type'
111=A0 'public."location"'
112=A0 'public.login_attempt'
..
..
..
114=A0 'public.marital_status_of_parents_type'
115=A0 'public."object"'
116=A0 'public.operation'
..
..
..
135=A0 'public.relationship_to_child_type'
136=A0 'public."role"'
137=A0 'public.role_permission'
..
..
..
150=A0 'public.service_area_vicinity_type'
151=A0 'public."session"'
152=A0 'public.site_extra'
..
..
..
When I dump the database from the old server using pg_dump, I get dump =
file
with quotes around these 5 table names. I've tried removing the quotes =
in
the ascii dump file before using psql to rebuild the database on the new
server, but the quotes are always there when printing from the PERL
debugger, and only on these 5 table names. Is there a standard way to =
get
around this?=A0 Changing the table names is one of the last things that =
I want
to do at this time since I'm still learning about the massive program =
that
manipulates them.
The version of PostgreSQL on the old server is:
$ rpm -qa | grep postgresql
postgresql-jdbc-7.4.2-1PGDG
postgresql-tcl-7.4.2-1PGDG
postgresql-contrib-7.4.2-1PGDG
postgresql-7.4.2-1PGDG
postgresql-server-7.4.2-1PGDG
postgresql-python-7.4.2-1PGDG
postgresql-odbc-7.2.4-5.73
postgresql-libs-7.4.2-1PGDG
postgresql-test-7.4.2-1PGDG
postgresql-devel-7.4.2-1PGDG
postgresql-docs-7.4.2-1PGDG
postgresql-pl-7.4.2-1PGDG
And on the new server is:
# rpm -qa | grep postgresql
compat-postgresql-libs-4-2PGDG.fc6
postgresql-libs-8.2.4-1PGDG
postgresql-server-8.2.4-1PGDG
postgresql-8.2.4-1PGDG
Thanks to everyone for your time.
Curt Hesher, President
M.S. Computer Science
CURTECH Computer Services, LLC
www.at-home-computer-support.com
850.980.2571
Curt@CURTECHIT.com
=A0
Specializing in the setup, maintenance, and repair of PCs including =
networks
(LAN/WAN), hardware (modems, video cards, memory, sound cards, SCSI =
cards,
mass storage devices, USB and firewire adapters, routers, network =
interface
cards, monitors, speakers), software (MS Office, MS Windows XP, 2000, =
NT,
ME, 98SE, 98, 95, Easy CD Creator, virus scan software, and many =
others),
tutoring, troubleshooting, data backup and recovery, CD/DVD backup, =
virus
removal, and much more.=A0 I work with you, at your speed, at your =
location,
when ever is most convenient for you to help you succeed with modern
technology.
=A0
Privileged & Confidential.=A0 This email is intended for the named
recipient(s) only and may contain information that is proprietary,
privileged, confidential, or otherwise legally exempt from disclosure. =
If
you are not the named addressee, you are not authorized to read, print,
retain, copy, or disseminate this message or any part of it. Nothing in =
this
email is intended to constitute a waiver of any privilege or the
confidentiality of this message. If you have received this email in =
error,
please notify the sender immediately and delete this message. Thank you.
Appendix A
DBI users,
=A0 I=92ve recently taken on a project of =
moving a large Perl based
application from one server to another.=A0 In the process of setting =
things up
I=92ve come across this error message which I do not know how to =
handle.=A0 Over
the last 48 hours I=92ve checked the mail list archive at google, =
checked the
search engines (google, yahoo, live), read some docs (perldoc DBI, =
perldoc
DBI::Pg), and couldn=92t get the FAQ to load.=A0 If someone could shed =
some
light on this matter I would greatly appreciate it.
=A0
The error message (found in the Apache web server error_log file) is:
=A0
index.cgi: "access" has no primary key at
/usr/lib/perl5/site_perl/5.8.8/Class/DBI/Loader/Generic.pm line 166
=A0
This seems odd since the table =93access=94 does have a primary key as =
evidenced
by the following PostgreSQL psql command:
=A0
=A0
=A0
mydb=3D> \d access
=A0=
Table =
"public.access"
Column =A0 =
| =A0 =
Type |
Modifiers =A0 =
-------------------------+-----------------------------+---- ------------
-------------------------+-----------------------------+----
---------------------------------
=A0id = A0 | =
integer | =
not null default
nextval('access_id_seq'::regclass)
=A0login_attempt_id =A0 | =
integer =A0=A 0 | =
not null
=A0access_time | timestamp without =
time zone |=20
=A0http_user_agent_type_id | =
integer =A0=A 0 | =
not null
=A0ip = A0 | =
inet =A 0 =
|=20
=A0url | =
character varying |=20
Indexes:
=A0 "access_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
=A0 "$1" FOREIGN KEY (login_attempt_id) REFERENCES =
login_attempt(id)
"$2" FOREIGN KEY (http_user_agent_type_id) REFERENCES
http_user_agent_type(id)
=A0
=A0
=A0
Some details about the system that I=92m running on are:
=A0
=A0 # psql --version
psql (PostgreSQL) 8.1.8
contains support for command-line editing
=A0
=A0
# perldoc perllocal
Thu Apr 12 09:58:38 2007: "Module" DBI
=A0
· "installed into: =
/usr/lib/perl5/site_perl/5.8.8"
=A0
· "LINKTYPE: dynamic"
=A0
· "VERSION: 1.54"
=A0
· "EXE_FILES: dbiproxy dbiprof"
=A0
=A0
# perldoc perllocal
Thu Apr 12 12:18:50 2007: "Module" DBD::Pg
=A0
· "installed into: =
/usr/lib/perl5/site_perl/5.8.8"
=A0
· "LINKTYPE: dynamic"
=A0
· "VERSION: 1.49"
=A0
· "EXE_FILES: "
=A0
=A0
Thank you for your time.