This discussion is archived
1 2 Previous Next 22 Replies Latest reply: Mar 27, 2013 6:05 AM by EdStevens RSS

dblink oracle vs mysql

864103 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    Hi,

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

    Download and install mysql-connector-odbc rpm package file.
    Regards,
  • 4. Re: dblink oracle vs mysql
    864103 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points