[egenix-users] Copy ing rows from tables...

[M.-A. Lemburg]

Jerry Westrick wrote:
> Dear sirs.
> 
> I have the situation where I "replicate" a database table.  That is I
> have a complete copy of a (MS SQL) database table stored locally (in
> Postgress).  The system has been setup so that I can identify the
> modified rows. Theese I need to retrieve and store (either insert or
> update) into my postgress table.
> 
> I access both databases via your zope mxodbc product.  
> 
> My question is, which is the easiest way to retrive a row from
> the MS-SQL connection, and inseert/update itno the postgress connection.
> 
> THe standard zope solution, requires the listing of all field names in
> both the update and insert statements.  This is terribly maintenance
> prone.  Since the 2 tables are identical (except my version has an
> additional field which is updated via trigger), I suspect that there
> must be a lower level routines, which i can use which takes advantage 
> of the fact that the 2 tables are similarly structured.
> 
> Thanking you in advance for your support.

It is quite easy to fetch the field names for both tables and
format an SQL query for the update from that list.

A typical way of doing that is to run the query "SELECT * FROM
MyTable WHERE 1=0" and then having a look at the result set
which  is empty but does list the field names.

Another approach is to use the .columns() method on the mxODBC
Zope DA connection object:

     def columns(self, table_name):

         """ Returns a list of dictionaries with entries 'Name',
             'Type', 'Precision', 'Scale', 'Nullable' ('with Null' or '')
             for each column in the table table_name.

         """

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Software directly from the Source  (#1, Oct 10 2003)
 >>> Python/Zope Products & Consulting ...         http://www.egenix.com/
 >>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::