Thread: Trigger updates MS SQL table

Is it too presumptious that there should be a direction I can go to solve my
problem.

Right now, I have a postgres environment that our business is run on and SQL
Server 2000 databases that our customer ASP solutions run in. Both serve
their purposes well. So, I need both. What I was hoping, was to get a form
of replication going between the two. In a perfect world, my trigger would
just create an ODBC connection to the MS Sql server and run the
update/insert query. I know that cannot exactly happen, it just doesn't work
that way.

But can I create a trigger that allows me to connect to a SOAP/XML service
and deliver the update via a stored procedure call? Is this too much to hope
for, or are people doing this already?

Thanks
Don Sceifers

IIRC some folks on -hackers are working on cross database queries to
interface with oracle databases, theres a chance that could work for sql
server too. Someone else on -hackers was looking at an internal soap/xml
communications method to do similar things to what your describing. You
might want to check the archives on both ideas. 

The other option, and probably more doable, is to write a function in
one of the untrusted languages to communicate with your xml service via
sockets.

Robert Treat

On Wed, 2003-09-10 at 15:29, Home (E-mail) wrote:
> Is it too presumptious that there should be a direction I can go to solve my
> problem.
> 
> Right now, I have a postgres environment that our business is run on and SQL
> Server 2000 databases that our customer ASP solutions run in. Both serve
> their purposes well. So, I need both. What I was hoping, was to get a form
> of replication going between the two. In a perfect world, my trigger would
> just create an ODBC connection to the MS Sql server and run the
> update/insert query. I know that cannot exactly happen, it just doesn't work
> that way.
> 
> But can I create a trigger that allows me to connect to a SOAP/XML service
> and deliver the update via a stored procedure call? Is this too much to hope
> for, or are people doing this already?
> 
> Thanks
> Don Sceifers
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Have you tried plperlu.  I currently am using DBI (DBD::Pg and DBD::Oracle) to connect to external databases under PG 
V7.2.4.  While you can't use plperlu as your trigger language you call call it from your plpgsql.  

Also note, I am only doing selects in my functions, so I don't have to worry about 2 phase commits and all the other 
problems that go along with it.


Jim



---------- Original Message -----------
From: Robert Treat <xzilla@users.sourceforge.net>
To: "Home (E-mail)" <don@sceifers.com>
Cc: pgsql-interfaces@postgresql.org
Sent: 15 Sep 2003 15:56:58 -0400
Subject: Re: [INTERFACES] Trigger updates MS SQL table

> IIRC some folks on -hackers are working on cross database queries to
> interface with oracle databases, theres a chance that could work for sql
> server too. Someone else on -hackers was looking at an internal soap/xml
> communications method to do similar things to what your describing. You
> might want to check the archives on both ideas.
> 
> The other option, and probably more doable, is to write a function in
> one of the untrusted languages to communicate with your xml service via
> sockets.
> 
> Robert Treat
> 
> On Wed, 2003-09-10 at 15:29, Home (E-mail) wrote:
> > Is it too presumptious that there should be a direction I can go to solve my
> > problem.
> > 
> > Right now, I have a postgres environment that our business is run on and SQL
> > Server 2000 databases that our customer ASP solutions run in. Both serve
> > their purposes well. So, I need both. What I was hoping, was to get a form
> > of replication going between the two. In a perfect world, my trigger would
> > just create an ODBC connection to the MS Sql server and run the
> > update/insert query. I know that cannot exactly happen, it just doesn't work
> > that way.
> > 
> > But can I create a trigger that allows me to connect to a SOAP/XML service
> > and deliver the update via a stored procedure call? Is this too much to hope
> > for, or are people doing this already?
> > 
> > Thanks
> > Don Sceifers
> > 
> > 
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 8: explain analyze is your friend
> 
> -- 
> Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
------- End of Original Message -------