Re: MS-SQL<->Postgres sync

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" <postgresql(at)aranya(dot)com>
To: "Kevin Bednar" <kevin(at)stockwelldesigngroup(dot)com>,
pgsql-sql(at)postgresql(dot)org
Date: Mon, 10 Jul 2006 12:15:34 -0500
Subject: Re: [SQL] MS-SQL<->Postgres sync

Please reply to all when replying on the list...

On 7/10/06, Kevin Bednar <kevin(at)stockwelldesigngroup(dot)com > 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