Re: [HACKERS] PostgreSQL Backend as SW Gateway to Oracle - Mailing list pgsql-hackers

From jwieck@debis.com (Jan Wieck)
Subject Re: [HACKERS] PostgreSQL Backend as SW Gateway to Oracle
Date
Msg-id m0yuL8x-000EBPC@orion.SAPserv.Hamburg.dsh.de
Whole thread Raw
In response to Re: [HACKERS] PostgreSQL Backend as SW Gateway to Oracle  (Armin Schloesser <armin@ap-kas.ie.philips.com>)
List pgsql-hackers
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) #

pgsql-hackers by date:

Previous
From: Vadim Mikheev
Date:
Subject: Re: [HACKERS] Constraints in PostgreSQL
Next
From: The Hermit Hacker
Date:
Subject: Re: [HACKERS] Leaving for vacation