[unixODBC-support] Oracle's Heteregenous Services via UnixODBC

Nick Gorham nick.gorham at easysoft.com
Tue Jan 30 09:28:15 GMT 2007

Marcin Stępnicki wrote:

> Hello.
> My issue is only remotely related to UnixODBC, but you seem to be my
> only hope. I've described my problem before at psql-sql
> (http://www.mail-archive.com/pgsql-sql@postgresql.org/msg23257.html ,
> please note the answers) and at Oracle's Metalink (without any
> responses), but there it goes again:
> I've set up connection from oracle to postgresql via unixodbc and 
> oracle's
> heterogeneus services. The connection works, but when I issue the
> following:
> select "p_nr_pesel" from "ZEW_PATIENTS"@my_postgresql where
> "p_patient_id"=19300;
> I see this in postgresql logs:
> [29391]  DEBUG:  query: select * from "zew_patients"
> [29391]  DEBUG:  query: select u.usename, c.relname, a.attname,
> a.atttypid, t.typname, a.attnum, a.attlen, a.atttypmod, a.attnotnull,
> c.relhasrules from pg_user u, pg_class c, pg_attribute a, pg_type t
> where u.usesysid = c.relowner and c.oid= a.attrelid and a.atttypid =
> t.oid and (a.attnum > 0) and c.relname like 'ZEW_PATIENTS' order by
> attnum
> [29391]  DEBUG:  query:  SELECT "A1"."p_nr_pesel" FROM "ZEW_PATIENTS"
> "A1" WHERE ("A1"."p_patient_id" = 19300)
> The first query, according to psql-sql thread, is a very bad way to
> get the columns' names.
> It slows down the whole operation for obvious reasons. When I issue it 
> for the
> second time in the same session, I see only the third select (that's how
> it should be from my point of view). After reconnecting I see
> everything again :(.

Unfortunatly thats what the Oracle HS interface does, its not down to 
the driver, its down to Oracle. And I guess if postgres takes some time 
to do that, then its a unfortunate combination.

If you are prepared to write some more complex PSQL it is possible to 
take more direct control of what happens via the interface, but I don't 
know if it allows you to avoid the column discovery.

Nick Gorham
Easysoft Limited
http://www.easysoft.com, http://www.unixODBC.org

More information about the unixODBC-support mailing list