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.

--
James Card
209-578-5580

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 ?

Werner,-

--
Dr. Werner Van Belle
http://werner.sigtrans.org/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQBHBzhVCEqhQ551YXQRAmYRAJ9I6gGpQPJaQKl1nrIy+u 8WTALKlgCgrTE9
j5vi53ALNwCMfhbDNTM/IZQ=
=VMzS
-----END PGP SIGNATURE-----

Have you guys thought about trying to use a Federated table?
http://dev.mysql.com/doc/refman/5.0/...ge-engine.html

Probably not exactly the solution you want, but maybe useful.

On 10/6/07, Werner Van Belle <werner@sigtrans.org> wrote:
>
> 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 ?
>
> Werner,-
>
> --
> Dr. Werner Van Belle
> http://werner.sigtrans.org/
>
>


--
Rob Wultsch
(480)223-2566
wultsch@gmail.com (email/google im)
wultsch (aim)
wultsch@hotmail.com (msn)

On Sat, 06 Oct 2007 00:25:04 -0700, Werner Van Belle <werner@sigtrans.org>
wrote:

> 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
209-578-5580