[DB-SIG] client side sub queries

[M.-A. Lemburg]

On 2007-05-23 20:37, Chris Clark wrote:
> 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.

While I don't think that this is awkward. Indeed it can be a lot more
efficient if done right, e.g. by loading the selects from both
databases into memory and using Gadfly for the query work.

However, there are also several other options you could use.

Here's one that easy to setup, the EasySoft ODBC Join Engine:

http://www.easysoft.com/products/data_access/odbc_odbc_join_engine/index.html

The use mxODBC to access it from Python and you're set :-)

Quite a few commercial database engines also allow similar tricks
ie. you can directly hook up one database to another and query
both using the same SQL statement, doing joins, selects, subqueries,
etc.

Oracle and SQL Server are two such engines. The setup is usually
called a "linked server".


Of course, all of this is completely unrelated to the DB-API :-)

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, May 23 2007)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

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


   eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611