MySQL ODBC Connector - Access, ODBC, Oracle, MySQL

Author

Access, ODBC, Oracle, MySQL

Robert Citek

2005-09-28, 8:24 pm

Is there a way to query data where one table is in an Oracle database
and another is in a MySQL database?

We have two projects. One project is entirely in Oracle 9i on an MS
Windows 2000 server and the other is in MySQL 4.1 on Linux. It isn't
worth our time at this point to migrate entirely to one or the
other. So I was wondering if there is a way to query data from two
tables, where one table was in one database and the other table in
another?

Currently, I have hacked together a perl script which does what I
need. But this is not a long-term solution. So we are looking for
other strategies. One strategy would be to be to use ODBC drivers
and MS Access to link the two tables within an MS Access database.
Another strategy would be to link the Oracle tables into MySQL, but I
don't know if that can be done. Any other strategies? Pointers to
documents or search terms gladly accepted as my current searches
returned a lot of hits but nothing useful, unless I missed it.

Regards,
- Robert
http://www.cwelug.org/downloads
Help others get OpenSource software. Distribute FLOSS
for Windows, Linux, *BSD, and MacOS X with BitTorrent

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sujay Koduri

2005-09-28, 8:24 pm

I don't know how far this is going to help you. But this is what we are
following to migrate from Oracle to MySQL.

Have a flag keeporacle as long as you want oracle to be in place.

Open connection to mysql
If (keeporacle)
{
open connection to oracle also.
}

if (keepOracle)
{
found = look in Mysql db
if (not found)
{
look in oracle db //Anyway this architecture must be in place
for you.
insert in MySQL db
}
}
else
look in mysql and serve back, if it exists.

Close connection to mysql
If (keeporacle)
{
close connection to oracle.
}

And once you feel confident that all the data is moved from oracle to MySQL,
take oracle out.

sujay

-----Original Message-----
From: Robert Citek & #91;mailto:rwcitek@a
lum.calberkeley.org]
Sent: Thursday, September 29, 2005 12:58 AM
To: MySQL
Subject: Access, ODBC, Oracle, MySQL

Is there a way to query data where one table is in an Oracle database and
another is in a MySQL database?

We have two projects. One project is entirely in Oracle 9i on an MS Windows
2000 server and the other is in MySQL 4.1 on Linux. It isn't worth our time
at this point to migrate entirely to one or the other. So I was wondering
if there is a way to query data from two tables, where one table was in one
database and the other table in another?

Currently, I have hacked together a perl script which does what I need. But
this is not a long-term solution. So we are looking for other strategies.
One strategy would be to be to use ODBC drivers
and MS Access to link the two tables within an MS Access database.
Another strategy would be to link the Oracle tables into MySQL, but I don't
know if that can be done. Any other strategies? Pointers to documents or
search terms gladly accepted as my current searches returned a lot of hits
but nothing useful, unless I missed it.

Regards,
- Robert
http://www.cwelug.org/downloads
Help others get OpenSource software. Distribute FLOSS for Windows, Linux,
*BSD, and MacOS X with BitTorrent

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=sujayk@andale.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Robert Citek

2005-09-28, 8:24 pm

On Sep 28, 2005, at 2:37 PM, Sujay Koduri wrote:
> I don't know how far this is going to help you. But this is what we
> are
> following to migrate from Oracle to MySQL.

If we were migrating, that's probably the strategy that we would
use. But we are not, at least not yet. For now we just want to be
able to join two tables which reside in different databases (Oracle
and MySQL). What I'm hoping is one of the following:

1) I can link an Oracle table into MySQL using ODBC
2) I can link a MySQL table into Oracle using ODBC

I have found docs that hint that (1) is possible, but haven't found
anything that says "Here's how you do it." As for the second option,
I discovered something called "Oracle Heterogeneous Services" which
may be the ticket, but again, I haven't found anything that says
"Here's how you do it."

Personally, I would prefer option (1) over option (2) because that
would then provide a clear path and an incentive for migration to MySQL.

Regards,
- Robert
http://www.cwelug.org/downloads
Help others get OpenSource software. Distribute FLOSS
for Windows, Linux, *BSD, and MacOS X with BitTorrent

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sujay Koduri

2005-09-28, 8:24 pm

Once you get some "Here's how you do it." about option 1, please post it in
the lists also. Because we are also looking to do a similar one, but didn't
find out any good info.

sujay

-----Original Message-----
From: Robert Citek & #91;mailto:rwcitek@a
lum.calberkeley.org]
Sent: Thursday, September 29, 2005 1:32 AM
To: MySQL
Subject: Re: Access, ODBC, Oracle, MySQL

On Sep 28, 2005, at 2:37 PM, Sujay Koduri wrote:
> I don't know how far this is going to help you. But this is what we
> are following to migrate from Oracle to MySQL.

If we were migrating, that's probably the strategy that we would use. But
we are not, at least not yet. For now we just want to be able to join two
tables which reside in different databases (Oracle and MySQL). What I'm
hoping is one of the following:

1) I can link an Oracle table into MySQL using ODBC
2) I can link a MySQL table into Oracle using ODBC

I have found docs that hint that (1) is possible, but haven't found anything
that says "Here's how you do it." As for the second option, I discovered
something called "Oracle Heterogeneous Services" which may be the ticket,
but again, I haven't found anything that says "Here's how you do it."

Personally, I would prefer option (1) over option (2) because that would
then provide a clear path and an incentive for migration to MySQL.

Regards,
- Robert
http://www.cwelug.org/downloads
Help others get OpenSource software. Distribute FLOSS for Windows, Linux,
*BSD, and MacOS X with BitTorrent

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=sujayk@andale.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Robert Citek

2005-09-28, 8:24 pm

On Sep 28, 2005, at 3:09 PM, Sujay Koduri wrote:
> Once you get some "Here's how you do it." about option 1, please
> post it in
> the lists also. Because we are also looking to do a similar one,
> but didn't
> find out any good info.

I have been looking through here, but have not found anything
concrete, yet:

http://dev.mysql.com/doc/mysql/en/odbc-connector.html

Regards,
- Robert
http://www.cwelug.org/downloads
Help others get OpenSource software. Distribute FLOSS
for Windows, Linux, *BSD, and MacOS X with BitTorrent

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Berman, Mikhail

2005-09-28, 8:24 pm

Robert,

I have not worked for a while with Crystal Reports and I do not know if
CR allows to go across different database type, and are you willing to
spend the money on it, but for plain querying Crystal Reports works well
using ODBC connections. CR works where MS-Access fails, because
MS-Access does not really wants to work with a lot of data that might be
stored on either Oracle or MySQL

http://www.businessobjects.com/prod...stalreports/=20

Mikhail Berman

-----Original Message-----
From: Robert Citek & #91;mailto:rwcitek@a
lum.calberkeley.org]=20
Sent: Wednesday, September 28, 2005 3:28 PM
To: MySQL
Subject: Access, ODBC, Oracle, MySQL

Is there a way to query data where one table is in an Oracle database
and another is in a MySQL database?

We have two projects. One project is entirely in Oracle 9i on an MS
Windows 2000 server and the other is in MySQL 4.1 on Linux. It isn't
worth our time at this point to migrate entirely to one or the other.
So I was wondering if there is a way to query data from two tables,
where one table was in one database and the other table in another?

Currently, I have hacked together a perl script which does what I need.
But this is not a long-term solution. So we are looking for other
strategies. One strategy would be to be to use ODBC drivers =20
and MS Access to link the two tables within an MS Access database. =20
Another strategy would be to link the Oracle tables into MySQL, but I
don't know if that can be done. Any other strategies? Pointers to
documents or search terms gladly accepted as my current searches
returned a lot of hits but nothing useful, unless I missed it.

Regards,
- Robert
http://www.cwelug.org/downloads
Help others get OpenSource software. Distribute FLOSS for Windows,
Linux, *BSD, and MacOS X with BitTorrent

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql? unsub...s
inc.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

SGreen@unimin.com

2005-09-28, 8:24 pm

--=_alternative 0073524C8525708A_=
Content-Type: text/plain; charset="US-ASCII"

Robert Citek <rwcitek@alum.calberkeley.org> wrote on 09/28/2005 04:01:33
PM:

>
> On Sep 28, 2005, at 2:37 PM, Sujay Koduri wrote:
>
> If we were migrating, that's probably the strategy that we would
> use. But we are not, at least not yet. For now we just want to be
> able to join two tables which reside in different databases (Oracle
> and MySQL). What I'm hoping is one of the following:
>
> 1) I can link an Oracle table into MySQL using ODBC
> 2) I can link a MySQL table into Oracle using ODBC
>
> I have found docs that hint that (1) is possible, but haven't found
> anything that says "Here's how you do it." As for the second option,
> I discovered something called "Oracle Heterogeneous Services" which
> may be the ticket, but again, I haven't found anything that says
> "Here's how you do it."
>
> Personally, I would prefer option (1) over option (2) because that
> would then provide a clear path and an incentive for migration to MySQL.
>
> Regards,
> - Robert
> http://www.cwelug.org/downloads
> Help others get OpenSource software. Distribute FLOSS
> for Windows, Linux, *BSD, and MacOS X with BitTorrent
>
>

(1) is almost possible with 5.0. The Federated storage engine creates the
equivalent of "linked tables" but it is MySQL-to-MySQL only. There are
other restrictions, too. As soon as they work out the kinks for
MySQL-to-ODBC, you will be in business. Full details:
http://dev.mysql.com/doc/mysql/en/f...age-engine.html

(2) This was suggested earlier (just last week) and an example was walked
through on the list. Really, this is an Oracle question and I would
happily refer you to any Oracle docs on how to create such an ODBC-linked
table. I don't know where to look so I Googled:
http://www.google.com/search?hl=en&...G=Google+Search

(hit #2)This link seems promising. Try substituting references to their
drivers with references to the MyODBC driver:
http://www.easysoft.com/applications/oracle/hsodbc.html

(hit #4)This also looks to be useful. Substitute MS SQL server information
with MySQL values where appropriate.
http://www.databasejournal.com/feat...cle.php/3442661

(hit #5) (OMG!!) This one actually describes linking Oracle to MySQL :
http://techrepublic.com.com/5100-9592-5725974.html

One of these or some of the other appx 48500 other hits has GOT to work
for you.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

--=_alternative 0073524C8525708A_=--

Robert Citek

2005-09-29, 11:23 am

On Sep 28, 2005, at 4:03 PM, SGreen@unimin.com wrote:

> (1) is almost possible with 5.0. The Federated storage engine
> creates the
> equivalent of "linked tables" but it is MySQL-to-MySQL only. There are
> other restrictions, too. As soon as they work out the kinks for
> MySQL-to-ODBC, you will be in business. Full details:
> http://dev.mysql.com/doc/mysql/en/f...age-engine.html
>

Except that our data and all its infrastructure will need to be
upgraded and tested, too. Not something that will happen any time soon.

> (2) This was suggested earlier (just last week) and an example was
> walked
> through on the list. Really, this is an Oracle question and I would
> happily refer you to any Oracle docs on how to create such an ODBC-
> linked
> table. I don't know where to look so I Googled:
> http://www.google.com/search?hl=en&...ou
s+link
> +odbc&btnG=Google+Search
>
> (hit #2)This link seems promising. Try substituting references to
> their
> drivers with references to the MyODBC driver:
> http://www.easysoft.com/applications/oracle/hsodbc.html
>
> (hit #4)This also looks to be useful. Substitute MS SQL server
> information
> with MySQL values where appropriate.
> http://www.databasejournal.com/feat...cle.php/3442661
>
> (hit #5) (OMG!!) This one actually describes linking Oracle to MySQL :
> http://techrepublic.com.com/5100-9592-5725974.html
>
> One of these or some of the other appx 48500 other hits has GOT to
> work
> for you.
>

Thanks for the links. We are trying them out.

Yes, Google is nice and of the 48500 hopefully one will work. But
I'm also curious to know if anyone here on this list has actually
gotten MySQL and Oracle to interoperate? Lots of hits via Google are
not very useful if no one is able to reproduce the results.

Regards,
- Robert
http://www.cwelug.org/downloads
Help others get OpenSource software. Distribute FLOSS
for Windows, Linux, *BSD, and MacOS X with BitTorrent

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links