Access MySQL through hsodbc

I. Environment

OS: Linux myhostname 2.6.9-42. elsmp #1 SMP Sat Aug 12 09:39:11 CDT 2006 i686 i686 i386 GNU/Linux

Centos release 4.4 (final)

ORACLE: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-prod, the database has been installed

MySQL: 5.1.34-community for Windows

 

Ii. installation and configuration

1. Install unixodbc with the root user

Rpm-uvh unixODBC-2.2.12-1.el4s1.1.i386.rpm

 

2. Install MySQL ODBC with the root user

Rpm-uvh mysql-connector-odbc-5.1.5-0.i386.rpm

 

3. Install the Oracle gateway.

I installed 10201_gateways_linux32.zip

Unzip 10201_gateways_linux32.zip

CD gateways

./Runinstaller

The installation method is the same as that of the Oracle DB software. I have installed gateway and DB together to share an oraclehome

 

4. Configure/etc/ODBC. ini

[Dsname]
Driver =/usr/lib/libmydbc5.so
Description = MySQL
Server = XXX. XXX
Port = 3306
User = root
Uid = root
Password = mypass
Database = mysqldbname
Option = 3
Socket =
Charset = utf8

 

Test ODBC

ISQL-V dsname root mypass

 

5. configure $ ORACLE_HOME/HS/admin/initdsname. ora

Hs_fds_connect_info = dsname
Hs_fds_trace_level = 0
Hs_fds_shareable_name =/usr/lib/libmydbc5.so

 

6. Configure listener. ora and add the red part.

Sid_list_listener =
(Sid_list =
(Sid_desc =
(Sid_name = plsextproc)
(ORACLE_HOME =/usr/u01/APP/Oracle/product/10.2.0/db_1)
(Program = EXTPROC)
)
(Sid_desc =
(Global_dbname = boss)
(ORACLE_HOME =/usr/u01/APP/Oracle/product/10.2.0/db_1)
(Sid_name = boss)
)
(Sid_desc =
(Sid_name = phpcms)
(ORACLE_HOME =/usr/u01/APP/Oracle/product/10.2.0/db_1)
(Program = hsodbc)
)
)

 

7. Configure tnsnames. ora and add

Dsname =
(Description =
(Address_list =
(Address = (Protocol = TCP) (host = 192.168.0.125) (Port = 1521 ))
)
(CONNECT_DATA = (SERVICE_NAME = dsname ))
(HS = OK)
)

 

8. Restart the listener and test the listener.

LSNRCTL RELOAD

 

LSNRCTL Service

 

Service "dsname" has 1 instance (s ).
Instance "dsname", status unknown, has 1 handler (s) for this service...
Handler (s ):
"Dedicated" Established: 3 refused: 0
Local Server
The command completed successfully

 

Tnsping dsname

 

Attempting to contact (description = (address_list = (address = (Protocol = TCP) (host = 192.168.0.125) (Port = 1521) (CONNECT_DATA = (SERVICE_NAME = dsname )) (HS = OK ))
OK (0 msec)

 

9. Create a dblink

Create public database link linkname
Connect to "root"
Identified by <PWD>
Using 'dsname ';

10. Test

Select "name" from T1 @ linkname;

 

Iii. Legacy issues

1. Character Set problems. It is best to use utf8 for Oracle and MySQL; otherwise, there is a problem with Chinese characters.

2. The text field will report an error:

Select "textcol" from T1 @ linkname;

 

ORA-28500: returns this information when connecting Oracle to a non-Oracle System:
[Generic connectivity using ODBC] [MySQL] [ODBC 5.1 Driver] [mysqld-5.1.34-community] You have an error in your SQL syntax; check the manual that corresponds to your MySQL Server version for the right syntax to use near '"T1" where "ID" = 1' At Line 1 (SQL state: 37000; SQL code: 1064)
ORA-02063: followed by 2 lines (from dsname)

 

I would like to try using DataDirect's MySQL ODBC, but only the MySQL Enterprise version is supported. It is hard to find it. I will discuss it later.