Re: [Zope] duplicate column name
On Thu, 7 Dec 2000, Olaf Zanger wrote: > > > Ivan Cornell schrieb: > > > > Olaf Zanger wrote: > > > > > i work with postgreSQL 7.0.2, ZPyGreSQLDA-0-0-3 and zope 2.2.2 on suse > > > 7.0 linux > > > > > > when i try the line > > > > > > -> select adr.id,fac.id from fac, adr > > > > > > > You probably need to alias the columns: > > select adr.id as adr_id, fac.id as fac_id from fac, adr > > and then refer to adr_id & fac_id from zope. > > > > [can't remember why, but think it is due to the way columns are addressed > > in libpq] > > thanks, > worked out fine, gets a bit tricky though :-) > as visible further down it is not needed in "where" and "order by" > clauses *ç%&?! > > select > fac.id as fac_id, > adr.id as adr_id, > fac.name as fac_name, > adr.id as adr_id, > owner_adr_id, > > from > fac,adr > > where > fac.name like '%' > and adr.id=owner_adr_id > and fac.mod_id=mod.id > > order by > fac.name I suspect a lot of databases are like this, and it depends on implementation of the DA. As an example, the original ZMySQLDA (circa 1.1.3), using MySQLmodule-1.4, probably returned all column names as table.column. Versions of ZMySQLDA that use MySQLdb (i.e. the patched 1.1.3, 1.2, and 2.0.x) use just the column name. adr.id and fac.id have the same column name, of course. I'm a little surprised that some DAs would return column names with periods in them, since this would require using the special namespace object to get at them, i.e. _['adr.id']. The WHERE and ORDER BY clauses can use table.column references without any problem. The issue is the names of the columns returned by a SELECT. -- andy dustman | programmer | comstar.net is part of the Globix network telephone: 770.485.6025 / 706.549.7689 | icq: 32922760 | pgp: 0xc72f3f1d "Therefore, sweet knights, if you may doubt your strength or courage, come no further, for death awaits you all, with nasty, big, pointy teeth!" ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] duplicate column name
Ivan Cornell schrieb: > > Olaf Zanger wrote: > > > i work with postgreSQL 7.0.2, ZPyGreSQLDA-0-0-3 and zope 2.2.2 on suse > > 7.0 linux > > > > when i try the line > > > > -> select adr.id,fac.id from fac, adr > > > > You probably need to alias the columns: > select adr.id as adr_id, fac.id as fac_id from fac, adr > and then refer to adr_id & fac_id from zope. > > [can't remember why, but think it is due to the way columns are addressed > in libpq] thanks, worked out fine, gets a bit tricky though :-) as visible further down it is not needed in "where" and "order by" clauses *ç%&?! select fac.id as fac_id, adr.id as adr_id, fac.name as fac_name, adr.id as adr_id, owner_adr_id, from fac,adr where fac.name like '%' and adr.id=owner_adr_id and fac.mod_id=mod.id order by fac.name -- soli-con Engineering Zanger Dipl.-Ing. (FH) Olaf Zanger Nusch Lorrainestrasse 23 3013 Bern / Switzerland Fon: +41-31-332 9782 Mob: +41-76-572 9782 mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] http://www.soli-con.com begin:vcard n:Zanger;Olaf Marc tel;cell:+41-76-572 9782 tel;work:+41-31-332 9782 x-mozilla-html:FALSE url:www.soli-con.com org:soli-con Engineering Zanger adr:;;Lorrainestrasse 23;Bern;BE;3013;Switzerland version:2.1 email;internet:[EMAIL PROTECTED] title:Dipl.-Ing. note;quoted-printable:IT-Consulting=0D=0AEmbedded Systems=0D=0AEnergy Systems=0D=0AOpen Source Solutions=0D=0A x-mozilla-cpt:;-32176 fn:Olaf Zanger end:vcard
Re: [Zope] duplicate column name
> I believe Zope (not PostgreSQL) needs different names for columns. Otherwise, how >would it choose the name of DTML variables? Try renaming the columns with AS in the >ZSQL method. well zope uses the "name" option and a "column" option for the dtml-sqltest. that works quite well, if your variable name is different from your database-column name. thanks anyway olaf -- soli-con Engineering Zanger Dipl.-Ing. (FH) Olaf Zanger Nusch Lorrainestrasse 23 3013 Bern / Switzerland Fon: +41-31-332 9782 Mob: +41-76-572 9782 mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] http://www.soli-con.com begin:vcard n:Zanger;Olaf Marc tel;cell:+41-76-572 9782 tel;work:+41-31-332 9782 x-mozilla-html:FALSE url:www.soli-con.com org:soli-con Engineering Zanger adr:;;Lorrainestrasse 23;Bern;BE;3013;Switzerland version:2.1 email;internet:[EMAIL PROTECTED] title:Dipl.-Ing. note;quoted-printable:IT-Consulting=0D=0AEmbedded Systems=0D=0AEnergy Systems=0D=0AOpen Source Solutions=0D=0A x-mozilla-cpt:;-32176 fn:Olaf Zanger end:vcard
Re: [Zope] duplicate column name
Olaf Zanger wrote: > i work with postgreSQL 7.0.2, ZPyGreSQLDA-0-0-3 and zope 2.2.2 on suse > 7.0 linux > > when i try the line > > -> select adr.id,fac.id from fac, adr > You probably need to alias the columns: select adr.id as adr_id, fac.id as fac_id from fac, adr and then refer to adr_id & fac_id from zope. [can't remember why, but think it is due to the way columns are addressed in libpq] Ivan ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] duplicate column name
On Thursday 7 December 2000, at 13 h 15, the keyboard of Olaf Zanger <[EMAIL PROTECTED]> wrote: > -> select adr.id,fac.id from fac, adr It is legal SQL but > ->error type: value error > ->error value: duplicate column name,id I believe Zope (not PostgreSQL) needs different names for columns. Otherwise, how would it choose the name of DTML variables? Try renaming the columns with AS in the ZSQL method. ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
[Zope] duplicate column name
i there, i work with postgreSQL 7.0.2, ZPyGreSQLDA-0-0-3 and zope 2.2.2 on suse 7.0 linux when i try the line -> select adr.id,fac.id from fac, adr in the database connection test field i get an error as follows: ->error type: value error ->error value: duplicate column name,id it worked for me until now, because i used different names everywhere. on psql it's no problem did i just screw up some settings or is it ZPyGreSQL? olaf -- soli-con Engineering Zanger Dipl.-Ing. (FH) Olaf Zanger Nusch Lorrainestrasse 23 3013 Bern / Switzerland Fon: +41-31-332 9782 Mob: +41-76-572 9782 mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] http://www.soli-con.com begin:vcard n:Zanger;Olaf Marc tel;cell:+41-76-572 9782 tel;work:+41-31-332 9782 x-mozilla-html:FALSE url:www.soli-con.com org:soli-con Engineering Zanger adr:;;Lorrainestrasse 23;Bern;BE;3013;Switzerland version:2.1 email;internet:[EMAIL PROTECTED] title:Dipl.-Ing. note;quoted-printable:IT-Consulting=0D=0AEmbedded Systems=0D=0AEnergy Systems=0D=0AOpen Source Solutions=0D=0A x-mozilla-cpt:;-32176 fn:Olaf Zanger end:vcard
