Thread: PostgreSQL Backend as SW Gateway to Oracle
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 ==============================================================================
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: > (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) >
> 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: > > 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 >
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 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!
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
> > 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
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) #