X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 40E939FB269 for ; Mon, 10 Jul 2006 14:21:00 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 07265-01 for ; Mon, 10 Jul 2006 14:20:54 -0300 (ADT) X-Greylist: delayed 01:52:54.2733 by SQLgrey- Received: from stockwelldesigngroup.com (mail.stockwelldesigngroup.com [66.97.4.21]) by postgresql.org (Postfix) with ESMTP id 935199FB24A for ; Mon, 10 Jul 2006 14:20:54 -0300 (ADT) Received: from WorldClient by stockwelldesigngroup.com (MDaemon PRO v9.0.1) with ESMTP id md50000036872.msg for ; Mon, 10 Jul 2006 13:20:53 -0400 Received: from [65.41.91.88] by stockwelldesigngroup.com via WorldClient with HTTP; Mon, 10 Jul 2006 13:20:52 -0400 Date: Mon, 10 Jul 2006 13:20:52 -0400 From: "Kevin Bednar" To: "Aaron Bono" , pgsql-sql@postgresql.org Subject: Re: MS-SQL<->Postgres sync MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="_0710-1320-52-PART-BREAK" Message-ID: X-Mailer: WorldClient 9.0.1 In-Reply-To: References: X-Authenticated-Sender: kevin@stockwelldesigngroup.com X-Spam-Processed: stockwelldesigngroup.com, Mon, 10 Jul 2006 13:20:53 -0400 (not processed: message from valid local sender) X-Return-Path: kevin@stockwelldesigngroup.com X-MDaemon-Deliver-To: pgsql-sql@postgresql.org X-MDAV-Processed: stockwelldesigngroup.com, Mon, 10 Jul 2006 13:20:53 -0400 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/151 X-Sequence-Number: 25725 --_0710-1320-52-PART-BREAK Content-Type: text/plain; charset=us-ascii 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" To: "Kevin Bednar" , pgsql-sql@postgresql.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 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 --_0710-1320-52-PART-BREAK Content-Type: text/html; charset=us-ascii
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@aranya.com>
To: "Kevin Bednar" <kevin@stockwelldesigngroup.com>, pgsql-sql@postgresql.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@stockwelldesigngroup.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
--_0710-1320-52-PART-BREAK--