1 2 Previous Next 22 Replies Latest reply: Mar 27, 2013 8:05 AM by EdStevens RSS

    dblink oracle vs mysql

    864103
      Hi all ,

      kindly i need your support with me i need to create database link between oracle database 11g.2 under OL5.6
      and mysql database version 5

      i fallow up with the link below
      http://www.pythian.com/blog/how-to-access-mysql-from-oracle-with-odbc-and-sql/
      and i reach Creating an ODBC DSN to access the DEMO database
      but i can't find cat ~oracle/.odbc.ini in my oracle server
      [oracle@TCCBS ~]$ cat ~oracle/.odbc.ini
      cat: /home/oracle/.odbc.ini: No such file or directory
      [oracle@TCCBS ~]$
      please advice ???


      thanks
        • 1. Re: dblink oracle vs mysql
          asahide
          Hi,

          You can newly create .odbc.ini file.
          Regards,
          • 2. Re: dblink oracle vs mysql
            864103
            okay i will do it , but what about odbc connector how i install it in linux ??please !
            • 3. Re: dblink oracle vs mysql
              asahide
              Hi,

              Download and install mysql-connector-odbc rpm package file.
              Regards,
              • 4. Re: dblink oracle vs mysql
                864103
                thanks man for your time and support , but if you have good idea of this db link between oracle and mysql
                please share with me step vie step , i do a db link between oracle and oracle but this is my first time
                to this oracle and mysql , i will be a high appreciated to you .
                • 5. Re: dblink oracle vs mysql
                  ShishirTekadeR
                  1- Install ODBC driver 3.51 on Oracle box (download ODBC 3.51 for MySql)
                  2- Create userid on MySQL
                  3- Setup ODBC connection on Oracle box and test until OK
                  4- Check Listener, tnsnames and init.ora are setup as per documentation
                  5- Restart listener
                  6- Test tnsping until OK
                  7- Create public MySQL_DBLINK DBLink on Oracle (UserId and Password are case sensitive!!!)
                  8- Test "select count(*) from anytable@MySQL_DBLINK;" until OK

                  *****************************************
                  Best Regards,
                  Shishir Tekade.
                  My Blog: http://shishirtekade.com
                  • 6. Re: dblink oracle vs mysql
                    864103
                    thanks man , but please check the below
                    SQL> create database link mysql
                      2  connect to "demo"
                      identified by "demo"
                      using 'mysql';  3    4
                    
                    Database link created.
                    
                    SQL>
                    SQL>
                    SQL>
                    SQL> select "col3" from "demo"@mysql;
                    select "col3" from "demo"@mysql
                                              *
                    ERROR at line 1:
                    ORA-12154: TNS:could not resolve the connect identifier specified
                    
                    
                    SQL> select * from "demo"@mysql;
                    select * from "demo"@mysql
                                         *
                    ERROR at line 1:
                    ORA-12154: TNS:could not resolve the connect identifier specified
                    listener file in oracle
                    # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.3/network/admin/listener.ora
                    # Generated by Oracle configuration tools.
                    
                    LISTENER =
                      (DESCRIPTION_LIST =
                        (DESCRIPTION =
                          (ADDRESS = (PROTOCOL = TCP)(HOST = TCCBS)(PORT = 1521))
                          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
                        )
                      )
                    
                    ADR_BASE_LISTENER = /u01/app/oracle
                    SID_LIST_LISTENER =
                      (SID_LIST =
                        (SID_DESC =
                          (ORACLE_HOME = /u01/app/oracle/product/11.2.3)
                          (SID_NAME = mysql)
                          (PROGRAM = dg4odbc)
                          (ENVS ="LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.3/lib:/lib:/usr/lib")
                        )
                      )
                    MYSQL =
                      (DESCRIPTION =
                        (ADDRESS_LIST =
                          (ADDRESS = (PROTOCOL = TCP)
                                     (HOST = localhost)
                                     (PORT = 1521)
                          )
                        )
                        (CONNECT_DATA =
                          (SID = mysql)
                        )
                        (HS = OK)
                      )
                        
                    please advice
                    • 7. Re: dblink oracle vs mysql
                      864103
                      please any help :
                      SQL> select count(*) from demo@MySQL;
                      select count(*) from demo@MySQL
                                                *
                      ERROR at line 1:
                      ORA-12154: TNS:could not resolve the connect identifier specified
                      • 8. Re: dblink oracle vs mysql
                        864103
                        Hi i think i reach it , please check the below
                        SQL> select * from demo@mysql;
                        select * from demo@mysql
                                           *
                        ERROR at line 1:
                        ORA-28545: error diagnosed by Net8 when connecting to an agent
                        Unable to retrieve text of NETWORK/NCR message 65535
                        ORA-02063: preceding 2 lines from MYSQL
                        thanks
                        • 9. Re: dblink oracle vs mysql
                          ShishirTekadeR
                          1. We need to install the ODBC driver for the non-Oracle database we are going to connect to.


                          2. Once the ODBC Driver is installed we will need to configure a data source for the non-Oracle database. For Windows open the control panel and locate the Administrative Tools. Then locate the Data Sources (ODBC) and double click on the Data Sources.
                          Select the System DSN as for the HS service it will need to be a System DSN then Click on Add button.
                          Select the Driver for the non-Oracle data source. In this case we will be using the MySQL 3.51 Driver for a local MySQL database.
                          In this case we will give this data source the name of mysql configured on the localhost, we will be connecting with the root user. In this case we are on the default port therefore we done have to configure the connect options.


                          3. After configuring the connection settings we can test if the connection is working by pressing the test button near the bottom left of the Window. A window will indicate if the connection was successful.


                          4. Configure the init.ora for the HS service that well be used for the non-Oracle database connectivity.

                          initmysql.ora

                          # This is a sample agent init file that contains the HS parameters that are
                          # needed for an ODBC Agent.

                          #
                          # HS init parameters
                          #
                          #HS_DB_NAME = mysql
                          HS_FDS_CONNECT_INFO = mysql
                          HS_FDS_TRACE_LEVEL = ON

                          #
                          # Environment variables required for the non-Oracle system
                          #
                          #set =


                          5. Need to add the non-Oracle data source to the listener.ora

                          SID_LIST_LISTENER =
                          (SID_LIST =
                          (SID_DESC =
                          (SID_NAME = PLSExtProc)
                          (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
                          (PROGRAM = extproc)
                          )
                          (SID_DESC =
                          (SID_NAME = orcl)
                          (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
                          )
                          (SID_DESC=
                          (SID_NAME=mysql)
                          (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
                          (PROGRAM=hsodbc)
                          (ENVS=LD_LIBRARY_PATH = C:\oracle\product\10.2.0\db_1\lib32)
                          )
                          )


                          6. Reload the listener and check that we have a service for the non-Oracle data source.

                          C:\Oracle\product\10.2.0\db_1\BIN> lsnrctl reload

                          LSNRCTL for 32-bit Windows: Version 10.2.0.4.0 - Production on 10-AUG-2008 19:14:21

                          Copyright (c) 1991, 2007, Oracle. All rights reserved.

                          Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
                          The command completed successfully


                          7. Check the listener services to make sure the mysql shows

                          C:\Oracle\product\10.2.0\db_1\BIN> lsnrctl services

                          LSNRCTL for 32-bit Windows: Version 10.2.0.4.0 - Production on 10-AUG-2008 19:14:33

                          Copyright (c) 1991, 2007, Oracle. All rights reserved.

                          Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
                          Services Summary...
                          Service "ORCLXDB" has 1 instance(s).
                          Instance "orcl", status READY, has 1 handler(s) for this service...
                          Handler(s):
                          "D000" established:0 refused:0 current:0 max:1002 state:ready
                          DISPATCHER
                          (ADDRESS=(PROTOCOL=tcp)(HOST=xxxxxxxxx)(PORT=2323))
                          Service "ORCL_XPT" has 1 instance(s).
                          Instance "orcl", status READY, has 1 handler(s) for this service...
                          Handler(s):
                          "DEDICATED" established:0 refused:0 state:ready
                          LOCAL SERVER
                          Service "PLSExtProc" has 1 instance(s).
                          Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
                          Handler(s):
                          "DEDICATED" established:0 refused:0
                          LOCAL SERVER
                          Service "mysql" has 1 instance(s).
                          Instance "mysql", status UNKNOWN, has 1 handler(s) for this service...
                          Handler(s):
                          "DEDICATED" established:0 refused:0
                          LOCAL SERVER
                          Service "orcl" has 2 instance(s).
                          Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
                          Handler(s):
                          "DEDICATED" established:0 refused:0
                          LOCAL SERVER
                          Instance "orcl", status READY, has 1 handler(s) for this service...
                          Handler(s):
                          "DEDICATED" established:0 refused:0 state:ready
                          LOCAL SERVER
                          The command completed successfully


                          8. Need to now add the non-Oracle data source to the tnsnames.ora

                          mysql =
                          (DESCRIPTION =
                          (ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxxxx)(PORT = 1521))
                          (CONNECT_DATA =
                          (SID = MYSQL)
                          )
                          (HS=OK)
                          )


                          9. Check that the non-Oracle data source is reachable via a tnsping.

                          C:\Oracle\product\10.2.0\db_1\BIN>tnsping mysql

                          TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 14-DEC-2008 18:12:46
                          Copyright (c) 1997, 2007, Oracle. All rights reserved.

                          Used parameter files:C:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora

                          Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxxxx)(PORT = 1521)) (CONNECT_DATA = (SID = MYSQL)) (HS=OK))OK (30 msec)


                          10. Create a public database link and check that it works.

                          C:\Oracle\product\10.2.0\db_1\BIN>sqlplus /@orcl

                          SQL*Plus: Release 10.2.0.4.0 - Production on Sun Aug 10 20:10:00 2008

                          Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


                          Connected to:
                          Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
                          With the Partitioning, OLAP, Data Mining and Real Application Testing options

                          SQL> create public database link mysql connect to "root" identified by "xxxxxxxxxx"
                          using 'mysql' ;

                          Database link created.

                          SQL> select count(*) from alfresco.alf_node@mysql ;

                          COUNT(*)
                          ----------
                          1469

                          SQL>


                          *****************************************
                          Best Regards,
                          Shishir Tekade.
                          My Blog: http://shishirtekade.com
                          • 10. Re: dblink oracle vs mysql
                            864103
                            thanks man i solve this issue i just change the ip in tnsnames to oracle server not mysql server , but know i get new error
                            SQL>  select * from demo@mysqlora;
                             select * from demo@mysqlora
                                                *
                            ERROR at line 1:
                            ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
                            ORA-02063: preceding line from MYSQLORA
                            also please be informed that 2 server is linux
                            HS_FDS_CONNECT_INFO=demo
                            HS_FDS_TRACE_LEVEL=ON
                            HS_FDS_SHAREABLE_NAME=/home/oracle/mysql515/lib/libmyodbc5.so
                            HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15
                            # HS_NLS_NCHAR=AL32UTF8
                            #
                            # ODBC specific environment variables
                            #
                            set ODBCINI=/home/oracle/.odbc.ini
                            set LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.3/lib:/lib:/usr/lib
                            
                            #
                            # Environment variables required for the non-Oracle system
                            #
                            set HOME=/home/oracle
                            [oracle@TCCBS ~]$ tnsping mysql
                            
                            TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 26-MAR-2013 11:58:55
                            
                            Copyright (c) 1997, 2011, Oracle.  All rights reserved.
                            
                            Used parameter files:
                            /u01/app/oracle/product/11.2.3/network/admin/sqlnet.ora
                            
                            
                            Used TNSNAMES adapter to resolve the alias
                            Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 172.240.5.185) (PORT = 1521))) (CONNECT_DATA = (SID = mysql)) (HS = OK))
                            OK (10 msec)
                            Service "mysql" has 1 instance(s).
                              Instance "mysql", status UNKNOWN, has 1 handler(s) for this service...
                                Handler(s):
                                  "DEDICATED" established:0 refused:0
                                     LOCAL SERVER
                            The command completed successfully
                            [oracle@TCCBS ~]$
                            and still i have error
                            SQL> select * from demo@mysqlora;
                            select * from demo@mysqlora
                                               *
                            ERROR at line 1:
                            ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
                            ORA-02063: preceding line from MYSQLORA
                            please i think the issue is with odbc i install this packages but i not define the data source and everything cuz my system is linux
                            not windows i don't know how to lunch the odbc connector ???please help me in this issue .
                            • 11. Re: dblink oracle vs mysql
                              ShishirTekadeR
                              please check below i think u maybe get u r solution...


                              Creating a MySQLDEMO database, user and table

                              For the purpose of the demonstration, I’ve created a database, a user and a table named DEMO with the mysql client. You’ll find the script below.

                              Important Note:
                              The gateway for ODBC doesn’t look to work correctly when data are stored in utf8 in MySQL, whether or not the Connector/ODBC does the transformation into a non-utf8 character set. For this reason, I set the MySQL database default character set to latin1. I suspect somehow the issue is related to the ODBC driver: if I use latin1 on the client side, Oracle should not see any difference, whatever the storing character set is. Anyway, there is also a limitation on the Oracle side, and it doesn’t handle utf8 correctly with the Connector (see MySupport note 756186.1)

                              $ mysql -uroot -p

                              create database demo character set latin1;
                              grant all privileges on demo.* to 'demo'@'localhost'
                              identified by 'demo' with grant option;
                              flush privileges;
                              exit;

                              $ mysql -udemo -pdemo -Ddemo

                              create table demo (
                              col1 integer,
                              col2 date,
                              col3 varchar(10),
                              col4 varchar(10) character set utf8,
                              col5 varbinary(10)) engine innodb;

                              insert into demo(col1, col2, col3, col4, col5)
                              values(1, cast(now() as date), '0123456789', '0123456789', '0123456789');

                              select * from demo \G

                              *********** 1. row ************
                              col1: 1
                              col2: 2009-03-11
                              col3: 0123456789
                              col4: 0123456789
                              col5: 0123456789

                              exit;
                              Creating an ODBC DSN to access the DEMO database

                              Once the database was created, I created a user DSN in the Oracle owner, so that the the listener can get it via the dg4odbc program. By default the file that store the user DSN is $HOME/.odbc.ini, but you can change it to any file/location that fits your needs. This is how the file looks like on my server:

                              $ cat ~oracle/.odbc.ini
                              [ODBC Data Sources]
                              demo = MySQL ODBC Driver 5.1

                              [demo]
                              Driver = /home/oracle/mysql515/lib/libmyodbc5.so
                              DATABASE = demo
                              DESCRIPTION = MySQL ODBC 5.1.5 Connector Sample
                              PORT = 3306
                              SERVER = 127.0.0.1
                              # UID = demo
                              # PWD = demo
                              CHARSET = latin1
                              TRACEFILE = /tmp/myodbc-demodsn.trc
                              TRACE = OFF
                              Make sure the CHARSET parameter is set so that it doesn’t use utf8.

                              Configuring dg4odbc to use the DSN

                              dg4odbc gets its settings from a file named init[SID].ora located in $ORACLE_HOME/hs/admin. In this case, SID is an arbitrary parameter we’ll configure in the listener.orafile (see next section). I’ve used SID=mysql for this demo, and the initmysql.ora file looks like the one below:

                              $ cat $ORACLE_HOME/hs/admin/initmysql.ora
                              #
                              # HS init parameters
                              #
                              HS_FDS_CONNECT_INFO=demo
                              HS_FDS_TRACE_LEVEL=0
                              HS_FDS_SHAREABLE_NAME=/home/oracle/mysql515/lib/libmyodbc5.so
                              HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15
                              # HS_NLS_NCHAR=AL32UTF8
                              #
                              # ODBC specific environment variables
                              #
                              set ODBCINI=/home/oracle/.odbc.ini
                              set LD_LIBRARY_PATH=/home/oracle/mysql515/lib

                              #
                              # Environment variables required for the non-Oracle system
                              #
                              set HOME=/home/oracle
                              As you can see above:

                              ODBCINI is the location odbc.ini the file.
                              HS_FDS_CONNECT_INFO points to the right DSN.
                              HS_FDS_SHAREABLE_NAME points to ODBC driver shared library.
                              HS_LANGUAGE is set to avoid the problem described in Oracle MySupport “756186.1: Error Ora-28500 and Sqlstate I Issuing Selects From a Unicode Oracle RDBMS With Dg4odbc To Mysql”.
                              Configuring the listener

                              To configure the listener, I had to change the listener.ora file to add the SID defined in the previous section and associate it with the gateway for ODBC; here is a copy of my setup used; I’ve kept all the settings (host, port, dynamic registration) default:

                              $ cat $ORACLE_HOME/network/admin/listener.ora

                              SID_LIST_LISTENER =
                              (SID_LIST =
                              (SID_DESC =
                              (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
                              (SID_NAME = mysql)
                              (PROGRAM = dg4odbc)
                              (ENVS ="LD_LIBRARY_PATH=/home/oracle/mysql515/lib:/usr/lib:$ORACLE_HOME/lib")
                              )
                              )
                              Once the listener is setup, you can bounce or reload it; if it’s not started, just start it:

                              $ lsnrctl start
                              And add an entry in the listener.ora file like the one below; make sure you’ve added HS=OK and that it’s not in the CONNECT_DATA clause:

                              MYSQL =
                              (DESCRIPTION =
                              (ADDRESS_LIST =
                              (ADDRESS = (PROTOCOL = TCP)
                              (HOST = localhost)
                              (PORT = 1521)
                              )
                              )
                              (CONNECT_DATA =
                              (SID = mysql)
                              )
                              (HS = OK)
                              )
                              Creating a database link to connect to MySQL from Oracle

                              At this point, and after a little debugging of the different layers, I was able to create a database link from Oracle to MySQL. To do so, I connected to Oracle and used the CREATE DATABASE LINK command:

                              $ sqlplus / as sysdba

                              create database link mysql
                              connect to "demo"
                              identified by "demo"
                              using 'mysql';

                              select "col3" from "demo"@mysql;

                              col3
                              ---------------------------------
                              0123456789
                              Note:
                              The case policy differs between Oracle and MySQL, and you must
                              always surround the table and columns name with double quotes.

                              Using the Gateway for ODBC

                              If you remember correctly, I stored the string “0123456789″ in col3, col4, and col5. You’ll see some of the issues with the character set by querying a datum stored in utf8:

                              select "col4" from "demo"@mysql;

                              col4
                              ----------------------------------------
                              0 1 2 3 4
                              and one stored in a varbinary:

                              select "col5" from "demo"@mysql;

                              col5
                              --------------------
                              30313233343536373839
                              But you’ll also be able to enjoy some of the features of your new Oracle/MySQL integrated environment, and be able to create a table in Oracle using data from MySQL:

                              create table demo as
                              select "col1" col1, "col2" col2, "col3" col3
                              from "demo"@mysql;

                              select * from demo;

                              COL1 COL2 COL3
                              ---- --------- ----------
                              1 11-MAR-09 0123456789
                              Unfortunately, you cannot insert data directly from Oracle into MySQL with an insert as select:

                              insert into "demo"@mysql("col1").
                              select 2 from dual;

                              ERROR at line 2:
                              ORA-02025: all tables in the SQL statement must be at the remote database
                              But you can workaround that issue with some PL/SQL (I’m not saying it’s efficient):

                              begin
                              for i in (select col1, col2, col3 from demo) loop
                              insert into "demo"@mysql("col1","col2", "col3")
                              values (2,i.col2, i.col3);
                              end loop;
                              end;
                              /

                              select "col1","col2", "col3"
                              from "demo"@mysql;

                              col1 col2 col3
                              ---- --------- ----------
                              1 11-MAR-09 0123456789
                              2 11-MAR-09 0123456789
                              To prevent the access to MySQL from Oracle, you can drop the database link:

                              drop database link mysql;
                              That is it. It works pretty well so far and, despite the limits of such an approach, it can be quite useful for those that want to migrate from MySQL to Oracle


                              *****************************************
                              Best Regards,
                              Shishir Tekade.
                              My Blog: http://shishirtekade.com
                              • 12. Re: dblink oracle vs mysql
                                864103
                                hi Dear , thanks for your support ,

                                please check with me the below
                                create database and tabkles and user already done with latin1 charcharter set 
                                
                                Creating an ODBC DSN to access the DEMO database
                                
                                i do this 
                                
                                sudo yum install unixODBC unixODBC-devel libtool-ltdl libtool-ltdl-devel.x86_64
                                sudo yum install mysql-connector-odbc
                                
                                
                                [oracle@TCCBS ~]$ cat ~oracle/.odbc.ini
                                [ODBC Data Sources]
                                demo = MySQL ODBC Driver 5.1
                                
                                [demo]
                                Driver      = /usr/lib64/libmyodbc3.so
                                DATABASE    = demo
                                DESCRIPTION = MySQL ODBC 5.1.5 Connector Sample
                                PORT        = 3306
                                SERVER      = 172.240.5.115    MYSQL IP 
                                # UID         = demo
                                # PWD         = demo
                                CHARSET     = latin1
                                TRACEFILE   = /tmp/myodbc-demodsn.trc
                                TRACE       = OFF
                                
                                
                                
                                # HS init parameters
                                #
                                HS_FDS_CONNECT_INFO=demo
                                HS_FDS_TRACE_LEVEL=ON
                                HS_FDS_SHAREABLE_NAME=/usr/lib64/libmyodbc3.so
                                HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15
                                # HS_NLS_NCHAR=AL32UTF8
                                #
                                # ODBC specific environment variables
                                #
                                set ODBCINI=/home/oracle/.odbc.ini
                                set LD_LIBRARY_PATH=/usr/lib64/libmyodbc3.so:/u01/app/oracle/product/11.2.3/lib:/lib:/usr/lib
                                
                                #
                                # Environment variables required for the non-Oracle system
                                #
                                set HOME=/home/oracle
                                
                                
                                listener :
                                
                                LISTENER =
                                  (DESCRIPTION_LIST =
                                    (DESCRIPTION =
                                      (ADDRESS = (PROTOCOL = TCP)(HOST = TCCBS)(PORT = 1521))
                                      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
                                    )
                                  )
                                
                                ADR_BASE_LISTENER = /u01/app/oracle
                                
                                SID_LIST_LISTENER =
                                  (SID_LIST =
                                    (SID_DESC =
                                      (ORACLE_HOME = /u01/app/oracle/product/11.2.3)
                                      (SID_NAME = mysql)
                                      (PROGRAM = dg4odbc)
                                      (ENVS ="LD_LIBRARY_PATH=/usr/lib64/libmyodbc3.so:/usr/lib:$ORACLE_HOME/lib")
                                    )
                                  )
                                
                                
                                
                                tnsnames :
                                
                                
                                MYSQL =
                                  (DESCRIPTION =
                                    (ADDRESS_LIST =
                                      (ADDRESS = (PROTOCOL = TCP)
                                                 (HOST = 172.240.5.185)    ORACLE IP 
                                                 (PORT = 1521)
                                      )
                                    )
                                    (CONNECT_DATA =
                                      (SID = mysql)
                                    )
                                    (HS = OK)
                                  )
                                
                                
                                please tel me what wrong in my configuartion ?                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                                • 13. Re: dblink oracle vs mysql
                                  EdStevens
                                  861100 wrote:
                                  hi Dear , thanks for your support ,

                                  please check with me the below
                                  create database and tabkles and user already done with latin1 charcharter set 
                                  
                                  Creating an ODBC DSN to access the DEMO database
                                  
                                  i do this 
                                  
                                  sudo yum install unixODBC unixODBC-devel libtool-ltdl libtool-ltdl-devel.x86_64
                                  sudo yum install mysql-connector-odbc
                                  
                                  
                                  [oracle@TCCBS ~]$ cat ~oracle/.odbc.ini
                                  [ODBC Data Sources]
                                  demo = MySQL ODBC Driver 5.1
                                  
                                  [demo]
                                  Driver      = /usr/lib64/libmyodbc3.so
                                  DATABASE    = demo
                                  DESCRIPTION = MySQL ODBC 5.1.5 Connector Sample
                                  PORT        = 3306
                                  SERVER      = 172.240.5.115    MYSQL IP 
                                  # UID         = demo
                                  # PWD         = demo
                                  CHARSET     = latin1
                                  TRACEFILE   = /tmp/myodbc-demodsn.trc
                                  TRACE       = OFF
                                  It looks like you've done nothing to actually configure your odbc.ini file. The only connection you have defined is named "demo" ..... the userid and password are still commented out .... makes me wonder if the ip address is even correct.

                                  Perhaps these will help:

                                  http://www.easysoft.com/developer/interfaces/odbc/linux.html

                                  http://www.unixodbc.org/odbcinst.html
                                  >
                                  # HS init parameters
                                  #
                                  HS_FDS_CONNECT_INFO=demo
                                  HS_FDS_TRACE_LEVEL=ON
                                  HS_FDS_SHAREABLE_NAME=/usr/lib64/libmyodbc3.so
                                  HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15
                                  # HS_NLS_NCHAR=AL32UTF8
                                  #
                                  # ODBC specific environment variables
                                  #
                                  set ODBCINI=/home/oracle/.odbc.ini
                                  set LD_LIBRARY_PATH=/usr/lib64/libmyodbc3.so:/u01/app/oracle/product/11.2.3/lib:/lib:/usr/lib

                                  #
                                  # Environment variables required for the non-Oracle system
                                  #
                                  set HOME=/home/oracle


                                  listener :

                                  LISTENER =
                                  (DESCRIPTION_LIST =
                                  (DESCRIPTION =
                                  (ADDRESS = (PROTOCOL = TCP)(HOST = TCCBS)(PORT = 1521))
                                  (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
                                  )
                                  )

                                  ADR_BASE_LISTENER = /u01/app/oracle

                                  SID_LIST_LISTENER =
                                  (SID_LIST =
                                  (SID_DESC =
                                  (ORACLE_HOME = /u01/app/oracle/product/11.2.3)
                                  (SID_NAME = mysql)
                                  (PROGRAM = dg4odbc)
                                  (ENVS ="LD_LIBRARY_PATH=/usr/lib64/libmyodbc3.so:/usr/lib:$ORACLE_HOME/lib")
                                  )
                                  )



                                  tnsnames :


                                  MYSQL =
                                  (DESCRIPTION =
                                  (ADDRESS_LIST =
                                  (ADDRESS = (PROTOCOL = TCP)
                                  (HOST = 172.240.5.185) ORACLE IP
                                  (PORT = 1521)
                                  )
                                  )
                                  (CONNECT_DATA =
                                  (SID = mysql)
                                  )
                                  (HS = OK)
                                  )


                                  please tel me what wrong in my configuartion ?
                                  • 14. Re: dblink oracle vs mysql
                                    864103
                                    please check with me now my lisetener and tnsnsames is correct
                                    [ODBC Data Sources]
                                    demo = MySQL ODBC Driver 5.1
                                    
                                    [demo]
                                    Driver      = /usr/lib64/libmyodbc3.so
                                    DATABASE    = demo
                                    DESCRIPTION = MySQL ODBC 5.1.5 Connector Sample
                                    PORT        = 3306
                                    SERVER      = 172.240.5.115
                                    UID         = demo
                                    PWD         = demo
                                    CHARSET     = latin1
                                    TRACEFILE   = /tmp/myodbc-demodsn.trc
                                    TRACE       = OFF
                                    
                                    
                                    
                                    # HS init parameters
                                    #
                                    HS_FDS_CONNECT_INFO=mysql
                                    HS_FDS_TRACE_LEVEL=ON
                                    HS_FDS_SHAREABLE_NAME=/usr/lib64/libmyodbc3.so
                                    HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15
                                    # HS_NLS_NCHAR=AL32UTF8
                                    #
                                    # ODBC specific environment variables
                                    #
                                    set ODBCINI=/home/oracle/.odbc.ini
                                    set LD_LIBRARY_PATH=/usr/lib64/libmyodbc3.so:/u01/app/oracle/product/11.2.3/lib:/lib:/usr/lib
                                    
                                    #
                                    # Environment variables required for the non-Oracle system
                                    #
                                    set HOME=/home/oracle
                                    ~
                                    please help me in this issue i know it's not your business but if you can help me i will appreciated .
                                    1 2 Previous Next