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

[Jerry Westrick]

Mr. Lemburg

First thansk for the prompt reply...
(it's nice to be supported out of europe every once in a while)!

Looking in your documentation, is see a routne called "execute"
which I could use to avoid having to do SQL formating.  

So following your suggestion, and documentation I can:
1- Get list of columns (in correct order from result set)
2- build insert/update stmt with '?' parameter markers
3- then for each row returned from original select
    pass the resulting row as 'params' parameter to "execute" method

I wonder if the returned row would be acceptable as input to the 
params parameter....


Jerry Westrick



On Fri, 2003-10-10 at 10:41, M.-A. Lemburg wrote:
> 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.
> 
>          """