Thread: Possible ODBC driver error
I recently copied five tables to the server using the Postgres ODBC driver with MS Access. The tables can be viewed in MS Access but not using PHP or Zeos Database Explorer, which both return the same error message: ERROR: Relation 'adweb_servinf1' does not exist The tables are visible in ZDO and the column names can be viewed. All the names of the tables also appear in pg_tables. Is this a bug in the ODBC driver i.e. it does not make the full entries needed in Postgres for it to know about tables? -- ======================================================================= Patrick Dunford, Christchurch, NZ - http://pdunford.godzone.net.nz/ But Christ has indeed been raised from the dead, the firstfruits of those who have fallen asleep. -- 1 Corinthians 15:20 http://www.heartlight.org/cgi-shl/todaysverse.cgi?day=20010415 ======================================================================= Created by Mail2Sig - http://pdunford.godzone.net.nz/software/mail2sig/
On Mon, 16 Apr 2001 17:18:02 +1200 AD in comp.databases.postgresql.general, Patrick Dunford said: >I recently copied five tables to the server using the Postgres ODBC driver >with MS Access. > >The tables can be viewed in MS Access but not using PHP or Zeos Database >Explorer, which both return the same error message: >ERROR: Relation 'adweb_servinf1' does not exist > >The tables are visible in ZDO and the column names can be viewed. All the >names of the tables also appear in pg_tables. > >Is this a bug in the ODBC driver i.e. it does not make the full entries >needed in Postgres for it to know about tables? The problem appears to be (as my sysadmin pointed out) that the ODBC driver allows mixed cased table names to be created. All other table names created via other means are full lowercase. Apparently table names are case sensitive, and Postgres assumes the table name will be stored as all lowercase. -- ======================================================================= Patrick Dunford, Christchurch, NZ - http://pdunford.godzone.net.nz/ But Christ has indeed been raised from the dead, the firstfruits of those who have fallen asleep. -- 1 Corinthians 15:20 http://www.heartlight.org/cgi-shl/todaysverse.cgi?day=20010415 ======================================================================= Created by Mail2Sig - http://pdunford.godzone.net.nz/software/mail2sig/
On Mon, 16 Apr 2001 17:21:16 +1200 AD in comp.databases.postgresql.bugs, Patrick Dunford said: >On Mon, 16 Apr 2001 17:18:02 +1200 AD in comp.databases.postgresql.general, >Patrick Dunford said: > >>I recently copied five tables to the server using the Postgres ODBC driver >>with MS Access. >> >>The tables can be viewed in MS Access but not using PHP or Zeos Database >>Explorer, which both return the same error message: >>ERROR: Relation 'adweb_servinf1' does not exist >> >>The tables are visible in ZDO and the column names can be viewed. All the >>names of the tables also appear in pg_tables. >> >>Is this a bug in the ODBC driver i.e. it does not make the full entries >>needed in Postgres for it to know about tables? > >The problem appears to be (as my sysadmin pointed out) that the ODBC driver >allows mixed cased table names to be created. All other table names created >via other means are full lowercase. Apparently table names are case >sensitive, and Postgres assumes the table name will be stored as all >lowercase. Also appears to affect other related things such as column names. -- ======================================================================= Patrick Dunford, Christchurch, NZ - http://pdunford.godzone.net.nz/ But Christ has indeed been raised from the dead, the firstfruits of those who have fallen asleep. -- 1 Corinthians 15:20 http://www.heartlight.org/cgi-shl/todaysverse.cgi?day=20010415 ======================================================================= Created by Mail2Sig - http://pdunford.godzone.net.nz/software/mail2sig/
Here's one possibility... Were the names mixed case? If so, the copied tables probably were created with their names quoted and you'll need to quote the names to reference them. Postgres lowercases unquoted names (for case-insensitivity), so if you create a table with something like: create table "A" ... the only way to reference the table is as "A" not A. On Mon, 16 Apr 2001, Patrick Dunford wrote: > I recently copied five tables to the server using the Postgres ODBC driver > with MS Access. > > The tables can be viewed in MS Access but not using PHP or Zeos Database > Explorer, which both return the same error message: > ERROR: Relation 'adweb_servinf1' does not exist > > The tables are visible in ZDO and the column names can be viewed. All the > names of the tables also appear in pg_tables. > > Is this a bug in the ODBC driver i.e. it does not make the full entries > needed in Postgres for it to know about tables?
On Mon, 16 Apr 2001, Stephan Szabo wrote: > On Mon, 16 Apr 2001, Patrick Dunford wrote: > > > I recently copied five tables to the server using the Postgres ODBC driver > > with MS Access. > > > > The tables can be viewed in MS Access but not using PHP or Zeos Database > > Explorer, which both return the same error message: > > ERROR: Relation 'adweb_servinf1' does not exist > > > > The tables are visible in ZDO and the column names can be viewed. All the > > names of the tables also appear in pg_tables. > > > > Is this a bug in the ODBC driver i.e. it does not make the full entries > > needed in Postgres for it to know about tables? Are you using PHP w/ODBC, or w/the compiled-in Postgres DB connector? If you don't see it w/the built-in connector, it's not a ODBC problem at all. When you say the "tables can be viewed" do you mean that the data in the tables can be viewed in Access, but you can't view the data of tables in PHP/Zeos? Can you create a new, simple table, insert one record, and view this through PHP/Zeos? -- Joel Burton <jburton@scw.org> Director of Information Systems, Support Center of Washington
On Mon, 16 Apr 2001 15:12:39 +0000 (UTC) AD in comp.databases.postgresql.general, Stephan Szabo said: > >Here's one possibility... >Were the names mixed case? If so, the copied tables probably >were created with their names quoted and you'll need to quote >the names to reference them. Postgres lowercases unquoted >names (for case-insensitivity), so if you create a table with >something like: >create table "A" ... >the only way to reference the table is as "A" not A. Yes this is so maybe the driver needs an option that gives us a choice (convert table and field names when making a table), since it's fiddly doing it by hand > > >On Mon, 16 Apr 2001, Patrick Dunford wrote: > >> I recently copied five tables to the server using the Postgres ODBC driver >> with MS Access. >> >> The tables can be viewed in MS Access but not using PHP or Zeos Database >> Explorer, which both return the same error message: >> ERROR: Relation 'adweb_servinf1' does not exist >> >> The tables are visible in ZDO and the column names can be viewed. All the >> names of the tables also appear in pg_tables. >> >> Is this a bug in the ODBC driver i.e. it does not make the full entries >> needed in Postgres for it to know about tables? > > >---------------------------(end of broadcast)--------------------------- >TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/users-lounge/docs/faq.html > -- ======================================================================= Patrick Dunford, Christchurch, NZ - http://pdunford.godzone.net.nz/ Then the end will come, when he hands over the kingdom to God the Father after he has destroyed all dominion, authority and power. -- 1 Corinthians 15:24 http://www.heartlight.org/cgi-shl/todaysverse.cgi?day=20010416 ======================================================================= Created by Mail2Sig - http://pdunford.godzone.net.nz/software/mail2sig/