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

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(at)debis(dot)com (Jan Wieck) #