Thread: PostgreSQL Backend as SW Gateway to Oracle

PostgreSQL Backend as SW Gateway to Oracle

From
"Dr. Armin Schloesser"
Date:
Hello

Sorry for asking the hackers directly, but I think my question will be very implementation

specific so I think only one of yor can give me the answer or thow me out the door.

First some introductionary explanations:

We are developing a SCADA system for process automation since the last 6 years. In 1991

we changed the platform from a commercial Unix to Linux and are very happy with the choice.

Within the SCADA system we have a programming environment (called STX, similar to the IEC1131 Structured Text

language) for doing sophisticated controlling or analysis needed to run the process.

From within this programming environment we also have the need to access a relational database to store

any data from the SCADE system into. This to give the end user also the possibilty to access this data

for offline analysis purpose e.g from a MS/Windows application.

We have implemented the PostgreSQL database during the last weeks, by using the libpq. This works fine.

Nearly all needed functionality and tools are available for it (pgaccess, Web access, ODBC, ...).

The problem is now as following:

 We have some customers, who want to have the database interface within the SCADA system (same API),

but want to have the data itself stored in a Oracle database (one customer on  Digital Unix, the other

on Win/NT).

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

mentioned platforms and simply acts as a SW gateway to the Oracle Sever (using e.g the Oracle OCI interaface

or the PRO/C interafce). Such a solution would preserve my interface completely (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

==============================================================================
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
==============================================================================




Re: [HACKERS] PostgreSQL Backend as SW Gateway to Oracle

From
"Dr. Michael Meskes"
Date:
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!

Re: [HACKERS] PostgreSQL Backend as SW Gateway to Oracle

From
Bruce Momjian
Date:
(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)

Re: [HACKERS] PostgreSQL Backend as SW Gateway to Oracle

From
Armin Schloesser
Date:
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:

> (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)
>


Re: [HACKERS] PostgreSQL Backend as SW Gateway to Oracle

From
"Thomas G. Lockhart"
Date:
> 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

Re: [HACKERS] PostgreSQL Backend as SW Gateway to Oracle

From
Armin Schloesser
Date:
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:

>
> 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
>


Re: [HACKERS] PostgreSQL Backend as SW Gateway to Oracle

From
Maarten Boekhold
Date:
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                         |
-----------------------------------------------------------------------------


Re: [HACKERS] PostgreSQL Backend as SW Gateway to Oracle

From
Bruce Momjian
Date:
> 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)

Re: [HACKERS] PostgreSQL Backend as SW Gateway to Oracle

From
"Dr. Michael Meskes"
Date:
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!

Re: [HACKERS] PostgreSQL Backend as SW Gateway to Oracle

From
Tom
Date:
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


Re: [HACKERS] PostgreSQL Backend as SW Gateway to Oracle

From
"Thomas G. Lockhart"
Date:
> > 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

Re: [HACKERS] PostgreSQL Backend as SW Gateway to Oracle

From
jwieck@debis.com (Jan Wieck)
Date:
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) #