Carl Karsten wrote: > Carsten Haese wrote: > >> On Tue, 22 May 2007 17:33:27 -0500, Carl Karsten wrote >> >>> Or some such abomination of results of one query as a parameter of a >>> 2nd. >>> >>> given my use case, I can understand why this isn't in the spec, and >>> why it may never be. but it seems to come up more often that I >>> would expect, so here we go. >>> >>> My current problem: reconcile transaction details that are off due >>> to rounding errors. the 2 sets of details are stored on different >>> servers, and no chance of getting one server to hit the 2nd, so the >>> python client code is going to have to help by getting a list of >>> keys from one and constructing "WHERE OtherKey IN ( 'key1', 'key2', >>> 'key3', ...)" which isn't 'hard' but I find annoying that I have >>> to convert formats in the application layer. >>> >> Option 1: Create a temporary table on one server and load the data from the >> other server into it. Then, use a server side subquery or join the tables >> together. >> >> If you don't have write permission on either server, there is Option 2: Fetch >> all relevant data from server 1 into client memory, fetch all relevant data >> from server 2 into client memory, and do the reconciliation in client memory. >> > > The perms issue can be taken care of by having the table created ahead of > time. > but, the application level code is still 'custom' and falls into a similar > pit > as embedding parameters into the SQL command string. >
From my ivory tower, this sounds more like a database problem than an application problem. I agree completely that having to implement this logic in the application is awkward. Is replication an option with the database you are using? Either replicating from server 1 into server 2 (possibly vice-versa) or replicate from server 1 into server 3 and replicate from server 2 into server 3 so you can perform all queries in server 3. With replication you tend to have options of either batch extract/load (ETL) or real time replication (usually implemented by the database itself, sometimes by a 3rd party tool). The downside of replication are increased space requirements and possibly delays in replication if it is not real time (for a reporting situation like this, collisions should not be an issue). Chris _______________________________________________ DB-SIG maillist - DB-SIG@python.org http://mail.python.org/mailman/listinfo/db-sig
