Oracle HSODBC problems

Re: Oracle HSODBC problems

LLC wrote:
> Hi List;
>
> We've setup an oracle HSODBC (Heterogeneous Services) connection to a postgres database using the postgresql ODBC driver. The odbs driver and oracle instance are on a windows box and the Postgres db is on a red hat box. We've specified the db name in the ODBC DSN and in the query but each time we get 'table does not exist'. Based on the postgres logs I see that the database is always 'postgres' . Since postgres is the default db it leads me to believe that the oracle connection is not passing the database name...
>
> Anyone have any experiences similar, know of any work-arounds, etc?

In the ODBC DSN you can specify the database.

Joshua D. Drake


>
> Thanks in advance
>


--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/



---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: Oracle HSODBC problems

On 8/29/06, LLC wrote:
> each time we get 'table does not exist'

In PostgreSQL:
CREATE TABLE test_tbl (test_id NUMERIC(10) NOT NULL PRIMARY KEY,
test_name VARCHAR(32));

In PostgreSQL:
INSERT INTO test_tbl VALUES (1, 'From PostgreSQL');

In Oracle over HSODBC:
INSERT INTO "test_tbl"@dblinkname VALUES (2, 'From Oracle');


--
Jonah H. Harris, Software Architect | phone: 732.331.1300
EnterpriseDB Corporation | fax: 732.331.1301
33 Wood Ave S, 2nd Floor | jharris@enterprisedb.com
Iselin, New Jersey 08830 | http://www.enterprisedb.com/

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: Oracle HSODBC problems

----- Original Message -----


Subject: Re: [ODBC] Oracle HSODBC problems


From: "Jonah H. Harris"


Date: Tue, August 29, 2006 20:11





On 8/29/06, LLC wrote:
> each time we get 'table does not exist'

In PostgreSQL:
CREATE TABLE test_tbl (test_id NUMERIC(10) NOT NULL PRIMARY KEY,
test_name VARCHAR(32));

In PostgreSQL:
INSERT INTO test_tbl VALUES (1, 'From PostgreSQL');

In Oracle over HSODBC:
INSERT INTO "test_tbl"@dblinkname VALUES (2, 'From Oracle');


--
Jonah H. Harris, Software Architect | phone: 732.331.1300
EnterpriseDB Corporation | fax: 732.331.1301
33 Wood Ave S, 2nd Floor | jharris@enterprisedb.com
Iselin, New Jersey 08830 | http://www.enterprisedb.com/

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster




Hi Jonah;

The above works so long as the test_tbl lives in the 'postgres' database
(the default db). If I run the folowing it does not work. Oracle cannot
find the table because it's not connecting to the testdb database. The
postgres logs indicate a connection attempt via the postgres database.

The correct database is specified in the odbs DSN as well, but seems to
have no effect.

Thanks for your help...


In PostgreSQL:
CREATE database testdb;

\c testdb

CREATE TABLE test_tbl (test_id NUMERIC(10) NOT NULL PRIMARY KEY,
test_name VARCHAR(32));

In PostgreSQL:
INSERT INTO testdb.test_tbl VALUES (1, 'From PostgreSQL');

In Oracle over HSODBC:
INSERT INTO "testdb.test_tbl"@dblinkname VALUES (2, 'From Oracle');



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Re: Oracle HSODBC problems

Hi Joshua;


I do have the database name in the ODBC DSN but it seems to have no
effect. In the below samples, sample 1 works and sample 2 does not.

Thanks for your help


Sample 1:
In PostgreSQL:
CREATE TABLE test_tbl (test_id NUMERIC(10) NOT NULL PRIMARY KEY,
test_name VARCHAR(32));

In PostgreSQL:
INSERT INTO test_tbl VALUES (1, 'From PostgreSQL');

In Oracle over HSODBC:
INSERT INTO "test_tbl"@dblinkname VALUES (2, 'From Oracle');

Sample 2:
In PostgreSQL:
CREATE database testdb;

\c testdb

CREATE TABLE test_tbl (test_id NUMERIC(10) NOT NULL PRIMARY KEY,
test_name VARCHAR(32));

In PostgreSQL:
INSERT INTO testdb.test_tbl VALUES (1, 'From PostgreSQL');

In Oracle over HSODBC:
INSERT INTO "testdb.test_tbl"@dblinkname VALUES (2, 'From Oracle');




---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: Oracle HSODBC problems

On 8/30/06, LLC wrote:
> I do have the database name in the ODBC DSN but it seems to have no
> effect. In the below samples, sample 1 works and sample 2 does not.

Hmm... I've gotten it to work before with differing database names. I
don't recall doing anything special to configure it.

--
Jonah H. Harris, Software Architect | phone: 732.331.1300
EnterpriseDB Corporation | fax: 732.331.1301
33 Wood Ave S, 2nd Floor | jharris@enterprisedb.com
Iselin, New Jersey 08830 | http://www.enterprisedb.com/

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match