Thread: ODBC Connection to External Data Source

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA

-----Original Message-----
From: Paul R. Sand [mailto:paul_sand@mverify.com]
Sent: Tuesday, January 27, 2004 4:56 PM
To: pgsql-odbc@postgresql.org
Subject: [ODBC] ODBC Connection to External Data Source

I am looking for procedures on how to connect postgres to an external data source (ODBC to Oracle). I then will query the external data source to write into postgres relations. I can’t find information on how to set up the ODBC connection from postgres to Oracle so I can use SQL in postgres to access data in the Oracle database. I have the Oracle ODBC drivers install and can access them with Excel. I need to also access the database from postgres. Any suggestions on where I can find information on how to make this OBDC connection?

 

Thanks!

 

Paul

--- "Paul R. Sand" <paul_sand@mverify.com> wrote:
> I am looking for procedures on how to connect
> postgres to an external data
> source (ODBC to Oracle). I then will query the
> external data source to write
> into postgres relations. I can't find information on
> how to set up the ODBC
> connection from postgres to Oracle so I can use SQL
> in postgres to access
> data in the Oracle database. I have the Oracle ODBC
> drivers install and can
> access them with Excel. I need to also access the
> database from postgres.
> Any suggestions on where I can find information on
> how to make this OBDC
> connection?

There is an experimental package out there which
allows you to query an Oracle database from a
PostgreSQL database, but you probably don't want that
(if you are interested, check the archives of the
pgsql-hackers list).

You really need a client app to do what you want.  The
app would make separate ODBC connections to the two
databases, and transfer data between them as you wish.
 A simple setup would be to install the PostgreSQL
ODBC driver on your workstation
(http://gborg.postgresql.org/project/psqlodbc), set up
a DSN for your PostgreSQL database, then link to the
tables for both databases via a MS Access database.
You could then use SQL within Access to do what you
want.  This could be slow however, depending on your network.

__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free web site building tool. Try it!
http://webhosting.yahoo.com/ps/sb/

I am looking for procedures on how to connect postgres to an external data source (ODBC to Oracle). I then will query the external data source to write into postgres relations. I can't find information on how to set up the ODBC connection from postgres to Oracle so I can use SQL in postgres to access data in the Oracle database. I have the Oracle ODBC drivers install and can access them with Excel. I need to also access the database from postgres. Any suggestions on where I can find information on how to make this OBDC connection?

 

Thanks!

 

Paul

--- "Trewern, Ben" <Ben.Trewern@mowlem.com> wrote:
> What you're looking for is dblink it's in the
> contrib directory of the
> postgres distribution.  Not sure how it works as I
> haven't used it but it
> does have some documentation.
>

dblink allows queries from one PostgreSQL database to
another, but not to a different DBMS.  There is a
version in existence which works with Oracle (not sure
if it is a hacked version of dblink, or a new
attempt), but AFAIK it has not progressed beyond the
alpha stage yet.

> Regards,
>
> Ben
>
>
>  -----Original Message-----
> From: Paul R. Sand [mailto:paul_sand@mverify.com]
> Sent: 27 January 2004 21:56
> To: pgsql-odbc@postgresql.org
> Subject: [ODBC] ODBC Connection to External Data
> Source
>
>
>
> I am looking for procedures on how to connect
> postgres to an external data
> source (ODBC to Oracle). I then will query the
> external data source to write
> into postgres relations. I can't find information on
> how to set up the ODBC
> connection from postgres to Oracle so I can use SQL
> in postgres to access
> data in the Oracle database. I have the Oracle ODBC
> drivers install and can
> access them with Excel. I need to also access the
> database from postgres.
> Any suggestions on where I can find information on
> how to make this OBDC
> connection?
>
>
>
> Thanks!
>
>
>
> Paul
>
>
>
>
*****************************************************************************
> This email and any attachments transmitted with it
> are confidential
> and intended solely for the use of the individual or
> entity to whom
> they are addressed. If you have received this email
> in error please
> notify the sender and do not store, copy or disclose
> the content
> to any other person.
>
> It is the responsibility of the recipient to ensure
> that opening this
> message and/or any of its attachments will not
> adversely affect
> its systems. No responsibility is accepted by the
> Company.
>
*****************************************************************************
>


__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free web site building tool. Try it!
http://webhosting.yahoo.com/ps/sb/