PostgreSQL Backend as SW Gateway to Oracle

On Wed, Jul 08, 1998 at 09:37:41AM +0200, Dr. Armin Schloesser wrote:

Now after this lengthy introduction my questions:

Just to be sure. You�d like to have a connected table as in M$ Access?

2 Is there anyone outside that will be interested also in this approach.

Yes, me.

3 Are you interested in getting back the new stuff

Of course.

Gru� nach Kassel.

Michael

--
Dr. Michael Meskes meskes@online-club.de, meskes@debian.org
Go SF49ers! Go Rhein Fire! Use Debian GNU/Linux!

(still using the libpg). > > Now after this lengthy introduction my
questions: > > 1 Could you give me more information on how and where to
hook into the backend code to implement this > > stuff. > > 2 Is
there anyone outside that will be interested also in this approach. > >
3 Are you interested in getting back the new stuff > > Thanks a lot for
your patience!! > > Greetings, > > Dr. Armin. Schloesser > >

[See what happens when you send lines >80.]

You want to use libpq to send that through the backend, and then pass it
to Oracle. Some people have asked for this, but I know of know way to
accomplish this. Your best be would be to create a fake libpq, that has
the same function names/behavior, but calls native Oracle C functions.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Hello Bruce,

thanks for your reply.

I already have anticipated that there is no off the shelf solution for my
problem.

A wrapper linpq I dont't want to use, cause then I would have to link
against different stuff coneccting to PostgreSQL and Oracle. I want to
have this switching done not in my application, but in a separate process.

After all the Oracle client SW is not available under Linux.

Nevertheless I would like to implement this Oracle Gateway using the
PostgeSQL stuff.

Now the problem for me is how to get used to the backend architecture as
fast as possible. That means is there any more deeper technical doku
available, describing the backend control flow and architekture.
Especially the layout of list and nodes structure of the parser.

Cause using the Oracle OCI there has to be some preprocessing done to
parse the libpq SQL strings for binding input and output variables to. So
a simple SELECT call is not mappable to a single OCI call.

If there are also other guys outside interested in this approach, it would
be perhaps worth to discuss also their requirements to get a proper
functional spec to implement the SW gateway.

Sorry for the long lines. I used the netscape to generate the mail and he
doen't complain about long lines. I will use the good old pine in the
future.

Greetings,

Dr. Armin. Schloesse

==============================================================================
Philips Automation Projects Phone: +49 561 501 1395
Miramstr. 87 Fax: +49 561 501 1688
34123 Kassel Email: armin@ap-kas.ie.philips.com
Germany
==============================================================================

On Wed, 8 Jul 1998, Bruce Momjian wrote:

On Thu, Jul 09, 1998 at 08:13:48AM +0200, Armin Schloesser wrote:

Now the problem for me is how to get used to the backend architecture as
fast as possible. That means is there any more deeper technical doku
available, describing the backend control flow and architekture.
Especially the layout of list and nodes structure of the parser.

Do you want to implement a special solution for your problem, or add a table
type that is a link to a different table. With ODBC you can do this in M$
Access for instance.

IMO a link to a different databse would be a very nice feature to have. We
could add the code to link an external postgres database as well.

Cause using the Oracle OCI there has to be some preprocessing done to
parse the libpq SQL strings for binding input and output variables to. So
a simple SELECT call is not mappable to a single OCI call.

Yes, there should a an adapter for each different DB system.

If there are also other guys outside interested in this approach, it would
be perhaps worth to discuss also their requirements to get a proper
functional spec to implement the SW gateway.

Interested yes. But I'm afraid I have neither time to work on it, nor enough
inside knowledge. But then I'm working to get more knowledge anyway.

Michael

--
Dr. Michael Meskes meskes@online-club.de, meskes@debian.org
Go SF49ers! Go Rhein Fire! Use Debian GNU/Linux!

Nevertheless I would like to implement this Oracle Gateway using the
PostgeSQL stuff.
Cause using the Oracle OCI there has to be some preprocessing done to
parse the libpq SQL strings for binding input and output variables to.
So a simple SELECT call is not mappable to a single OCI call.
If there are also other guys outside interested in this approach, it
would be perhaps worth to discuss also their requirements to get a
proper functional spec to implement the SW gateway.

I don't have a particular interest in the Oracle gw, but am interested
in getting simultaneous multiple db access within Postgres. I had been
thinking of trying to implement this as a Postgres "master database"
with hooks deeper in the backend to call out to a remote database as a
separate session. Sort of like Ingres implemented their distributed
databases. Haven't done anything with it though...

- Tom

Hello Tom,

exactly what I think should be done first.

Build a clear interface between the parsing and I think the execution step
of the postgres backend to hook in own stuff to interface to an
arbritrary other database or even other implemention of the real
hard word of storing and getting the data.

Greetings,

armin

==============================================================================
Philips Automation Projects Phone: +49 561 501 1395
Miramstr. 87 Fax: +49 561 501 1688
34123 Kassel Email: armin@ap-kas.ie.philips.com
Germany
==============================================================================

On Thu, 9 Jul 1998, Thomas G. Lockhart wrote:

On Thu, 9 Jul 1998, Thomas G. Lockhart wrote:

Nevertheless I would like to implement this Oracle Gateway using the
PostgeSQL stuff.
Cause using the Oracle OCI there has to be some preprocessing done to
parse the libpq SQL strings for binding input and output variables to.
So a simple SELECT call is not mappable to a single OCI call.
If there are also other guys outside interested in this approach, it
would be perhaps worth to discuss also their requirements to get a
proper functional spec to implement the SW gateway.

I don't have a particular interest in the Oracle gw, but am interested
in getting simultaneous multiple db access within Postgres. I had been
thinking of trying to implement this as a Postgres "master database"
with hooks deeper in the backend to call out to a remote database as a
separate session. Sort of like Ingres implemented their distributed
databases. Haven't done anything with it though...

Cewl... wouldn't this enable us to run PostgreSQL on beowolf-like
clusters? :) (for those of you unknown to them, see
http://cesdis.gsfc.nasa.gov/beowulf/consortium/consortium.html, this one
is also very nice :) http://cnls.lanl.gov/avalon/ ).

Maarten

_____________________________________________________________________________
| TU Delft, The Netherlands, Faculty of Information Technology and Systems |
| Department of Electrical Engineering |
| Computer Architecture and Digital Technique section |
| M.Boekhold@et.tudelft.nl |
-----------------------------------------------------------------------------

Hello Bruce,

thanks for your reply.

I already have anticipated that there is no off the shelf solution for my
problem.

A wrapper linpq I dont't want to use, cause then I would have to link
against different stuff coneccting to PostgreSQL and Oracle. I want to
have this switching done not in my application, but in a separate process.

After all the Oracle client SW is not available under Linux.

Nevertheless I would like to implement this Oracle Gateway using the
PostgeSQL stuff.

Now the problem for me is how to get used to the backend architecture as
fast as possible. That means is there any more deeper technical doku
available, describing the backend control flow and architekture.
Especially the layout of list and nodes structure of the parser.

Check the web site documentation. Under developers, there is all the
stuff you should need. Description/flowchart, and developers FAQ.

Cause using the Oracle OCI there has to be some preprocessing done to
parse the libpq SQL strings for binding input and output variables to. So
a simple SELECT call is not mappable to a single OCI call.

You would have to put code into the server to call pass the query and
returned data to oracle. Not easy.

Sorry for the long lines. I used the netscape to generate the mail and he
doen't complain about long lines. I will use the good old pine in the
future.

You can set your netscape window size in your .Xdefaults file.

Netscape.Composition.geometry: =750x650

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

On Wed, 8 Jul 1998, Dr. Armin Schloesser wrote:

Cause there is no Oracle client SW available under Linux and I don't want to change the API in our

programming environment I would like to have a modified PostgreSQL Backend that runs under the above

You can use the Openlink ODBC broker (basically an ODBC proxy). You run
it the broker on a platform that does have an ODBC driver available
(the broker is available for _many_ platforms). Then you use an Openlink
ODBC driver to connect to the broker (there are Openlink ODBC drivers
available for _many_ platforms, including Linux). The broker just
redirects the requests for you.

See www.openlinksw.com Trial versions of this are available. Basically
Openlink offers almost complete "access any database, anywhere" solutions.

Tom

Hi,

Hello Bruce,

thanks for your reply.

I already have anticipated that there is no off the shelf solution for my
problem.

A wrapper linpq I dont't want to use, cause then I would have to link
against different stuff coneccting to PostgreSQL and Oracle. I want to
have this switching done not in my application, but in a separate process.

After all the Oracle client SW is not available under Linux.

Nevertheless I would like to implement this Oracle Gateway using the
PostgeSQL stuff.

Now the problem for me is how to get used to the backend architecture as
fast as possible. That means is there any more deeper technical doku
available, describing the backend control flow and architekture.
Especially the layout of list and nodes structure of the parser.

Cause using the Oracle OCI there has to be some preprocessing done to
parse the libpq SQL strings for binding input and output variables to. So
a simple SELECT call is not mappable to a single OCI call.

Hmmm - why? From looking at the Oratcl package from Tom
Poindexter (Tcl extension to access Oracle DB) I know, that
Oracles OCI interface accepts mainly the same SQL strings
sent to a PostgreSQL backend. Column names and data types of
the result can be fingered out some way using odescr().

Using this information would make it possible, to build up
the data structures sent from a PostgreSQL backend to the
frontend.

A little server, running on the system where Oracle resides,
could behave like a PostgreSQL postmaster and backend.
Accepting connections on PGPORT, receiving query strings and
sending back results in the fe-be protocol. The client
shouldn't matter that the DB server it connects to isn't a
real PostgreSQL.

For every SQL statement recieved, the pseudo Postmaster just
calls Oracle using OCI and sends back the results in libpq
format.

Every client program that doesn't use PostgreSQL specific
stuff rather than standard SQL queries should be able to
access Oracle over libpq than.

If there are also other guys outside interested in this approach, it would
be perhaps worth to discuss also their requirements to get a proper
functional spec to implement the SW gateway.

Sorry for the long lines. I used the netscape to generate the mail and he
doen't complain about long lines. I will use the good old pine in the
future.

Greetings,

Dr. Armin. Schloesse

Until later, Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #

in getting simultaneous multiple db access within Postgres...
thinking of trying to implement this as a Postgres "master database"
with hooks deeper in the backend to call out to a remote database as
a separate session. Sort of like Ingres implemented their
distributed databases. Haven't done anything with it though...

Cewl... wouldn't this enable us to run PostgreSQL on beowolf-like
clusters? :)

Well, no. I haven't coded on a beowolf system (and my Linux Journal with
a writeup on it has gone wandering :( but a beowolf must be a MIMD
system with (perhaps) a shared file system. So, we would need a
medium-grained or coarse-grained decomposition of the backend to
distribute a single session across a cluster.

However, what I proposed would allow a single database, or parts of a
single logical database, to reside on one host, with access from a
client hitting multiple hosts to find all the tables, so one could
distribute the load if several pieces or many databases were involved.
Doesn't need to be a beowolf, just a networked set of servers.

- Tom