List:General Discussion« Previous MessageNext Message »
From:James Card Date:October 8 2007 5:49pm
Subject:Re: Connect To Foreign Tables
View as plain text  
On Sat, 06 Oct 2007 00:25:04 -0700, Werner Van Belle <werner@stripped>  

> Hello,
> On Friday 05 October 2007 01:11:35 James Card wrote:
>> The application I'm working on uses MySQL 5.0.41 and we also need to  
>> retrieve some data from a SQL-Server database in another department.  
>> SQL-Server has a feature that I haven't tested) that allows it to make  
>> an ODBC connection to a foreign database and treat its tables as if  
>> they were local.
>> Is there any way to accomplish something similar in MySQL? I'd love to  
>> be able to create a view that joins data from my local tables with that  
>> from SQL_Server.
> Did you get any response ? I'm quite interested in this question too.  
> Recently I had to join around 1 G of local data with the genome  
> databases in Germany and found that I either had to upload 1G of data to  
> a temporary table or download 54G of data.  Neither was particularly  
> inspiring, so it would have been nice if I could just use various  
> databases through the same local server.
> Are there any solutions for this kind of problem ?

I received one response and it said "use an App whose ConnectionManager  
will establish 2 connections the 1st one to SQLServer and one connection  
for MySQL" -- in other words: no, MySQL doesn't do this; your application  
code would have to retrieve the two datasets separately and create the  
join in memory. Way beyond the budget for this simple project. I ended up  
writing separate queries against the two databases; not as simple and  
elegant as a single query would have been, and the code was a bit more  
complex, but it works.

James Card
Connect To Foreign TablesJames Card5 Oct
  • Re: Connect To Foreign TablesWerner Van Belle6 Oct
    • Re: Connect To Foreign TablesRob Wultsch6 Oct
    • Re: Connect To Foreign TablesJames Card8 Oct