Yeah, I was kind of thinking that myself. I do have control over both DB's,
and postgres does have an ODBC connector available as well. Perhaps using a
trigger to watch a temp table on each site and having it replicate to the
opposite side and then remove the record from the temp table would work.
I'll have to look into it. Thanks! Kevin
-Original Message-From: "Aaron Bono"
<[EMAIL PROTECTED]>To: "Kevin Bednar"
<[EMAIL PROTECTED]>, pgsql-sql@postgresql.orgDate:
Mon, 10 Jul 2006 12:15:34 -0500Subject: Re: [SQL]
MS-SQL<->Postgres syncPlease reply to all when replying on the
list... On 7/10/06, Kevin Bednar <[EMAIL PROTECTED]
> wrote:
Thanks Aron. What I'm actually trying to do is this:
Postgress in physical store, being used by POS system
as the back end. MS-SQL being used on web server by ecommerce
system. Table structures are different of
course, but some common fields. What I want to do is when an item is sold in
the store, update the quantity field for that sku number on the web site and
vice versa. Only 2 fields basically need to be updated on each side, the SKU
number and quantity. This is to keep the product table in sync and try to
avoid selling product that isnt in stock and setting a flag on the web
system stating such. Thanks for your help.
For something this simple you are probably better off
doing some custom coding.If you have the ability to modify the
databases, I would recommend putting a trigger on each database so when
there is a product sold, that sale is recorded in a temp table (which serves
as a queue of data that needs to be synched). Then have a process read
from these temp tables and feed the data back to the other database.
Of course, I am assuming you have full control to change the databases -
some vendors do not allow that. You may be able to connect the
databases - MS SQL Server will definitely allow you to connect via ODBC to
another database and feed data back and forth. I think there are add
on modules for PostgreSQL but I have not tried to have PostgreSQL talk to
other databases before. -Aaron
