Re: database link

2005-09-20 Thread Ananda Kumar 
Hi Mark,
First set up an ODBC data source for your MySQL database, using MyODBC.
 How do i do this.
 regards
anandkl
 On 9/19/05, Ananda Kumar [EMAIL PROTECTED] wrote: 
 
 Hi Mark,
 Thanks for the email. Yes i also read about this.
 But the problem with this approach is since our oracle db is a production 
 database which runs 24*78 and during night time it runs lot of other jobs i 
 cannot make the reporting job run from this oracle database. 
  regards
 anandkl
 
  On 9/16/05, Mark Leith [EMAIL PROTECTED] wrote:  
  
  You may want to think about doing this the opposite way around also, and
  look at pushing the data from Oracle in to MySQL. Oracle has something 
  called heterogeneous services, which allows you to define ODBC 
  datasources
  as valid entries in the tnsnames.ora file. Then you could simply create 
  a
  job in Oracle that executes a procedure to do the entire process 
  (truncate / 
  load), no external scripting necessary..
  
  Here's a quick example of what to do:
  
  First set up an ODBC data source for your MySQL database, using MyODBC.
  
  Create a file in ORACLE_HOME/hs/admin called initMySQL.ora. In this file 
  put
  the following options:
  
  HS_FDS_CONNECT_INFO = MySQL5 #ODBC DSN /* Replace MySQL5 with your DSN 
  */
  HS_FDS_TRACE_LEVEL = OFF
  
  Alter your listener.ora file (ORACLE_HOME/network/admin) to add the 
  following:
  
  (SID_DESC =
  (PROGRAM = hsodbc)
  (ORACLE_HOME = oracle/product/92) /* Your ORACLE_HOME */
  (SID_NAME = MySQL5) /* Your DSN */
  )
  
  Add the following to your tnsnames.ora file:
  
  MYSQL5 =
  (DESCRIPTION=
  (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
  (CONNECT_DATA=(SID=MYSQL5))
  (HS=OK)
  )
  
  Reload your Oracle listener (lsnrctl reload), and then connect to the 
  Oracle 
  database. To set the database link up:
  
  CREATE DATABASE LINK mysql5 CONNECT TO user identified by password 
  using
  'mysql5';
  
  User and password should be a valid user within MySQL, that can connect 
  from 
  the Oracle host. You should be set to go from there. Here's a quick 
  example
  of this working, to a MySQL 5 database using the new sakila sample
  database that Mike Hillyer recently released
  ( http://www.openwin.org/mike/download/sakila.zip):
  
  SQL select count(*) from [EMAIL PROTECTED];
  
  COUNT(*)
  --
  1000
  
  SQL desc [EMAIL PROTECTED];
  Name Null? Type 
  - 
  
  film_id NUMBER(10)
  category_id NOT NULL NUMBER(10)
  title NOT NULL VARCHAR2(27) 
  description LONG
  rental_duration NOT NULL NUMBER(3)
  length NUMBER(10)
  rating CHAR(5) 
  
  SQL insert into [EMAIL PROTECTED] values 
  (100,1,'test','test',1,1,'PG');
  
  1 row created.
  
  ---change prompts---
  
  mysql use sakila
  Database changed
  mysql select max(film_id) from film; 
  +--+
  | max(film_id) |
  +--+
  | 100 |
  +--+
  1 row in set (0.01 sec)
  
  HTH
  
  Mark Leith
  
  --
  No virus found in this outgoing message.
  Checked by AVG Anti-Virus. 
  Version: 7.0.344 / Virus Database: 267.11.0/103 - Release Date: 
  15/09/2005
  
  
  
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]

Re: database link

2005-09-19 Thread Ananda Kumar 
Hi Mark,
Thanks for the email. Yes i also read about this.
But the problem with this approach is since our oracle db is a production 
database which runs 24*78 and during night time it runs lot of other jobs i 
cannot make the reporting job run from this oracle database. 
 regards
anandkl

 On 9/16/05, Mark Leith [EMAIL PROTECTED] wrote: 
 
 You may want to think about doing this the opposite way around also, and
 look at pushing the data from Oracle in to MySQL. Oracle has something
 called heterogeneous services, which allows you to define ODBC 
 datasources
 as valid entries in the tnsnames.ora file. Then you could simply create a
 job in Oracle that executes a procedure to do the entire process (truncate 
 /
 load), no external scripting necessary..
 
 Here's a quick example of what to do:
 
 First set up an ODBC data source for your MySQL database, using MyODBC.
 
 Create a file in ORACLE_HOME/hs/admin called initMySQL.ora. In this file 
 put
 the following options:
 
 HS_FDS_CONNECT_INFO = MySQL5 #ODBC DSN /* Replace MySQL5 with your DSN */
 HS_FDS_TRACE_LEVEL = OFF
 
 Alter your listener.ora file (ORACLE_HOME/network/admin) to add the
 following:
 
 (SID_DESC =
 (PROGRAM = hsodbc)
 (ORACLE_HOME = oracle/product/92) /* Your ORACLE_HOME */
 (SID_NAME = MySQL5) /* Your DSN */
 )
 
 Add the following to your tnsnames.ora file:
 
 MYSQL5 =
 (DESCRIPTION=
 (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
 (CONNECT_DATA=(SID=MYSQL5))
 (HS=OK)
 )
 
 Reload your Oracle listener (lsnrctl reload), and then connect to the 
 Oracle
 database. To set the database link up:
 
 CREATE DATABASE LINK mysql5 CONNECT TO user identified by password 
 using
 'mysql5';
 
 User and password should be a valid user within MySQL, that can connect 
 from
 the Oracle host. You should be set to go from there. Here's a quick 
 example
 of this working, to a MySQL 5 database using the new sakila sample
 database that Mike Hillyer recently released
 (http://www.openwin.org/mike/download/sakila.zip):
 
 SQL select count(*) from [EMAIL PROTECTED];
 
 COUNT(*)
 --
 1000
 
 SQL desc [EMAIL PROTECTED];
 Name Null? Type
 - 
 
 film_id NUMBER(10)
 category_id NOT NULL NUMBER(10)
 title NOT NULL VARCHAR2(27)
 description LONG
 rental_duration NOT NULL NUMBER(3)
 length NUMBER(10)
 rating CHAR(5)
 
 SQL insert into [EMAIL PROTECTED] values (100,1,'test','test',1,1,'PG');
 
 1 row created.
 
 ---change prompts---
 
 mysql use sakila
 Database changed
 mysql select max(film_id) from film;
 +--+
 | max(film_id) |
 +--+
 | 100 |
 +--+
 1 row in set (0.01 sec)
 
 HTH
 
 Mark Leith
 
 --
 No virus found in this outgoing message.
 Checked by AVG Anti-Virus.
 Version: 7.0.344 / Virus Database: 267.11.0/103 - Release Date: 15/09/2005
 
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]

Re: database link

2005-09-19 Thread Ananda Kumar 
Hi Mark,
Thanks for the email. Yes i also read about this.
But the problem with this approach is since our oracle db is a production 
database which runs 24*78 and during night time it runs lot of other jobs i 
cannot make the reporting job run from this oracle database. 
 regards
anandkl

 On 9/16/05, Mark Leith [EMAIL PROTECTED] wrote: 
 
 You may want to think about doing this the opposite way around also, and
 look at pushing the data from Oracle in to MySQL. Oracle has something
 called heterogeneous services, which allows you to define ODBC 
 datasources
 as valid entries in the tnsnames.ora file. Then you could simply create a
 job in Oracle that executes a procedure to do the entire process (truncate 
 /
 load), no external scripting necessary..
 
 Here's a quick example of what to do:
 
 First set up an ODBC data source for your MySQL database, using MyODBC.
 
 Create a file in ORACLE_HOME/hs/admin called initMySQL.ora. In this file 
 put
 the following options:
 
 HS_FDS_CONNECT_INFO = MySQL5 #ODBC DSN /* Replace MySQL5 with your DSN */
 HS_FDS_TRACE_LEVEL = OFF
 
 Alter your listener.ora file (ORACLE_HOME/network/admin) to add the
 following:
 
 (SID_DESC =
 (PROGRAM = hsodbc)
 (ORACLE_HOME = oracle/product/92) /* Your ORACLE_HOME */
 (SID_NAME = MySQL5) /* Your DSN */
 )
 
 Add the following to your tnsnames.ora file:
 
 MYSQL5 =
 (DESCRIPTION=
 (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
 (CONNECT_DATA=(SID=MYSQL5))
 (HS=OK)
 )
 
 Reload your Oracle listener (lsnrctl reload), and then connect to the 
 Oracle
 database. To set the database link up:
 
 CREATE DATABASE LINK mysql5 CONNECT TO user identified by password 
 using
 'mysql5';
 
 User and password should be a valid user within MySQL, that can connect 
 from
 the Oracle host. You should be set to go from there. Here's a quick 
 example
 of this working, to a MySQL 5 database using the new sakila sample
 database that Mike Hillyer recently released
 (http://www.openwin.org/mike/download/sakila.zip):
 
 SQL select count(*) from [EMAIL PROTECTED];
 
 COUNT(*)
 --
 1000
 
 SQL desc [EMAIL PROTECTED];
 Name Null? Type
 - 
 
 film_id NUMBER(10)
 category_id NOT NULL NUMBER(10)
 title NOT NULL VARCHAR2(27)
 description LONG
 rental_duration NOT NULL NUMBER(3)
 length NUMBER(10)
 rating CHAR(5)
 
 SQL insert into [EMAIL PROTECTED] values (100,1,'test','test',1,1,'PG');
 
 1 row created.
 
 ---change prompts---
 
 mysql use sakila
 Database changed
 mysql select max(film_id) from film;
 +--+
 | max(film_id) |
 +--+
 | 100 |
 +--+
 1 row in set (0.01 sec)
 
 HTH
 
 Mark Leith
 
 --
 No virus found in this outgoing message.
 Checked by AVG Anti-Virus.
 Version: 7.0.344 / Virus Database: 267.11.0/103 - Release Date: 15/09/2005
 
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]

Database replication between oracle-mysql (was RE: database link)

2005-09-19 Thread Burke, Dan 

This is really cool!  Can something be done with this to use a
materialized view log on a table in Oracle and sync with a table in
mysql?


-Original Message-
From: Mark Leith [mailto:[EMAIL PROTECTED] 
Sent: Friday, September 16, 2005 9:54 AM
To: mysql@lists.mysql.com
Subject: RE: database link

You may want to think about doing this the opposite way around also, and
look at pushing the data from Oracle in to MySQL. Oracle has something
called heterogeneous services, which allows you to define ODBC
datasources
as valid entries in the tnsnames.ora file. Then you could simply create
a
job in Oracle that executes a procedure to do the entire process
(truncate /
load), no external scripting necessary..

Here's a quick example of what to do:

First set up an ODBC data source for your MySQL database, using MyODBC.

Create a file in ORACLE_HOME/hs/admin called initMySQL.ora. In this file
put
the following options:

HS_FDS_CONNECT_INFO = MySQL5 #ODBC DSN /* Replace MySQL5 with your DSN
*/
HS_FDS_TRACE_LEVEL = OFF

Alter your listener.ora file (ORACLE_HOME/network/admin) to add the
following:

(SID_DESC =
  (PROGRAM  = hsodbc)
  (ORACLE_HOME  = oracle/product/92) /* Your ORACLE_HOME */
  (SID_NAME = MySQL5) /* Your DSN */
)

Add the following to your tnsnames.ora file:

MYSQL5 =
  (DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
(CONNECT_DATA=(SID=MYSQL5))
(HS=OK)
  )

Reload your Oracle listener (lsnrctl reload), and then connect to the
Oracle
database. To set the database link up:

CREATE DATABASE LINK mysql5 CONNECT TO user identified by password
using
'mysql5';

User and password should be a valid user within MySQL, that can connect
from
the Oracle host. You should be set to go from there. Here's a quick
example
of this working, to a MySQL 5 database using the new sakila sample
database that Mike Hillyer recently released
(http://www.openwin.org/mike/download/sakila.zip):

SQL select count(*) from [EMAIL PROTECTED];

  COUNT(*)
--
  1000

SQL desc [EMAIL PROTECTED];
 Name  Null?Type
 - 

 film_idNUMBER(10)
 category_id   NOT NULL NUMBER(10)
 title NOT NULL VARCHAR2(27)
 descriptionLONG
 rental_duration   NOT NULL NUMBER(3)
 length NUMBER(10)
 rating CHAR(5)

SQL insert into [EMAIL PROTECTED] values (100,1,'test','test',1,1,'PG');

1 row created.

---change prompts---

mysql use sakila
Database changed
mysql select max(film_id) from film;
+--+
| max(film_id) |
+--+
|  100 |
+--+
1 row in set (0.01 sec)

HTH

Mark Leith

-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.0/103 - Release Date:
15/09/2005
 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]



___
This message has been scanned for viruses 
by TechTeam's email gateway.



_
This e-mail transmission is strictly confidential 
and intended solely for the person or organization 
to whom it is addressed. It may contain privileged 
and confidential information and if you are not the 
intended recipient, you must not copy, distribute or 
take any action in reliance on it. If you have 
received this e-mail in error, please notify the 
sender as soon as possible and delete the e-mail 
message and any attachment(s).

This message has been scanned for viruses 
by TechTeam's email gateway.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: database link

2005-09-16 Thread Mark Leith 
You may want to think about doing this the opposite way around also, and
look at pushing the data from Oracle in to MySQL. Oracle has something
called heterogeneous services, which allows you to define ODBC datasources
as valid entries in the tnsnames.ora file. Then you could simply create a
job in Oracle that executes a procedure to do the entire process (truncate /
load), no external scripting necessary..

Here's a quick example of what to do:

First set up an ODBC data source for your MySQL database, using MyODBC.

Create a file in ORACLE_HOME/hs/admin called initMySQL.ora. In this file put
the following options:

HS_FDS_CONNECT_INFO = MySQL5 #ODBC DSN /* Replace MySQL5 with your DSN */
HS_FDS_TRACE_LEVEL = OFF

Alter your listener.ora file (ORACLE_HOME/network/admin) to add the
following:

(SID_DESC =
  (PROGRAM  = hsodbc)
  (ORACLE_HOME  = oracle/product/92) /* Your ORACLE_HOME */
  (SID_NAME = MySQL5) /* Your DSN */
)

Add the following to your tnsnames.ora file:

MYSQL5 =
  (DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
(CONNECT_DATA=(SID=MYSQL5))
(HS=OK)
  )

Reload your Oracle listener (lsnrctl reload), and then connect to the Oracle
database. To set the database link up:

CREATE DATABASE LINK mysql5 CONNECT TO user identified by password using
'mysql5';

User and password should be a valid user within MySQL, that can connect from
the Oracle host. You should be set to go from there. Here's a quick example
of this working, to a MySQL 5 database using the new sakila sample
database that Mike Hillyer recently released
(http://www.openwin.org/mike/download/sakila.zip):

SQL select count(*) from [EMAIL PROTECTED];

  COUNT(*)
--
  1000

SQL desc [EMAIL PROTECTED];
 Name  Null?Type
 - 

 film_idNUMBER(10)
 category_id   NOT NULL NUMBER(10)
 title NOT NULL VARCHAR2(27)
 descriptionLONG
 rental_duration   NOT NULL NUMBER(3)
 length NUMBER(10)
 rating CHAR(5)

SQL insert into [EMAIL PROTECTED] values (100,1,'test','test',1,1,'PG');

1 row created.

---change prompts---

mysql use sakila
Database changed
mysql select max(film_id) from film;
+--+
| max(film_id) |
+--+
|  100 |
+--+
1 row in set (0.01 sec)

HTH

Mark Leith

-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.0/103 - Release Date: 15/09/2005
 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: database link

2005-09-15 Thread SGreen 
Ananda Kumar [EMAIL PROTECTED] wrote on 09/15/2005 10:33:16 AM:

 Hi,
 How can i connect from MYSQL db to an oracle database. Do we need to 
create 
 any database link or use some ODBC.
 Please help
  regards
 anandkl


The only way to get one MySQL database server to directly manage data in a 
table that resides within another server is through the Federated storage 
engine. Federated is a new feature coming with v5.0 and is similar to 
MSSQL's linked table feature. Currently it only supports MySQL to MySQL 
table interactions.

When you say connect from MYSQL db to an oracle database what exactly 
are you trying to accomplish? What are you trying to do? What problem are 
you having that you think the connection would solve? Please give us 
some additional details so we can answer your question more thoroughly.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

RE: database link

2005-09-15 Thread Berman, Mikhail 
 
Hi Ananda,

I am not sure what do you mean by connecting MySQL to Oracle:

* Is it a permanent connection
* Do you want to transfer data from one to the other
* or ?

Mikhail Berman

-Original Message-
From: Ananda Kumar [mailto:[EMAIL PROTECTED] 
Sent: Thursday, September 15, 2005 10:33 AM
To: mysql@lists.mysql.com
Subject: database link

Hi,
How can i connect from MYSQL db to an oracle database. Do we need to
create any database link or use some ODBC.
Please help
 regards
anandkl

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: database link

2005-09-15 Thread SGreen 
No. You will need to use an application external to the server to perform 
the data collection process. This application can be one you write 
yourselves or something you purchase. There are many ways to perform 
things on a schedule:

a) you application continuously runs and performs certain actions 
according to an internal timer

b) the operating system uses one of its task scheduling services to launch 
your application according to a schedule you set.

There are absolutely too many  ways to solve your problem to answer your 
question any more specifically. 

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


Ananda Kumar [EMAIL PROTECTED] wrote on 09/15/2005 10:56:36 AM:

 Hi Green,
 Thanks for the answer.
 We are planing to have reporting database on mysql. This reporting 
 database needs to get data from oracle database on a daily basis. It
 would drop the table and get the fresh data from oracle database. So
 can this be done using database link. 
 
 Regards
 anandkl
 
 
 
 
 On 9/15/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: 
 
 Ananda Kumar  [EMAIL PROTECTED] wrote on 09/15/2005 10:33:16 AM: 
 
 
  Hi,
  How can i connect from MYSQL db to an oracle database. Do we need to 
create 
  any database link or use some ODBC.
  Please help
   regards 
  anandkl
 
 
 The only way to get one MySQL database server to directly manage 
 data in a table that resides within another server is through the 
 Federated storage engine. Federated is a new feature coming with v5.
 0 and is similar to MSSQL's linked table feature. Currently it 
 only supports MySQL to MySQL table interactions. 
 
 When you say connect from MYSQL db to an oracle database what 
 exactly are you trying to accomplish? What are you trying to do? 
 What problem are you having that you think the connection would 
 solve? Please give us some additional details so we can answer your 
 question more thoroughly. 
 
 Shawn Green
 Database Administrator
 Unimin Corporation - Spruce Pine

Re: database link

2005-09-15 Thread SGreen 
Yes, you can use the same process. You will need to modify your existing 
code so that you get  the data from Oracle and write it to MySQL. You will 
be able to reuse most, but not all, of your existing script. Those 
portions that connect to your destination server and  write data into the 
database will need to be modified to use the proper MySQL equivalents.

Also, please remember to CC: all responses to the entire list. If I had 
become unavailable due to the demands of my normal job (all but two or 
three active members of the list do NOT work for MySQL, we all have 
regular, full-time jobs) someone else could have responded in my place. It 
also makes any information you gain or provide useful for others as it 
will be in the public realm.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine




Ananda Kumar [EMAIL PROTECTED] 
09/15/2005 11:17 AM
Please respond to
[EMAIL PROTECTED]


To
[EMAIL PROTECTED] [EMAIL PROTECTED]
cc

Subject
Re: database link






Hi Green,
Right now we are doing the same way. The current reporting db is on 
oracle, so we use a cronjob and the scripts connects to main db  using 
database link and fetches data into the reporting database. So i was 
wondering if i can do the same using mysql and connect to oracle db. 
 
Regards
anandkl
 
 

 
On 9/15/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: 

No. You will need to use an application external to the server to perform 
the data collection process. This application can be one you write 
yourselves or something you purchase. There are many ways to perform 
things on a schedule: 

a) you application continuously runs and performs certain actions 
according to an internal timer 

b) the operating system uses one of its task scheduling services to launch 
your application according to a schedule you set. 

There are absolutely too many  ways to solve your problem to answer your 
question any more specifically. 

Shawn Green 
Database Administrator
Unimin Corporation - Spruce Pine 


Ananda Kumar  [EMAIL PROTECTED] wrote on 09/15/2005 10:56:36 AM:

 Hi Green, 

 Thanks for the answer. 
 We are planing to have reporting database on mysql. This reporting 
 database needs to get data from oracle database on a daily basis. It 
 would drop the table and get the fresh data from oracle database. So
 can this be done using database link. 
   
 Regards 
 anandkl 
   
 
 
   
 On 9/15/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: 
 
 Ananda Kumar  [EMAIL PROTECTED] wrote on 09/15/2005 10:33:16 AM: 
 
 
  Hi, 
  How can i connect from MYSQL db to an oracle database. Do we need to 
create 
  any database link or use some ODBC.
  Please help
   regards 
  anandkl 
 
 
 The only way to get one MySQL database server to directly manage 
 data in a table that resides within another server is through the 
 Federated storage engine. Federated is a new feature coming with v5. 
 0 and is similar to MSSQL's linked table feature. Currently it 
 only supports MySQL to MySQL table interactions. 
 
 When you say connect from MYSQL db to an oracle database what 
 exactly are you trying to accomplish? What are you trying to do? 
 What problem are you having that you think the connection would 
 solve? Please give us some additional details so we can answer your 
 question more thoroughly. 
 
 Shawn Green
 Database Administrator
 Unimin Corporation - Spruce Pine