Re: Any Oracle HSODBC and PostgreSQL experience out there?

On Tue, Apr 18, 2006 at 05:43:11PM -0700, Reece Hart wrote:
> Thanks Josh, Elein, David for replies.
>
> On Thu, 2006-04-13 at 16:03 -0700, Josh Berkus wrote:
>
> > You could hack this by extending DBI-Link, although the syntax
> > will be awkward ... currently RULES give us no way to grab the
> > WHERE clause from an incoming query. So you'd need to do
> > something like:
>
> Am I missing something? I'm trying to query a pg database from
> within Oracle... DBI-Link doesn't help with going that direction,
> right?

It might, and here's a sketch of one way.

Let's say you have a set of tables in PostgreSQL that you want visible
from Oracle.

* Duplicate (or translate--this could be a little tricky) the
appropriate parts of their DDL on the Oracle side. Now there are
two sets of tables: one in PostgreSQL, the copy in Oracle.

* Hook up the Oracle set via DBI-Link in the same database as your
regular tables.

* Make some triggers on each table similar to the ones you'd use for
materialized views. The triggers would repeat INSERTs, UPDATEs and
DELETEs to the DBI-Link schema inside PostgreSQL. Ass-u-me'ing that
you have primary keys on each of those tables, you could automate
the trigger (function) creation process via using the information
schema.

* Do a bunch of INSERT INTO ... SELECT... on the with your PostgreSQL
tables as source and the DBI-Link views as destination to get
started.

> I'm surprised at this outcome with HSODBC... I'm new to gateways but I
> don't see how HSODBC is useful if you can't push predicates to the
> remote side. I realize that this is is non-trivial, but in the
> PostgreSQL world it seems like a Fetter Small Magic Plugin (TM) to a
> cost-based optimizer might do the trick.

I wish I were that smart. :)

Cheers,
D (pushing predicates from the PA)
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!